1 PostgreSQL을 시작하면서

2 사용자 생성

3 데이터베이스 만들기

4 테스트를 위한 데이터넣기

2004년 10월 초.. PostgreSQL책을 별 기대감 없이 구입했다. 책의 목차를 둘러보고는 기쁨을 감출 수가 없었다. 열심히 판매되고 있는 상용DBMS와 견주어 공짜 DBMS라는 오명(?)은 나에게서 사라지고 말았다. 상용 DBMS에서 지원하고 있는 기능을 대부분 지원하고 있으며, 특정 DBMS는 없는 기능을 제공 하고 있었다.

이 홈페이지에 있는 문서들은 기본은 제외할 것이다. 기본기능에 대한 소개로 우리가 익히 알고 있는 SELECt, UPDATE, DELETE, INSERT, GROUP BY, HAVING등과 같은 기본키워드는 이러한 기능이 제공되고 있다는 것만을 보여줄 것이다. 많은 부분을 프로시저와 함수, 그리고 트리거에 할당할 것이다. 또한 내장SQL에 대해서도 많은 양을 할당할 것이다.

실제로 본인은 PostgreSQL을 처음 설치해 보았다. 그리고 처음 접하는 것이다. 알고 있는 것이라고는 기존에 다른 DBMS를 다뤘던 것과 데이터베이스의 기초이론 뿐이다. 역시 백문이불여일타!! 한번 해보는 것이 중요하다. 마우스로 뚝딱거려봤더니 뭔가 석연치 않은 것이 역시 윈도우 환경에서는 쬐끔 힘들구나라는 생각을 해보았다. 그러나 세계의 PostgreSQL 개발자들이 놀았던 것은 아니다. 다 길이 있다.

문법은 다음과 같다.

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

SYSID uid 
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP groupname [, ...]
| VALID UNTIL 'abstime' 

c:\>psql template1 postgres
Password:
Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

template1=# create user yasi
template1-# with sysid 5001 password '1234'
template1-# valid until 'infinity';
CREATE USER
template1=# create user sibal
template1-# with sysid 5002
template1-# valid until 'infinity';
CREATE USER

이제 생성한 사용자에 대한 그룹을 생성하도록 하자.

template1=# create group "user"
template1-# with sysid 5000
template1-# user yasi;
CREATE GROUP

sibal 사용자도 user 그룹에 속하도록 해보자. 그리고 sibal사용자에 대한 패스워드도 설정하자.

template1=# alter group "user" add user sibal;
ALTER GROUP
template1=# alter user sibal with password '1234';
ALTER USER

문법은 다음과 같다.

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] dbowner ]
[ LOCATION [=] 'dbpath' ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ] ]

다음과 같이 psql을 이용하여 template1 데이터베이스로 접근한다.

c:\>psql template1 postgres
Password:
Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

template1=#

데이터베이스를 만들기 전에 먼저 테이블스페이스를 만든다. 테이블스페이스는 논리적인 데이터베이스 영역이며, 여러개의 파일을 포함한다. 논리적으로 볼 때 사용자는 하나의 테이블스페이스에 접근하는 것처럼 보이지만 실제로는 여러개의 파일에 접근하는 것이다. 짜증나게도 도움말에 테이블스페이스의 문법이 없다. 테이블스페이스를 만드려면 먼저 경로에 디렉토리를 하나 만다.

template1=# CREATE TABLESPACE UserTBS
template1-# OWNER postgres
template1-# LOCATION 'C:\\PostgresData';
CREATE TABLESPACE

이제 데이터베이스를 만들어 보도록 하자. 명령프롬프트에서 createdb 명령어를 실행한다. 주의할 것은 template1에 사용자가 없어야 한다는 것이다. 만약 다른 사용자가 접근중이라면 다음과 같은 메세지를 뿌린다.

c:\>createdb -DUserTBS -EEUC_KR -Oyasi -Uyasi UserDB
Password:
createdb: database creation failed: ERROR: source database "template1" is being accessed by other users

쩝...어쨌든 설치하자마자 다른 데이터베이스를 생성해 주어야 한다.

c:\>createdb --help
createdb creates a PostgreSQL database.

