공개용 데이터베이스 서버 PostgreSQL (3)

공개용 데이터베이스 서버 PostgreSQL (3)

한동훈 (하이텔 리눅스동 대표시삽)

1. 들어가면서

지난 시간에는 PostgreSQL의 설치와 간단한 테스트에 이어, PostgreSQL의 사용을 도와주는 외부 명령어를 살펴보았다. 이번 시간에는 PostgreSQL에서 제공하는 데이터 타입과 데이터베이스 질의어인 SQL에 대해서 살펴보겠다.

PostgreSQL에서의 데이터 타입은 상당히 많고 폭이 넓다. 생각도 하지 못한 듯한 데이터 타입들이 있으며, 공학용이나 복잡한 기하학 용도로 사용될 법한 데이터 타입들도 준비외어 있다. 물론, 이러한 데이터 타입조차도 사용자 마음대로 정의하여 사용할 수 있다는 장점이 있다.
그리고 SQL 질의어도 마찬가지로 확장성이 뛰어나다. 사실, PostgreSQL 6.2에서 새롭게 지원하는 필드 제한, 보충 기능들과 트리거 기능은 이전 버전에서도 PostgreSQL의 SQL확장을 사용하여 해결할 수 있었다. 이제 본격적으로 PostgreSQL의 기능들을 마음껏 누려 보도록 하자.

2. 데이터 타입

PostgreSQL 6.2에서 제공하는 터이터 타입은 다음과 같다.

1) 기본 데이터 타입

abstime   : 절대 날짜와 시간
aclitem    : 엑세스 제어 목록 아이템
bool        : 부울런(논리) 값
box         : 2차원 사각형
bpchar     : 공백 채움 문자
bytea       : 가변길이의 바이트 배열
char        : 문자
char2      : 2 문자의 배열
char4      : 4 문자의 배열
char8      : 8문자의 배열
char16     : 16문자의 배열
cid          : 명령 식별 타입
date        : ANSI SQL 데이터 타입
datetime : 범용 날짜와 시간
filename : 거대 객체의 파일이름
int2         : 부호있는 2바이트 정수
int28       : int2의 8 배열
int4         : 부호있는 2바이트 정수
float4      : 단정도 부동소수
float8      : 배정도 부동소수
lseg        : 2차원 선 구간
money     : 고정 정밀도를 가지는 십진수 타입
name      : 저장 시스템 식별자를 위한 다중 문자 타입
oid         : 객체 식별자 타입
oid8        : oid의 8 배열
oidchar16 : oid 와 char16의 조합
oidint2    : oid 와 int2의 조합
oidint4    : oid 와 int4의 조합
path        : 열렸거나 닫혀진 선 구간
point       : 2차원 기하학 점
polygon : 2차원 다각형 (닫혀진 path와 동일)
circle      : 2차원 원 (중심점과 반경)
regproc   : 등록된 프로시저
reltime     : 상대 날짜와 시간 간격
smgr       : 저장 관리자
text         : 가변길이의 문자 배열
tid          : 튜플 식별자 타입
time        : ANSI SQL 시간 타입
timespan : 범용 시간 간격
timestemp : 제한 범위 ISO형식의 날짜와 시간
tinterval   : 시간 간격(절대시작시각과 절대종료시각)
varchar    : 가변길이의 문자들
xid           : 트랜잭션 식별자 타입

2) SQL/92와 PostgreSQL 의 비교

SQL/92 타입과 동일한 PostgreSQL 데이터 타입은 다음과 같다.

PostgreSQL 타입	SQL/92 타입	의미
char(n)	charater(n)	고정 길이 문자열
varchar(n)	charater varying	가변 길이 문자열
float4/8	float(p)	정밀도 p를 가지는 부동 소수
float8	double precision	배정도 부동 소수
float8	real	배정도 부동 소수
int2	smallint	부호있는 2바이트 정수
int4	int	부호있는 4바이트 정수
int4	intgre	부호있는 4바이트 정수
int4	decimal(p,s)	p<=9, s=0 인 수치
int4	numeric(p,s)	p==9, s=0 인 수치
timestemp	time zone이 있는 timestemp	날짜와 시간
timestemp	interval	범용 시간 간격

3) 날짜/시간 데이터 타입

위에서 나열한 날짜/시간 데이터에 대해서 잠깐 살펴보자

datetime
이 데이터 타입은 범용 날짜/시간타입으로 다양한 형태의 입력을 허용한다. 입력 형태는 ISO호환, SQL 호환, PostgreSQL 방식이 될 수 있다. 출력형태는 set 명령을 사용하여 다양하게 조절할 수 있다. datetime 타입은 다음의 문법을 사용한다.

년-월-일 [시:분:초] [AD, BC] [Timezone]

년월일 [시:분:초] [AD, BC] [Timezone]

월일 [시:분:초]년 [AD, BC] [Timezone]

유효한 날짜는 Nov 13 00:00:00 4013 BC GMT에서 미래까지이다. 시간 지대는 GMT, PST, KST 와 같은 세문자짜리이다. 시간은 GMT(Greenwich Mean Time) 로 저장되며, 입출력 함수는 이들 시간을 서버의 지역시간으로 변환한다.
그리고 값 지정시에 current, infinity, -infinity을 사용할 수 있다.

current는 값이 계산 될 때의 현재 시간이며, infinity는 유효 시간 이후를 말하며, -infinity는 유효시간 이전을 뜻한다. 그 외에도 now, today, yesterday, tomorrow, epoch를 사용할 수 있다. current는 항상 계산 현재시간으로 변경되지만 now, today 등은 바로 계산되어 상수로 저장된다는 점에서 다르다. epoch는 jan 1 00:00:00 1970 GMT을 이야기한다.

mydb=> create table test_datetime (dt datrtime);
CREATE
mydb=> insert into test_datetime values ('1997-11-24 21:17:00 KST);
INSERT 535342 1
mydb=> select *from test_datetime;
dt
-----------------
Mon Nov 24 21:17:00 1997 KAT
(1 row)

mydb=> ＼h set
Command: set
Description: set run-time environment
Syntax:
set DateStyle to {'ISO' | 'SQL' | 'Postgres' | 'European' | 'US' | 'NonEuropean'}
set GEQO to {'ON[=#]' | 'OFF'}
set R_PLANS to {'ON'| 'OFF'}

mydb=>set DateStyle to 'SQL' ;
SET VARIABLE
mydb=> select *from test_datetime;
dt
------------------
11/24/1997 21:17:00.00 KST
(1 row)

timespan
timespan은 범용 시간간격 데이터 타입이다. datetime과 마찬가지로 출력형식은 set 명령으로 다양하게 설정할 수 있다. 입력형식은 다음과 같다.

Quantity Unit [Quantity Unit...] [Direction]

Direction 에는 ago를 사용할 수 있다.

mydb=> create table test_timespan (tt timespan);
CREATE
mydb=> insert into test_timespan values ('-10 hour');
INSERT 535357 1
mydb=> select *from test_timespan;
tt
----------------
@ 10 hours ago
(1 row)

abstime
절대 시간(abstime 포함)은 제한된 범위(+/-68 년)와 초단위까지 저장할 수 있는 날짜 타입이다. 절대 시간은 다음의 형식을 따른다.

Month Day [Hour : Minute : Second] Year [Timezone]

유효날짜 범위는 Dec 13 20:45:53 1901 GMT에서 Jan 19 03:14:04 2038 GMT 까지 이며, 입출력 루틴은 기본적으로 지역시간지대를 따른다.

mydb=> create table test_abstime (ta abstime);
CREATE
mydb=> insert into test_abstime values ('11 25 23:33:25 1997 PST');
INSERT 535372 1
mydb=> select *from test_abstime;
tt
----------------
11/26/1997 16:33:25.00 KST
(1 row)

reltime

상대시간도 기본적으로 절대 시간과 유사하다. 시간의 범위는 +/-68년이며, 정밀도는 초단위까지이다. 형식은 다음과 같다.

@ Quantity Unit [Direction]

mydb=> create table test_reltime (tr reltime);
CREATE
mydb=> insert into test_reltime values ('@10 day ago');
INSERT 535387 1
mydb=> select *from test_reltime ;
tr
----------------
@10 day ago
(1 row)

4) 기하학 데이터 타입

