1. 들어가면서 지난 시간에는 PostgreSQL의 설치와 간단한 테스트에 이어, PostgreSQL의 사용을 도와주는 외부 명령어를 살펴보았다. 이번 시간에는 PostgreSQL에서 제공하는 데이터 타입과 데이터베이스 질의어인 SQL에 대해서 살펴보겠다. PostgreSQL에서의 데이터 타입은 상당히 많고 폭이 넓다. 생각도 하지 못한 듯한 데이터 타입들이 있으며, 공학용이나 복잡한 기하학 용도로 사용될 법한 데이터 타입들도 준비외어 있다. 물론, 이러한 데이터 타입조차도 사용자 마음대로 정의하여 사용할 수 있다는 장점이 있다.
그리고 SQL 질의어도 마찬가지로 확장성이 뛰어나다. 사실, PostgreSQL 6.2에서 새롭게 지원하는 필드 제한, 보충 기능들과 트리거 기능은 이전 버전에서도 PostgreSQL의 SQL확장을 사용하여 해결할 수 있었다. 이제 본격적으로 PostgreSQL의 기능들을 마음껏 누려 보도록 하자.
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 의 비교 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축 값을 나타내는 부동 소수이다.
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$ |
그 외의 함수들 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 로부터 상속을 받은 테이블(클래스)가 있다면 경고가 떨어지고 제거되지 않는다. 이때에는 상속받은 테이블(클래스)부터 제거해야 한다.
데이터 검색
데이터 검색(또는 열, 컬럼 회수)은 유명한 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 |
|
작성일 : 11-06-28 11:56
조회 : 2,695