Usage:
createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
-D, --tablespace=TABLESPACE default tablespace for the database
-E, --encoding=ENCODING encoding for the database
-O, --owner=OWNER database user to own the new database
-T, --template=TEMPLATE template database to copy
-e, --echo show the commands being sent to the server
-q, --quiet don't write any messages
--help show this help, then exit
--version output version information, then exit

Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-W, --password prompt for password

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@postgresql.org>.

c:\>createdb -DUserTBS -EEUC_KR -Oyasi -Uyasi UserDB
Password:
CREATE DATABASE

c:\>

생성한 DB에 접근해 보도록 하자.

c:\>psql UserDB yasi
Password:
Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

UserDB=>

잘된다..우헐헐~

이 예제는 Databases with PostgreSQL , 정보문화사, Richard Neil 공저, 이주호역의 3장의 예제이다. pgAddminIII를 띄우고 해당 DB를 마우스로 클릭하면 SQL이라는 도구버튼이 활성화된다. 그것을 클릭하면 SQL을 입력하고, 실행하고, 결과를 볼 수 있는 클라이언트 툴을 볼 수 있다. 이 설명이 뭔소리인지 모르겠면 마우스 노가다를 통하여 습득해라. 아니면 다른 웹문서..아니면 다른책...아무튼 직접해보려면 예가 필요한 것이다.
뭐..그리 좋지많은 안다. 데이터가 실제로 연결되어 있지 않기 때문에 제약조건에 걸리지는 않는다. 뭐..대충 야시가 ERD를 그려보았다. 그렸다기 보다는 그냥 선 몇개 그어보았다. SQL스크립트 상에서는 관계가 맺어져 있지 않은 것을 볼 수 있으나.. 아래 그림은 야시가 관계를 맺었다는 가정하에 그려본 것이다..

customer
item
orderinfor
stock
orderline
barcode

이렇게 6개의 테이블이다.

create table customer
(
customer_id serial ,
title char(4) ,
fname varchar(32) ,
lname varchar(32) not null,
addressline varchar(64) ,
town varchar(32) ,
zipcode char(10) not null,
phone varchar(16) ,
CONSTRAINT customer_pk PRIMARY KEY(customer_id)
);


create table item
(
item_id serial ,
description varchar(64) not null,
cost_price numeric(7,2) ,
sell_price numeric(7,2) ,
CONSTRAINT item_pk PRIMARY KEY(item_id)
);


create table orderinfo
(
orderinfo_id serial ,
customer_id integer not null,
date_placed date not null,
date_shipped date ,
shipping numeric(7,2) ,
CONSTRAINT orderinfo_pk PRIMARY KEY(orderinfo_id)
);


create table stock
(
item_id integer not null,
quantity integer not null,
CONSTRAINT stock_pk PRIMARY KEY(item_id)
);


create table orderline
(
orderinfo_id integer not null,
item_id integer not null,
quantity integer not null,
CONSTRAINT orderline_pk PRIMARY KEY(orderinfo_id, item_id)
);


create table barcode
(
barcode_ean char(13) not null,
item_id integer not null,
CONSTRAINT barcode_pk PRIMARY KEY(barcode_ean)
);

insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Miss','Jenny','Stones','27 Rowan Avenue','Hightown','NT2 1AQ','023 9876');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Andrew','Stones','52 The Willows','Lowtown','LT5 7RA','876 3527');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Miss','Alex','Matthew','4 The Street','Nicetown','NT2 2TX','010 4567');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Adrian','Matthew','The Barn','Yuleville','YV67 2WR','487 3871');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Simon','Cozens','7 Shady Lane','Oahenham','OA3 6QW','514 5926');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Neil','Matthew','5 Pasture Lane','Nicetown','NT3 7RT','267 1232');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Richard','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Ann','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Christine','Hickman','36 Queen Street','Histon','HT3 5EM','342 5432');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Mike','Howard','86 Dysart Street','Tibsville','TB3 7FG','505 5482');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Dave','Jones','54 Vale Rise','Bingham','BG3 8GD','342 8264');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Richard','Neill','42 Thached way','Winersby','WB3 6GQ','505 6482');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mrs','Laura','Hendy','73 Margeritta Way','Oxbridge','OX2 3HX','821 2335');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','Bill','O\''Neill','2 Beamer Street','Welltown','WT3 8GM','435 1234');
insert into customer(title, fname, lname, addressline, town, zipcode, phone) values('Mr','David','Hudson','4 The Square','Milltown','MT2 6RT','961 4526');

insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(3,'2000-03-13','2000-03-17', 2.99);
insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(8,'2000-06-23','2000-06-24', 0.00);
insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(15,'2000-09-02','2000-09-12', 3.99);
insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(13,'2000-09-03','2000-09-10', 2.99);
insert into orderinfo(customer_id, date_placed, date_shipped, shipping) values(8,'2000-07-21','2000-07-24', 0.00);

insert into stock(item_id, quantity) values(1,12);
insert into stock(item_id, quantity) values(2,2);
insert into stock(item_id, quantity) values(4,8);
insert into stock(item_id, quantity) values(5,3);
insert into stock(item_id, quantity) values(7,8);
insert into stock(item_id, quantity) values(8,18);
insert into stock(item_id, quantity) values(10,1);

insert into barcode(barcode_ean, item_id) values('6241527836173', 1);
insert into barcode(barcode_ean, item_id) values('6241574635234', 2);
insert into barcode(barcode_ean, item_id) values('6264537836173', 3);
insert into barcode(barcode_ean, item_id) values('6241527746363', 3);
insert into barcode(barcode_ean, item_id) values('7465743843764', 4);
insert into barcode(barcode_ean, item_id) values('3453458677628', 5);
insert into barcode(barcode_ean, item_id) values('6434564564544', 6);
insert into barcode(barcode_ean, item_id) values('8476736836876', 7);
insert into barcode(barcode_ean, item_id) values('6241234586487', 8);
insert into barcode(barcode_ean, item_id) values('9473625532534', 8);
insert into barcode(barcode_ean, item_id) values('9473627464543', 8);
insert into barcode(barcode_ean, item_id) values('4587263646878', 9);
insert into barcode(barcode_ean, item_id) values('9879879837489', 11);
insert into barcode(barcode_ean, item_id) values('2239872376872', 11);

insert into item(description, cost_price, sell_price) values('Wood Puzzle', 15.23, 21.95);
insert into item(description, cost_price, sell_price) values('Rubic Cube', 7.45, 11.49);
insert into item(description, cost_price, sell_price) values('Linux CD', 1.99, 2.49);
insert into item(description, cost_price, sell_price) values('Tissues', 2.11, 3.99);
insert into item(description, cost_price, sell_price) values('Picture Frame', 7.54, 9.95);
insert into item(description, cost_price, sell_price) values('Fan Small', 9.23, 15.75);
insert into item(description, cost_price, sell_price) values('Fan Large', 13.36, 19.95);
insert into item(description, cost_price, sell_price) values('Toothbrush', 0.75, 1.45);
insert into item(description, cost_price, sell_price) values('Roman Coin', 2.34, 2.45);
insert into item(description, cost_price, sell_price) values('Carrier Bag', 0.01, 0.0);
insert into item(description, cost_price, sell_price) values('Speakers', 19.73, 25.32);

insert into orderline(orderinfo_id, item_id, quantity) values(1, 4, 1);
insert into orderline(orderinfo_id, item_id, quantity) values(1, 7, 1);
insert into orderline(orderinfo_id, item_id, quantity) values(1, 9, 1);
insert into orderline(orderinfo_id, item_id, quantity) values(2, 1, 1);
insert into orderline(orderinfo_id, item_id, quantity) values(2, 10, 1);
insert into orderline(orderinfo_id, item_id, quantity) values(2, 7, 2);
insert into orderline(orderinfo_id, item_id, quantity) values(2, 4, 2);
insert into orderline(orderinfo_id, item_id, quantity) values(3, 2, 1);
insert into orderline(orderinfo_id, item_id, quantity) values(3, 1, 1);
insert into orderline(orderinfo_id, item_id, quantity) values(4, 5, 2);
insert into orderline(orderinfo_id, item_id, quantity) values(5, 1, 1);
insert into orderline(orderinfo_id, item_id, quantity) values(5, 3, 1);