point
평면에서 하나의 점을 나타낼 때 사용되는 데이터 타입이다.
데이터 형식은 다음과 같다.

(x, y)
x, y

x와 y는 각각 평면에서 x축, y축 값을 나타내는 부동 소수이다.

mydb=> create table test_point (p point);
CREATE
mydb=> insert into test_point values ('128, 215');
INSERT 535438 1
mydb=> insert into test_point values ('512, 640');
INSERT 535439 1
mydb=> insert into test_point values ('978.25, 1075.103');
INSERT 535440 1
mydb=> select *from test_point ;
p
----------------
(128, 215)
(512, 640)
(978.25, 1075.103)
(3 rows)

lseg
선 구간은 점 두 개로 구성된다. lseg 데이터 타입은 다음 형식으로 지정한다.

((x1, y1) , (x2, y2))
(x1, y1) , (x2, y2)
x1, y1 , x2, y2

(x1, y1)와 (x2, y2)는 선구간의 끝점이다.

mydb=> create table test_lseg (line lseg);
CREATE
mydb=> insert into test_lseg values ('0,0,480,640');
INSERT 535455 1
mydb=> insert into test_lseg values ('(640,0), (0,480)');
INSERT 535456 1
mydb=> select *from test_lseg ;
line
----------------
[(0,0),(480,640)]
[(640,0),(0,480)]
(2 rows)

box
box 데이터 타입은 두 개의 점으로 구성된다. 즉, 두 점을 반대쪽 꼭지점으로 하는 사각형을 구성하는 것이다.
box의 형식은 다음과 같다.

((x1, y1) , (x2, y2))
(x1, y1) , (x2, y2)
x1, y1 , x2, y2

mydb=> create table test_box (사각형 box);
CREATE
mydb=> insert into test_box values ('0,0,640,480');
INSERT 535471 1
mydb=> select *from test_box ;
사각형
----------------
(640,480),(0,0)
(1 row)

path
path 데이터 타입은 점의 집합으로 구성된다. 첫 번째 점과 마지막 점이 이어지지 않는다면 경로(path)는 open 이 되고, 이어진다면 close 가 된다. popen(p) 와 pclose(p)함수는 path데이터형 p를 강제로 open하거나 close 상태로 만든다. isopen(p)와 isclosed(p) 함수는 질의어에서 둘 중 하나를 선택하도록 한다.

path 데이터의 입력 형식은 다음 중 하나르 사용할 수 있다.

((x1, y1) , ... , (xn, yn))
[(x1, y1) , ... , (xn, yn)]
  (x1, y1) , ..., (xn, yn)
  (x1, y1 , ..., xn, yn)
   x1, y1 , ..., xn, yn

(x1, y1) , ..., (xn, yn)은 1에서 n 까지의 점이다.
"["는 열려진 경로를 나타낸다.
"("는 닫혀진 경로를 나타낸다.

mydb=> create table test_path (길 path);
CREATE
mydb=> insert into test_path values ('((10,10),(10,20),(20,30),(30,20),(30,10))');
INSERT 535486 1
mydb=> insert into test_path values ('[(10,10),(10,20),(20,30),(30,20),(30,10)]');
INSERT 535487 1
mydb=> select *from test_path ;
길
----------------
((10,10),(10,20),(20,30),(30,20),(30,10))
[(10,10),(10,20),(20,30),(30,20),(30,10)]
(2 row)

mydb=> select isopen(길) from test_path
isopen
----
f
t
(2 rows)

polygon
다각형(polygon)도 path와 마찬가지로 여러 개의 점으로 구성된다. 다각형은 당연히 닫혀지는 것을 기본으로 해야 한다. 이것만 제외하면, 데이터 입력 형식은 path와 동일하다.

circle
원은 중심점과 반지름으로 구성된다. 형식은 다음 중 하나를 사용 할 수 있다.

<(x, y), r >
((x, y), r )
(x, y), r
x, y, r

(x, y)는 원의 중심점이고 r은 반지름이다.

mydb=> create table test_circle (원 circle);
CREATE
mydb=> insert into test_circle values ('0,0, 100');
INSERT 535502 1
mydb=> select *from test_circle ;
원
----------------
<(0,0), 100>
(1 row)

지금까지 데이터 타입을 살펴 보았다. 이들은 PostgreSQL 6.2 기준이다. path 나 polygon, circle 등은 PostgreSQL 버전에 따라 조금 다른점이 있으므로 주의 하여야 한다.
이제 PostgreSQL에 내장되어 있는 오퍼레이터를 살펴보자. 조금 지겹더라도 기초를 튼튼히 하다보면 나중에 써멱을 떄가 반드시 있을 것이다. 그리고 자세히 알아두는 과정에서 PostgreSQL가 아주 재미있다는 것을 느낄 수 있을 것이다.

3. 내장 오퍼레이터와 함수

1)오퍼레이터

PostgreSQL 는 정말 많은 양의 내장 오퍼레이터를 제공한다. 이들 오퍼레이터는 pg_operator 시스템 카탈로그에 해당 오퍼레이터를 구현하는 프로시저의 객체 ID와 함께 정의되어 있다.

mydb=>select count(*) from pg_operator:
count
-----
488
(1 row)

488개의 오퍼레이터가 내장되어 있음을 알 수 있다. 나중에 오퍼레이터를 정의하는 방법을 설명할 때 알 수 있겠지만, PostgreSQL 에서는 대상 객체 타입에 따라 오퍼레이터가 다른 의미를 가진다. 즉 '<' 오퍼레이터라 하더라도 여러 데이터 타입에 작동하도록 할 수 있으며, 이 경우에는 PostgreSQL 에 해당 데이터 타입이 정의되어 있거나, 사용자가 정의하여야 한다. 즉, 형식적으로는 '>'와 같은 오퍼레이터 모양을 띄지만, 실제적으로는 앞뒤의 대상 데이터 타입을 참조하여 이에 맞는 프로시저를 실행하는 것이다.

일반적으로는 다음과 같이 사용할 것이다.

select * from emp where salary <40000;

반대로 오퍼레이터를 구현하는 함수를 직접 호출할 수도 있다.
이 경우에 위의 질의어는 다음과 같다.

select * from emp where int4lt(salary, 40000);

이들 오퍼레이터와 프로시저 목록은 'man pgbuiltin' 명령으로 참고 하기를 바라고, 여기서는 간단한 활용 예를 보이겠다.
두 점 사이의 거리를 구하려면 다음과 같이 하면 된다.

mybd=>select '25,25' ::point<-> '0,0' ::point as distance;
distance
-------------
35.3553390593274
(1 row)

여기에서 명시적인 형변환을 하기 위해 '::' 기호를 사용하였다. <->오퍼레이터는 A와 B사이의 거리를 구하는 오퍼레이터이다.
사각형의 박스가 서로 겹치는가도 알아볼 수 있다.

mybd=>select '0,0,640,480' ::box &&'400,300,1000,800' ::box as 박스겹침여부;
박스겹침여부
-------------
t
(1 row)

원의 포함여부도 일상적인 오퍼레이터를 사용하여 쉽게 알 수 있다. 이에 해당하는 프로시저가 이미 만들어져 PostgreSQL 에 내장되어 있음을 다시 상기하자.

mybd=>select '0,0,50' ::circle @'0,0,100' ::circle as A는B에포함될까;
a는b에포함될까
-------------
t
(1 row)

그 외의 유용한 여러 오퍼레이터가 있으므로 맨 페이지를 참고 하기 바란다.

2)오라클 호환 함수

오퍼레이터와 함수는 밀접한 관련이 있다. 오퍼레이터는 함수로 구현된다. 여기서는 RDBMS 로는 가장 많이 알려져 있는 오라클호환 함수를 잠시 살펴보기로 한다. PostgreSQL 6.2에서 구현된 함수는 다음과 같다.
이들 함수는 모두 text 데이터 타입에서 동작한다는 것에 주의해야 한다. 즉, varchar 와 같은 데이터형에서는 동작하지 않는다.(varchar 일 경우에는 명시적인 형변환을 하거나, 이들 함수가 varchar를 받아들이도록 조치를 취해야 한다.)

lower (text)
소문자로 변환한다.

mydb=>select lower('Linux');
lower
----
linux
(1 row)

upper (text)
대문자로 변환한다.

mydb=>select upper('Linux');
upper
----
LINUX
(1 row)

initcao (text)
단어의 첫글자를 대문자로 변환하고, 나머지는 소문자로 한다. 단어는 공백문자로 구분한다.

mydb=>select initcap('linux is not trademark');
initcap
----------------
Linux is Not Trademark
(1 row)

lpad (text1, len [,text2])
text1 의 왼쪽에 text2 를 전체길이가 len 이 되도록 채운다. text2 가 없다면 기본적으로 공백문자가 사용된다.

mydb=>select lpad('linux', 30, 'world');
lpad
----------------
worldworldworldworldworldlinux
(1 row)

rpad (text1, len [,text2])
text1의 오른쪽에 text2 를 전체길이가 len 이 되도록 채운다. text2 가 없다면 기본적으로 공백문자가 사용된다.

mydb=>select rpad('linux', 30, 'world');
rpad
----------------
linuxworldworldworldworldworld
(1 row)

ltrim (text[,set])
text 문자열에서 set문자열 중 가장 먼저 나타나는 문자부터 매칭되는 데까지 제거하여 반환한다.

mydb=>select ltrim('linux world', 'wlinud');
ltrim
----------------
x world
(1 row)

rtrim (text[,set])
rtrim 은 ltrim 의 반대되는 함수이다.

mydb=>select rtrim('linux', 'ux');
rtrim
----------------
lin
(1 row)

substr (text, m [,n])
text 문자열에서 m 번째에서 n 길이 만큼의 문자열을 돌려준다.

mydb=>select substr('linux world', 1,5);
substr
----------------
linux

translate (text, from, to)
text 문자열에서 from 문자를 찾아서 to 로 대체한다.

mydb=>select translate('ms', 's', '$');
translate
----------------
m$

그 외의 함수들

그 외에 사용되는 일반함수를 살펴보도록 하자.

정수를 부동소숫점으로 변환

float8 float8(int)
float4 float4(int)

부동소숫점을 정수로 변환

int4 int4(float)
int2 int2(float)

절대시간 관련

bool isfinite(abstime) abstime 이 finite 시간이면 TRUE를 돌려준다.
datetime datetime(abstime) abstime 을 datetime 으로 변환한다.

date 관련

datetime datetime(date) date 타입을 datetime 타입으로 변환한다.
datetime datetime(date, time) date 타입과 time 타입을 datetime 으로 변환한다.

datetime 관련

timespan age(datetime,datetime)     연과 월의 차이를 구한다.
float8 date_part(text,datetime)         date 필드의 포션을 지정한다.
datetime date_trunc(text,datetime)   date 를 지정한 유닛으로 자른다.
bool isfinite(datetime)                     finite 시간이면 TRUE 를 돌려준다.
abstime abstime(datetime)              abstime 으로 변환한다.

reltime 관련

timespan timespan(reltime) timespan 으로 변환한다.

time 관련

datetime datetime(date,time) datetime 으로 변환한다.

box 관련

box box(point,point) 점을 상자로 변환한다.
float8 area(box) 상자의 넓이를 구한다.

mydb=>select area(사각형) from test_box;
area
-----
307200
300000
(2 rows)

path 관련

bool isopen(path) 열려진 경로이면 TRUE를 반환한다.
bool isclosed(path) 닫혀진 경로이면 TRUE를 반환한다.

circle 관련

circle circle(point,float8)        원으로 변환한다.
polygon polygon(npts,circle)   원을 npts 개의 점을 가지는 다각형으로 변환한다.
  float8 center(circle)              원의 중심점을 구한다.
  float8 radius(circle)              원의 반지름을 구한다.
  float8 diameter(circle)          원의 직경을 구한다.
  float8 area(circle)                 원의 넓이를 구한다.

mydb=>select area(원) from test_circle ;
area
-------------
31415.9265358979
7853.98163397448
(2 rows)

기타 텍스트 관련 함수

text position(subtext in text)
text 에서 subtext 가 있는 위치를 돌려준다.

mydb=>select position('world' in 'linux world');
strpos
-----
7

4. PostgreSQL 에서의 SQL

PostgreSQL 은 표준 SQL 에 나오는 상당히 많은 SQL 명령을 제공하지만 조금의 약점이 있다. PostgreSQL 6.2.1 까지를 기준으로 볼 때, PRIMARY KEY, UNIQU, FOREIGN KEY, REFERENCES, SUBSELECT, HAVING 절 등을 지원하지 못하고 있다. 물론 이러한 것들은 PostgreSQL에서 다른 기능을 사용하여 충족시킬 수 있지만, 불편한 것은 사실이다. PostgreSQL 6.2 에 들어와서 NOT NULL, DEFAULT, CONSTRANT CHECK 등을 새롭게 지원하기 시작하여, 올해 안에 발표될 6.3에서는 여지껏 지원하지 못했던 표준 SQL 의 기능을 새롭게 많이 추가될 것으로 보인다.

이제, PostgreSQL 의 SQL 기능들을 자세하게 살펴보도록 하자. 질의어별로 나누어 보지 않고 관련 기능별로 구분하여 살펴보겠다.

1) 테이블

테이블은 DBMS 데이터베이스에서 가장 기본적인 개념으로, 실제 데이터들이 일정한 형태를 띄며 가시적으로 저장되는 곳이다. 따라서 테이블들을 어떻게 만드느냐에 따라 데이터베이스의 성능과 효율이 문제가 된다. 테이블 생성문제를 그냥 '필드 가지고 놀기' 라고 보는 관점은 그리 좋지 않다. 프로그램을 작성할 때 설계가 중요하듯이 데이터베이스 시스템을 구축할 때에도 설계가 중요하다. 한번 설계를 잘못하면 오랜 시간동안 고생을 하고 시스템 비용을 낭비하지만, 한번 설계를 잘 해두면 두고두고 이득을 본다. 즉, 데이터의 성질과 필드의 성격, 인덱스의 활용 여부, 제한 규정 등을 잘 파악하고 활용하여야 한다.

먼저, 테이블 생성에 대해 알아보자.

테이블 생성
테이블은 CREATE TABLE 명령을 사용하여 만든다. 문법은 다음과 같다.

create table classname (attname type [not null] [default value]
     [, attname type [default value] [not null]])
     [inherits ( classname [, classname])]
     [constraint cname check (test) [, check(test)]]
     [archive = archive_mode]
     [store = "smgr_name"]
     [arch_store = "smgr_name"]

arch_store 는 새로운 클래스에서 사용할 저장 시스템을 지정한다. store 는 현재의 데이터 배치를 제어하고, arch_store 는 역사적인 데이터 배치를 제어한다. arch_store 는 archive 가 지정될 때에만 지정될 수 있다. store 와 arch_store 는 'magnetic disk' 만 지정할 수 있으며, 기본 값도 'magnetic disk' 이다.

arch_store 는 none, light, heavy 중 하나가 될 수 있다. 쓸 일이 없으니 신경 쓰지 않아도 된다.

위의 문법이 조금 복잡한 듯이 보이지만 사실은 간단하다. 가장 간단한 형태의 테이블 정의는 다음과 같다.

CREATE TABLE emppay (
name text,
wage float4
) ;

NOT NULL 과 DEFAULT, CONSTRAINT를 추가하여 해당 필드에 대해 추가 제어를 하여 테이블을 만들 수도 있다.

테이블 정의에서 INHERITS 는 상속관계를 나타낸다.

CREATE TABLE emppay (
    name text NOT NULL,
    wage float4 DEFAULT 10.00
) CONSTRAIN empcon CHECK (wage > 5.30 and wage <=30.00),
               CHECK (name<> ") ;

deppay 테이블(클래스)은 이제 emppay 테이블(클래스)의 name, wage 필드(속성)을 포함하게 된다. 즉, emppay 로부터 상속을 받게 되는 것이다. 이러한 상속은 단순히 필드(속성)을 쉽고 일관성 있게 만들 수 잇다는 장점뿐만이 아니라, 상속 속성을 이용하여 데이터관리를 조리있게(객체지향적으로!!) 할 수 있다는 것을 의미한다.

CREATE TABLE deppay (
department text,
) INHERITS (emppay) ;

데이터 추가
데이터추가는 INSERT 명령을 사용한다. 문법은 다음과 같다. 여러 형태의 INSERT를 사용하여 데이터를 추가할 수 있다.

INSERT INTO classname
     [(att.expr-1 [,att_expr.i] )]
       {VALUES (expression 1[, expression-i] )|
     SELECT expression1 [, expression-i]
       [from from-list] [where qual]

INSERT 구문은 때로는 번거롭다.

INSERT INTO emppay VALUES ('linux', 25.03) ;
INSERT INTO emppay (name) values ('hacker') ;
INSERT INTO emppay
SELECT user FROM user ;

데이터를 한꺼번에 입력하고 싶을 때가 자주 있을 것이다. 그럴 때는 copy 명령을 사용하면 된다.

','는 입력시에 필드 구분자이다. '＼'는 표준입력에서 데이터 입력을 종료할 때 사용한다.

COPY [binary] <class_name> [with oids]
{to|from} {<filename>|stdin|stdout} [using delimiters <delim>] ;
mydb=>COPY emppay from stdin using delimiters ',' ;
Enter info followed by a newline
End with a backslash and a period on a line by itself.
>> beginner, 17.03
>> power user, 20.88
>> ＼.

copy 는 아스키 모드와 바이너리 모드로 처리된다. 기본은 아스키 모드이며 대부분 별 문제없다. 가끔은 이진 데이터 파일에서 입력을 받고자 하는 경우가 있는 경우라면 binary 예약어를 사용해 볼만 하다.

파일에서 데이터를 읽어 바로 테이블에 저장하고자 한다면, psql 의 내부 명령어인 ＼copy 명령을 사용하면 된다. 위의 copy 명령에서 파일을 대상으로 하는 것은 PostgreSQL 슈퍼사용자일 경우에만 해당되므로 psql 명령어 사용 습관들이는 것이 좋다. 내부 문법은 다음과 같다.

＼copy class_name {from|filename
mydb=> ＼copy emppay from test.data
Successfully copied.

단, 필드 구분자 지정이 없으므로 주의해야 한다. 이들 copy 명령에서 기본 필드 구분자는 스페이스가 아니라 탭문자(!!)라는 사실을 꼭 기억해야 한다.

* 데이터 갱신
UPDATE 는 데이터를 갱신하는데 사용된다.

UPDATE classname SET attname-1 = expression-1
     [, attname-i = expression-i]
     [FROM from-list]
     [WHERE qual]

UPDATE emppy
     SET name = 'guru', wage = 25.00
     FROM emppay
     WHERE name = 'hacker' ;

테이블의 변경
가끔씩 테이블 명을 변경하거나, 테이블의 필드명을 변경해야 할 필요성이 있을 것이다. 또는 테이블의 구조를 변경해야 할 필요도 생긴다. 이럴 때 사용되는 명령이 ALTER 이다. ALTER 는 필드와 테이블 이름을 변경하거나, 테이블에 필드를 추가하는 기능을 한다. 아직 PostgreSQL 6.2 에서는 필드제거 기능은 들어있지 않다.

위에서 '*' 는 PostgreSQL 의 객체지향적 특성을 드러내는 자그마한 특징이다.

ALTER TABLE <class_name> [*] ADD COLUMN <attr> <type>
ALTER TABLE <class_name> [*] RENAME [column] <attr1> to <attr2>
ALTER TABLE <class_name1> RENAME to <class_name2>
ALTER TABLE emppay ADD hireday date;
ALTER TABLE emppay RENAME hireday to empday ;
ALTER TABLE emppay RENAME to emppay2;

즉, '*' 를 명시하면 class_name에서 상속받는 모든 클래스에 대해 해당 작업을 확장하는 것이다. 클래스의 상속관계는 클래스(테이블)을 생성할 때 지정할 수 있다. 만일 해당 테이블에서 emppay 필드와 같이 특정 필드를 제거하려고 한다면, 당분간은 번거롭지만 단순작업을 해야 한다.

SELECT name, wage
INTO TABLE tmp
FROM emppay2 ;
DROP TABLE emppay2 ;
ALTER TABLE tmp RENAME TO emppay2 ;

데이터의 삭제와 테이블의 제거
데이터의 삭제는 DELETE 명령을 사용한다.
WHERE 절을 생략하면 해당 테이블의 데이터를 모두 지우게 되므로 주의해야 한다. 그렇다 하더라도 테이블까지 제거되는 것은 아니다. 테이블은 drop으로 제거한다.

DELETE FROM class_name [WHERE qual]
DELETE FROM emppay2 WHERE wage = 10 ;

만일 emppay2 로부터 상속을 받은 테이블(클래스)가 있다면 경고가 떨어지고 제거되지 않는다. 이때에는 상속받은 테이블(클래스)부터 제거해야 한다.

DROP table emppay2 ;

데이터 검색
데이터 검색(또는 열, 컬럼 회수)은 유명한 SELECT 로 처리한다. SELECT 를 모르고는 SQL 을 안다고 할 수 없다. 문법은 다음과 같다.

distinct 는 중복되는 데이터를 하나로 보여주는 것이다.

SELECT [distinct]
     expression1 [as attr_name-1]
     {, expression1 [as attr_name-i]}
     [into table clessname]
     [from from-list]
     [where where-clause]
     [group by attr_name1 {, attr_name-i....}
     [order by attr_name1
     [using op1] {, attr_namei [using opi]}

as 는 SELECT 의 결과로 나타나는 필드명을 회수시에 임시적으로 변경하는 것이다.

SELECT 도시, 최저온도, 최고온도
    FROM 날씨
    WHERE 도시 = '서울' ;
SELECT 도시, 강수량 as 비온량, 날짜
    FROM 날씨
    WHERE 날짜 >= '1997-10-1'
    GROUP BY 날짜, 비온량
    ORDER BY 도시
    USING <;

따라서 다른 위치에서 사용될 때에는 as 다음의 임시 필드명을 사용해야 한다. '>=' 는 일반적으로 사용하는 비교부호이고, GROUP BY 는 회수하는 데이터를 어떤 덩어리(그룹)로 묶을 것인가를 지정하며, ORDER BY 는 정렬기준을 나타낸다. PostgreSQL 에서는 ASC, DESC 대신에 'USING<'와 'USING>'를 사용하여 오름차순, 내림차순으로 정렬할 수도 있다.

SELECT에서 클래스의 상속성을 이용하여 데이터를 검색할 수도 있다.
이 경우에는 SELECT 시에 emppay에서 상속받은 모든 클래스를 포함하여 검색한다.

SELECT c.name, c.wage
FROM emppay* c
WHERE c.wage>= 10 ;

'*' 가 역시 상속 클래스까지를 포함하는 클래스 오퍼레이터이다. PostgreSQL 의 SQL에서 정규표현식(regex)을 사용하여 데이터를 검색할 수 있다. 정규표현식 검색을 잘 활용하면 복잡한 작업도 쉽게 사용할 수 있다는 것을 잘 알고 있을 것이다. A ~ B는 A가 정규표현식 B를 포함한다는 것이다. 대소문자를 무시하여 검색하려면 A ~ *B를 사용하면 된다. 포함하지 않는 것을 검색할 때에는 !를 앞에 붙이면 된다.

정규표현식보다 좀더 사용하기 쉬운 와일드카드는 like를 사용할 수 있다.

^|][[:alpha:]]{5}/[[:digit:]]{2}' as find
    find
    ---
    f
    (1 row)

PostgreSQL 의 SELECT 절에서 아직 HAVING 절과 보조 SELECT 를 지원하지 않고 있다. 그 외의 기능은 일반 SQL 과 같다.

2) 뷰

뷰는 일종의 가상 테이블이다. 즉, 실제로는 하드 디스크에 어떠한 데이터를 물리적으로 저장하고 있지는 않지만, 테이블과 비슷하게 보이는 것이다. 뷰도 하나의 개체이며, 실제로는 하나의 질의어이다. 뷰를 처리할 때는 일반 테이블처럼 하면 된다. 하지만 뷰는 테이블처럼 정적인 것이 아니라, 뷰에 영향을 미치는 테이블이나 객체의 변동에 따라서 민감하게 변화하는 동적인 객체인 것이다. 뷰의 생성은 일반 테이블을 생성할 때 사용하는 방법과는 다르고, SELECT 로 원하는 데이터를 회수한 후 새로운 테이블에 삽입하는 방법과 비슷하다.

날씨라는 테이블에서 도시가 서울인 것만 선택해서 서울 날씨라는 뷰를 만들었다.

create view view_name as
     select expression1 [as attr_name1]
     {, expression_i [as attr_namei]}
     [from from.last]
     where qual

mydb=> CREATE VIEW 서울날씨 as
mydb-> select*
mydb-> from 날씨
mydb-> where 도시 = '서울' ;
CREATE
mydb=> select*from 서울날씨 ;

도시	최저온도	최고온도	강수량	날짜
서울	10	27	0	10-01-1997
서울	12	25	0.12	10-02-1997

(2 rows)

여기에서 서울날씨 뷰는 날씨 테이블에 의존을 하며, 날씨 테이블에서 서울날씨 뷰의 생성조건을 만족시키는 데이터가 입력되면 서울날씨 뷰에도 입력된 것으로 처리된다. 이것을 다음에서 확인해보자

날씨 테이블에 입력한 12월2일자 데이터가 서울날씨 뷰에 들어와 있는 것을 알 수 있다.

mydb=> insert into 날씨 values('서울', -10,8,0.0, '1997-12-2') ;
INSERT 535891 1
mydb=> select*from 서울날씨 ;

도시	최저온도	최고온도	강수량	날짜
서울	10	27	0	10-01-1997
서울	12	25	0.12	10-02-1997
서울	-10	8	0	12-02-1997

(3 rows)

그 역은 성립하지 않는다. 즉, 서울날씨 뷰에 조건을 만족시키는 데이터를 입력한다고 해서 날씨테이블에 나타나지는 않는 다는 것이다. 다음에서 확인할 수 있다.

mydb=> insert into 서울날씨 values ('서울', -5, 10, 0.0, '1997-12-1') ;
INSERT 535892 1
mydb=> select*from 날씨 where 도시 = '서울' ;

도시	최저온도	최고온도	강수량	날짜
서울	10	27	0	10-01-1997
서울	12	25	0.12	10-02-1997
서울	-10	8	0	12-02-1997

(3 rows)

만일, 서울날씨 뷰의 생성조건을 만족시키지 않는 데이터가 뷰에 입력되면, 어떤 결과가 나타날까? 한번 확인해보자.

mydb=> insert into 서울날씨 values('부산', 1, 15, 0.0, '1997-12-2') ;
INSERT 535893 1
mydb=> select*from 날씨 where 도시 = '부산';

도시	최저온도	최고온도	강수량	날짜
부산	13	28	0.32	10-01-1997

(3 rows)

서울날씨 뷰에도 뷰의 생성조건을 만족시키지 못하는 데이터는 에러는 생기지 않았지만 받아들이지 않고 있음을 알 수 있다.

mydb=> select*from 서울날씨 ;

도시	최저온도	최고온도	강수량	날짜
서울	10	27	0	10-01-1997
서울	12	25	0.12	10-02-1997
서울	-10	8	0	12-02-1997

(3 rows)

mydb=> drop view 서울날씨 ;
DROP
mydb=>

그리고 본래의 날씨 테이블에도 데이터가 추가되지 않았다. 여기에서 보았듯이 뷰는 일반적으로 테이블과 비슷하게 취급되지만, 뷰의 생성 기반이 된 테이블과의 관계와 뷰를 생성시킨 조건에 영향을 받는다는 것이 테이블과는 다른 점이다.

3) 인덱스

PostgreSQL 에서는 B-tree와 R-tree의 두 가지 인덱싱 방식을 지원하고 있다. 이들 알고리즘은 접근방식(access method)이라고 불리우며, 인덱스 생성시에 사용자가직접 지정할 수 있다. B-tree 는 주로 일반적인 키의 인덱싱에 사용되며, R-tree는 사각형 컴포넌트의 관계를 표현하는데 사용된다. 따라서, 특수한 공학용도가 아니라면 R-tree는 신경쓰지 않아도 되며, 기본 접근방식은 B-tree 이다.

여기에서 am-name 은 접근방식(access method)이며, funcname은 인덱싱에 사용할 사용자 정의 함수이다.

create [unique] index index-name
on classname [using am-name]
( attname [type_class], ...)

create [unique] index index-name
on classname [using am-name]
( funcname (attname-1 {, attname-i}) type_class)

가끔 특수한 데이터타입에 있어서 사용자가 직접 함수를 작성하여 인덱싱에 이용하는 것이 속도 향상에 상당한 효과를 거둘 수 있다. 인덱스를 만들 때, 하나의 컬럼에만 인덱스를 부여할 수도 있고, 여러 개의 컬럼에 동시에 인덱스를 부여할 수도 있다. 예를 들어, 날씨 테이블의 도시 컬럼에 인덱스를 생성해 보자.

unique index 는 중복되지 않은 유일한 인덱스를 말한다.

mydb=> create index weather_index on 날씨 using btree(도시) ;
CREATE
mydb=> drop index weather_index ;
DROP
mydb=> create index weather_index on 날씨 (도시, 날짜) ;
CREATE
mydb=> drop index weather_index ;
DROP
mydb=> create index weather_index on 날씨 (도시, 날짜) ;
CREATE
mydb=> drop index weather_index ;
DROP
mydb=>

PostgreSQL 6.2 에서는 B-tree방식에서만 다중 컬럼에 대한 인덱스를 지원하며, 총 7개의 컬럼까지 가능하다.

4) 함수

PostgreSQL 에서 함수는 SQL 함수와 프로그래밍 언어 함수가 있다. 프로그래밍 언어함수는 주로 C로 작성되며, SQL 함수는 SQL 질의어로 간단히 정의한다. 이들 모두 PostgreSQL에서 정한 규칙에 따라서 작성해야 한다.

이들 함수를 사용하면 subselect 기능을 대신할 수 있다.

create function function_name
     ([type1 {, type-n}])
     returns type-r
     as {'/full/path/to/objectfile' | 'sql-queries'}
     language {'c' 'sql' 'internal'}

먼저 SQL 함수를 만드는 예를 들어보겠다. '날씨' 테이블에서 '도시'를 인자로 주면 평균강수량을 구하는 함수를 정의한다.

여기에서 함수명과 함께 함수인자를 varchar 로 정의하였는데, text, float8 의 정규데이터 타입이나 테이블명도 인자로 줄 수 있다.

mydb=> ＼d 날씨 ;

Table = 날씨

Field	Type	Length
도시	varchar	20
최저온도	int4	4
최고온도	int4	4
강수량	float8	8
날짜	date	4

mydb=> create function 강수량평균(varchar) returns float8
mydb-> as 'select avg(강수량) from 날씨 where 도시 = $1 ;' language'sql' ;
CREATE
mydb=> select 강수량평균('서울') ;
강수량평균
0.04
(1 row)

mydb=>select 강수량평균('부산') ;
강수량평균
0.32
(1 row)

mydb=>

여기에서 $1은 SQL 함수 내부에서 인자를 참조하는 방법이다. C 언어로 함수를 만드는 방법은 PostgreSQL 소스 디렉토리의 contrib 안의 여러 소스를 참고하면 대충 알 수 있을 것이다. C 소스로 SQL 함수를 작성하였다면, -fpic 옵션으로 일차 컴파일을 한다.
음, -shared 옵션을 사용하여 so 공유 오브젝트 파일로 만들어야 한다.
그 다음 create function을 사용하여 함수를 정의한다.

$ gcc -fpic -c -o datetime_functions.o datetime_functions.c
-l../../src/include -l../../src/backend
$ gcc -shared -o datetime_functions.so datetime_functions.o

$ psql mydb

mydb=> create function currentdate() returns date
mydb-> as '/usr/local/pgsql/lib/datetime_function.so' language 'c' ;
CREATE

mydb=>select currentdate() ;
currentdate
--------
12-09-1997
(1 row)

mydb=>

5) 룰

룰은 트리거와 유사한 PostgreSQL 의 확장 SQL 부분이다.

6) 트리거

     create rule rule_name
         as on event
           to object [where clause]
         do [instead]
         [action | nothing | [actions...]]

mydb=> create table 표1 (i int4) ;
CREATE
mydb=> create table 표2 (i int4) ;
CREATE
mydb=> create rule 규칙1 as on insert to 표1 do [insert into 표2 values(new.i)] ;
CREATE
mydb=> insert into 표1 values (10) ;
INSERT 535999 1
mydb=> select *from 표1
i
--
10
(1 row)

mydb=> select *from 표2
i
--
10
(1 row)

mydb=>

PostgreSQL에서 트리거를 지원하기 전까지 룰 시스템이 대신해왔다. 기본적인 기능은 룰과 비슷하나 트리거는 좀 더 표준이다.

EventX 는 INSERT, DELETE, UPDATE 중 하나이다.

create trigger trigname {before | after}
    {Event1 [OR Event2 [OR Event3]]}
     on relname for each {row | statement}
     execute procedure funcname (arguments)

create trigger 구문은 새로운 트리거를 만들어서 현재의 데이터베이스에 집어 넣는다. 트리거는 relname 객체상에서 EventX 사건이 발생한 전후에, 지정한 함수인 funcname 을 실행한다. 현재 PostgreSQL 에서 트리거는 statement 가 구현되어 있지 않다. 그리고 일반적인 SQL 에는 거의 사용하지 않고, 프로그래밍 인터페이스로 구현되어 있다. 자세한 내용은 PostgreSQL 소스 디렉토리의 doc에 보면 trigger 메뉴얼이 있으니 참고하기 바란다.

7) 시퀀스

시퀀스는 말그대로 일련번호 발생기이다. 데이터베이스의 특정 테이블의 로우에, 효율적인 관리를 위해 일반적으로 일련번호를 붙일 필요성이 있다.

seqname 은 일련번호 생성기의 이름이다. increment는 한번에 증가시킬 값으로, 음수와 양수가 될 수 있으며, 기본 값은 1이다.

create sequence seqname
     [increment incby_value]
     [minvalue min_value]
     [maxvalue max_value]
     [start start_value]
     [cache cache_value]
     [cycle]

minvalue 는 일련번호의 최소값으로, 증가시에는 1, 감소시에는 -2147483647이다. maxvalue는 일련번호의 최대값으로, 증가시에는 2147483647, 감소시에는 -1이다. start 값은 일련번호의 첫 번째 값으로, 증가시에는 minvalue 가 사용되고, 감소시에는 maxvalue 가 사용된다. cashe 는 캐쉬에 관련되는 것으로 일반적으로 사용할 필요는 없다. cycle 는 일련번호를 다시 처음부터 매기는 것을 말한다. 매 단계의 일련번호의 값을 얻는 함수는 nextval 이다. 일반적으로 사용되는 일련번호는 1부터 시작하여 1씩 증가시키는 경우가 많다. 이 경우에는 다음과 같이 간단하게 사용한다.

100부터 100씩 증가하는 일련번호를 생성하려면 다음과 같이 하면 된다.

mydb=> create table t1(num int4, name text) ;
CREATE
mydb=> create sequence t1_seq ;
CREATE
mydb=> insert into t1 values (nextval ('t1_seq'), '한동훈') ;
INSERT 536037 1
mydb=> insert into t1 values (nextval ('t1_seq'), '이규성') ;
INSERT 536038 1
mydb=> insert into t1 values (nextval ('t1_seq'), '정용석') ;
INSERT 536039 1
mydb=> select *from t1

num	name
1	한동훈
2	이규성
3	정용석

(3 rows)
mydb=> select currval ('t1_seq')
currval
---
3
(1 row)
mydb=>

8) 트랜잭션

create sequence seq_100 increment 100 start 100 ;

SQL 에서 트랜잭션은 없어서는 안되는 중요한 개념이다. 보통 하나의 데이터베이스를 여러명이 동시에 사용하게 된다. 이 경우에 각각의 사용자가 데이터베이스에 가한 일정 작업의 구간을 절정하여, 데이터베이스에 가한 영향을 수용하거나 취소할 수 있도록 하는 기능을 제공하는 것이 트랜잭션의 중요기능이다.
가령, 어느 데이터베이스 사용자가 오늘은 도서정리를 한다고 가정해 보자. 이 작업이 기존의 작업과 구별되고, 동일한 연장선상에 있지 않다면, 트랜잭션의 시작점(begin)을 설정하고 일정시간 작업을 계속해 나가게 된다. 그러다 일정작업단위가 끝나면, 그간에 데이터베이스에 가한 변화를 승인하려면 commit (또는 end)를, 계산이 잘못 되어서 취소하고 싶으면, rollback (또는 abort)을 사용할 것이다. PostgreSQL 에서 제공하는 트랜잭션 처리기능은 그리 세부적이지 못하다. 트랜잭션 구간 작업처리를 정밀하게 제어할 수 있게 된다. PostgreSQL은 평면적, 1구간 트랜잭션의 승인은 end, 취소는 abort로 한다. commit은 end 와 같고, rollback은 abort와 같다.

간단한 예를 들어보자

9) 통지와 청취

mydb=> create table test2 (num int4) ;
CREATE
mydb=> insert into test2 values (1)
INSERT 536270 1
mydb=> insert into test2 values (2)
INSERT 5362701
mydb=> select *from test2 ;
num
----
1
2
(2 rows)

mydb=> begin ;
BEGIN
mydb=> insert into test2 values (3)
INSERT 5362702 1
mydb=> select *from test2 ;
num
----
1
2
3
(3 rows)

mydb=> abort ;
ABORT
mydb=> select *from test2 ;
num
----
1
2
(2 rows)

mydb=> begin ;
BEGIN
mydb=> delete from test2 where num = 2
DELETE 1
mydb=> end ;
END
mydb=> select *from test2 ;
num
----
1
(1 rows)

mydb=>

다른 세션에서,

mydb=> create table 테스트1 (i int4) ;
CREATE
mydb=> notify 테스트1 ;
NOTIFY

notify 는 동시성에 관련된 통보를 하고, listen 은 그것을 청취한다.

mydb=> listen 테스트1 ;
LISTEN
mydb=>
mydb=> select *from 테스트1 ;
i
-
(0 rows)

ASYNC NOTIFY of '테스트' from backend pid '14126' received
mydb=>

그냥 listen 만한 상태에서는 아무런 일이 일어나지 않는다.

식으로 listen 할 테이블(클라스) 이름을 지정하면, 이후에 다른 프로세스(같은 프로세스도 포함하여)가 해당 notify를 하게되면 그때서야 listen 프로세스는 어느 프로세스가 notify를 보냈다는 통보를 받는다.

listen class_name ;

그리 유용한 질의어는 아니지만, 같은 테이블에 동시적으로 접근하는 프로세스간에 약간의 정보(프로세스 번호)를 교류하기 위한 것이다. 즉, 누가 지금 어느 테이블을 건드리고 있다...는 정도이다. libpq 에도 이에 상응하는 기본 함수가 있긴 하지만, 거의 쓰이지 않는 듯 하다.

10) 클러스트링

PostgreSQL에서 클러스트링은 인덱스에 기초하여 데이터를 물리적으로 재정렬하는 것이다. 하나의 클래스인 classname 상의 인덱스인 indexname 에 기초하여 PostgreSQL 가 클러스트링 작업을 수행하도록 지시하는 명령이다. indexname 은 classname 상에 미리 정의되어 있어야 한다. 클래스의 크러스터는 파괴된다. 즉, 클러스트링은 정적(static)이며, 사용자가 원할 경우 언제든지 다시 클러스트링을 수행할 수 있다.

mydb=> create index 날씨인덱스 on 날씨(도시) ;
CREATE
mydb=> cluster 날씨인덱스 on 날씨 ;
CLUSTER
mydb=>

11) 권한

grant
데이터베이스를 사용하다보면 사용자에게 데이터베이스 접근권한을 넘겨주거나 제한하여야 할 필요가 있다. 이럴 때 사용하는 명령이 grant 이다. 말 그대로 승낙한다는 뜻이다.

privilege 는 ALL, SELECT, INSERT, UPDATE DELETE, RULE 중의 하나 이상이 될 수 있다.

grant <privilege[,privilege...]>
on <rel1>[,...<reln>]
to [public | group <group> | <username>]

기본적으로 하나의 테이블은 PostgreSQL 의 모든 사용자가 읽기(select)만 할 수 있도록 되어있다. 이것을 원치 않는다면 권한을 revoke 할 수 있다.

날씨 테이블에 대한 모든 권한을 공중에 허용한다는 것이다.

grant all on 날씨 to public ;

이럴경에 모든 PostgreSQL사용자는 해당 테이블을 읽고 쓸수 있게 된다.

Linuxer 사용자에게 나씨 테이블에 대한 입력, 갱신, 삭제 권한을 부여하는 것이다. PostgreSQL 의 카탈로그에 보면 pg_group 이라는 객체가 있다. PostgreSQL의 카탈로그는 다른 RDBMS에 비해 상당히 복잡하고, 저급적인 인터페이스를 가지고 있다고 생각한다. 이 pg_group 카탈로그와 grant를 잘 사용하면, 특정 프로젝트를 수행하고 있는 데이터베이스 사용자 그룹에게만 권한을 부여할 수도 있다. 참고로 pg_group 카탈로그는 다음의 컬럼으로 이루어져 있다.

mydb=> ＼d pg_group
Table = pg_group

Field	Type	length
groname	name	32
grosysid	int4	4
grolist	int4[]	var

해당 그룹이 users 라는 그룹이고, 유닉스 그룹 아이디(/etc/group 에 나타난 번호)가 100이고, 이들 그룹에 포함시키고자 하는 사용자의 UNIX아이디(/etc/passwd 에 나타난 번호)가 501, 510 이라면, 먼저 postgres 계정으로 데이터베이스에 접속하여, 이들에 대한 그룹설정을 해준다.

이제 501, 510 사용자가 users 라는 그룹명으로 pg_group 카달로그에 포함을 시켰다.

mydb=>insert into pg_group values ('users', 100, '{501,510}') ;
INSERT 536064 1

이제 특정 테이블에 대한 권한을 이들 그룹에게만 부여해보자.

mydb=>grant all on 날씨 to group users ;
CHANGE
mydb=>

revoke
revoke는 말 그대로 권한을 취소한다는 의미이다. 문법은 grant와 동일하고, to 와 from 만 다르다.

역시 privilege 는 ALL, SELECT, INSERT, UPDATE, DELETE, RULE 중 하나 이상이 될 수 있다.

revoke <privilege[,privilege...]>
on <rel1>[,...<reln>]
from [public | group <group> | <username>]

grant에서 부여한 권한을 차례로 취소해보자.

revoke all on 날씨 from public ;
revoke insert, update, delete on 날씨 from linuxer ;
revoke all on 날씨 from group users ;

12) 질의 분석

explain 은 질의의 효율성을 분석하는 데 도움을 주는 명령어이다. explain 뒤에 사용할 질의를 명시하면 질의를 실행하는 대신, 옵티마이저가 계획한 질의의 실행경로와 계획단계를 사용자에게 알려준다. 따라서, 대형화된 데이터베이스에서 질의의 최적화를 성립하는 데 도움을 주게 된다.

explain 에 verbose를 사용하면 더 자세한 질의 실행계획을 알 수 있다.

mydb=> ＼h explain ;
Command : explain
Description : explain the query execution plan
Syntax :
explain [verbose] <query>
mydb=> explain select *from 날씨 ;
NOTICE : QUERY PLAN :
Seq Scan on 날씨 (cost =1.36 size =11 width = 32)
EXPLAIN
mydb=> explain verbose select *from 날씨 ;
NOTICE : QUERY PLAN :
{SEQSCAN :cost 1.363 :size 11 :width 32 : state nil :qptargetlist
({TLE :resdom { RESDOM :resno 1 :restype 1043 :reslen -1 :resname
"도시" :reskey 0 :reskeyop 0 :resjunk 0} :expr {VAR :varno 1 :varattno 1
:vartype 1043 :varnoold 1 :varoattno 1}} {TLE :resdom {RESDOM
:resno 2 :restype 23 :resnel 4 :resname "최저온도" :reskey 0 :reskeyop 0
:resjunk 0} :expr {VAR :varno 1 :varattno 2 :vartype 23 :varnoold 1
:varoattno 2}} {TLE :resdom {RESDOM :resno 3 :restype 23 :resnel 4
:resname "최고온도"  :reskey 0 :reskeyop 0  :resjunk 0} :expr {VAR :varno 1 :varattno 3 :vartype 23 :varnoold 1 :varoattno 3}} {TLE :resdom
{RESDOM :resno 4  :restype 701 :resnel 8 :resname "강수량" :reskey 0
:reskeyop 0 :resjunk 0} :expr {VAR :varno 1 :varattno 4 :vartype 701
:varnoold 1 :varoattno 4}} {TLE :resdom {RESDOM :resno 5 :restype
1082 :resnel 4 :resname "날짜"  :reskey 0 :reskeyop 0  :resjunk 0} :expr
{VAR :varno 1 :varattno 5  :vartype 1082 :varnoold 1 :varoattno 5}})
:qpqual nil :lefttree nil :righttree nil :scanrelid 1}
Seq Scan on 날씨 (cost =1.36 size =11 width = 32)
EXPLAIN
mydb=>

13) 커서 사용하기

declare
declare는 커서를 정의한다. SQL 에서 커서는 일종의 파일 스트림과 같은 역할을 하는 것이다. 파일을 열어서 FILE * 스트림으로 연결하여 사용하듯이, SQL 에서는 데이터베이스의 데이터를 SELECT 로 회수하여 커서와 연결하여 사용한다.

selsct 구문은 앞전과 동일하다. SELECT 구문의 경과를 커서 cursorname 과 연결하여 정의하는 것이다.

declare cursorname [ binary ] cursor for select statement

여기에서 binary 에 주목할 필요가 있다. 커서는 일반적으로 아스키 형태로 데이터를 반환한다. 데이터는 자연스럽게 바이너리 형식으로 저장되기 때문에, 시스템은 이것을 아스키 형식으로 변환해야 한다. 아스키 형식은 크기면에서 바이너리 형식보다는 일반적으로 큰 경우가 많다. 데이터가 아스키 형식으로 변환되었을 때, 클라이언트 어플리케이션은 데이터를 어떤 목적으로 처리하기 위해 바이너리 형식으로 변환해야 할 필요가 있을 것이다. 바이너리 커서는 사용자에게 데이터를 바이너리 형식으로 전달한다. 따라서, 바이너리 커서는 변환에 소모되는 오버헤드가 없기 때문에 좀더 빠르다. 하지만, 아스키 형식은 아키텍쳐에 비교적 영향을 받지 않지만, 바이너리 형식은 머쉰에 따라 달라지기 쉽상이다. 따라서, 클라이언트 머쉰과 서버 머쉰이 서로 다른 바이너리 형식을 사용한다면, 원치 않는 결과가 나타날 수도 있다. 주 목적이 데이터를 아스키 형태로 보여주기 위한 것이라면 아스키 형태로 커서를 선언하는 것이 좋다.

fetch
fetch 는 커서에서 인스턴스를 적재하는 일을 한다. 파일 스트림(커서)에서 fgets 함수등으로 불러오는 일(fetch) 과 비슷하다고 볼 수 있다.

number 는 cursor_name 커서에서 불러올 인스턴스의 갯수이다.

fetch [(forward | backward)] [(number | all)] [in cursor_name]

해당 커서에 남아있는 인스턴스가 number 보다 적다면 남아있는 것만 적재된다. all 은 현재 커서에 남아있는 모든 인스턴스를 불러온다. forward 와 backward 는 커서에서 앞뒤로 움직이며 적재할 수 있도록 한다. 즉, 파일 스트림에서 정방향(forward)으로, 또는 거꾸로(backward) 돌아가는 것과 비슷하다고 보면 된다. 기본은 물론 forward 이다. fetch 명령어는 트랜잭션 블록 안에서 사용되어야 한다. 그리고, 커서를 사용하고 난 다음에는 커서 cursor_name 과 관련되는 자원을 해제하기 위해 close 해주어야 한다. 트랜잭션 내부에서 declare 와 fetch를 사용하는 순서는 다음과 같다.

알아보기 쉽게 간단한 테이블을 만들어서 커서를 선언해 보자

첫 단계에서 5개를 fetch 하였을 경우에는, 커서 내의 위치가 0 에서 시작하여 1, 2, 3, 4, 5 의 5개를 불러와서 5에 위치하게 되고, 거꾸로 3개를 fetch 하였을 경우에는 4, 3, 2 의 3 개를 불러와서 2까지 가게 되며, 여기서 정방향으로 4개를 fetch 하였을 경우에는 3,4 , 5, 6 을 불러왔음을 알 수 있다. fetch all 은 나머지를 모두 보여준다.

BEGIN -> DECLARE CURSOR -> FETCH INSTANCE IN CURSOR ->
CLOSE CURSOR -> END

mydb=> create table test3 (num int4) ;
CREATE
mydb=> copy test3 from stdin ;
Enter info followed by a newline
End with a backslash and a period on a line by itself.
>>1
>>2
>>3
>>4
>>5
>>6
>>7
>>8
>>9
>>10
>>＼.
mydb=> begin ;
BEGIN
mydb=> declare mycursor cursor for select *from test3 ;
SELECT
mydb=> fetch 5 in mycursor ;
num
---
1
2
3
4
5
(5 rows)

mydb=> fetch backward 3 in mycursor ;
num
---
4
3
2
(3 rows)

mydb=> fetch forward 4 in mycursor ;
num
---
3
4
5
6
(4 rows)

mydb=> fetch all in mycursor ;
num
---
7
8
9
10
(4 rows)

mydb=>close mycursor ;
CLOSE
mydb=>end ;
END
mydb=>

14) 제거된 데이터의 물리적인 삭제

vacuum 은 데이터베이스를 정리하는 것이라 보면 된다. 데이터베이스의 로우를 제거하였다 하더라도 아직 데이터베이스에 남아있다. 무슨 이야긴가 하면,

즉, 테이블의 데이터를 지웠다고 하더라도, 데이터 자체가 물리적으로 지워진 것이 아니고 PostgreSQL에서 지워진 데이터라는 표시만 하고, 데이터는 그대로 저장되어 있다는 것이다.

mydb=> select *from 표1 ;
i
-
10
(1 row)

mydb=>delete from 표1 ;
DELETE 1
mydb=> select *from 표1 ;
i
-
(0 rows)

mydb=> select *from 표1['epoch', 'now'] ;
i
-
10
(1 row)

mydb=> vacuum ;
VACUUM
mydb=> select *from 표1['epoch', 'now'] ;
i
-
(0 rows)

mydb=>

그래서 데이터 회수시에 시간지정을 해주면 지워진 데이터라 할지라도 검색할 수 있는 것이다. vacuum 은 제거된 데이터를 물리적으로 제거해 버린다. vacuum을 실행하기 전에 지워진 데이터들이 별 필요없는지를 다시 한번 확인해 보는 것이 좋다. 한번씩 vacuum을 실행하는 것은 데이터베이스 처리 속도를 향상시키는 튜닝의 한가지 방법이다.

▲ top