상세 컨텐츠

본문 제목

postgreSQL 간단 사용

개발

by 동동주1123 2013. 6. 18. 11:05

본문

[출처] postgreSQL 간단 사용|작성자 마이키군  


원문:

http://database.sarang.net/database/postgres/manual/manual-7.3/tutorial-preface.html

이 글은 ‘데이터베이스를 사랑하는 사람들의 모임’의 김상기(ioseph) 님이 번역하신 글입니다.

많은 도움이 되시길 바라며, 이 번역물의 저작권은 PostgreSQL 저작권과 동일합니다.

자신의 사이트에 옮겨두실 요량이라면, 출처만 분명히 해두시면 됩니다

 

여러 파일을 한번에 읽기 편하도록 모은 것이며, 약간의 오타 등에 대한 것은 제가 약간 수정했습니다. ^^;;

정연우(jung78yw) : jung78yw@korea.com

 




서문

1. 이 책에서 다루는 내용

먼저 PostgreSQL과 PostgreSQL 자습서를 통해서 이렇게 만나게 되어서 반갑습니다. 여기서는 PostgreSQL이라는 관계형 데이터베이스 시스템(RDBMS)에 대한 간단한 소개와 SQL이라는 데이터베이스 질의 언어에 대해서 몇 개의 장에 걸쳐 다룰 것입니다. 이 글은 적어도 컴퓨터를 켜고 끌 수 있을 정도의 사용자들을 대상으로 작성되었습니다. 즉, 특정 Unix 운영체제에 대해서 잘 알고 있다거나, 프로그래밍 경험이 있는 독자들을 대상으로 작성되지 않은 아주 가볍게 읽을 수 있는 수준으로 작성되었습니다.이 문서 작성의 주안점은 사용자가 PostgreSQL의 핵심적인 기능들을 쉽게 체험할 수 있도록 하는 것입니다. 완벽한 설명서는 아니지만, 각 제목들만 대강 훑어볼 만한 문서도 아닙니다.

 

이 자습서를 읽은 뒤에 PostgreSQL 사용을 위한 전반적인 응용 프로그램 사용 방법과 SQL 구문에 대해서 보다 자세히 알려면 PostgreSQL7.3.4 사용자 안내서를 읽어보십시오. 한편 PostgreSQL 서버를 확장하거나 서버를 이용하는 응용 프로그램을 개발하고자 하는 개발자들은 PostgreSQL7.3.4 개발자 안내서를 읽어보십시오. 한편 PostgreSQL 서버 설치 운영에 관계된 자세한 내용은 PostgreSQL7.3.4 관리자 안내서에서 다루고 있습니다.

 

제1장. 시작하기

1.1. 설치

PostgreSQL을 사용하려면 당연히 설치부터 해야겠지요. 그런데, 대부분의 시스템(컴퓨터)에는 이미 PostgreSQL 패키지가 설치되어있는 경우가 많습니다. OS의 배포판에 기본으로 내장되고, 특별히 제외시키지 않는다면, 기본적으로 설치되어있기 때문이지요. 이런 경우라면, OS 배포판 설치 문서나 안내 문서를 참조하시면, PostgreSQL 패키지가 어디에 설치되었고, 어떻게 사용하는지에 대해서 알 수 있을 것입니다.

 

만일 사용하는 시스템에 PostgreSQL 패키지가 설치되어 있지 않거나, PostgreSQL 설치에 대해서 공부를 해보려고 한다면, 그리 어렵지 않은 일이기에 한번 정도 시도해 보는 것도 좋은 연습이 될 것입니다. PostgreSQL 패키지는 굳이 시스템 관리자의 권한(root ID로 로그인 하는 것)으로 작업을 해야 할 것이 하나도 없는 그저 일반 사용자도 자기 홈 디렉토리에 충분히 설치하고 사용할 수 있는 패키지입니다.

 

PostgreSQL 패키지를 직접 설치하고자 한다면, 먼저 PostgreSQL 관리자 안내서 에 있는 설치 관련 부분은 차근히 꼼꼼히 잘 읽고 설치를 하십시오. 또한 서버 셋팅 관련된 적절한 환경 변수들에 대해서 설명한 다음 장도 같이 읽으면 도움이 될 것입니다.

 

시스템 관리자가 PostgreSQL 사용에 대한 기본적인 작업들을 해 두지 않았다면, 사용자가 직접 몇 가지 작업을 해야 할 것도 있습니다. 가령, PostgreSQL 서버가 리모트 호스트에서 가동 중이라면, 그 리모트 호스트명을 지정한 PGHOST 환경 변수를 지정할 필요도 있고, 또한 PGPORT 환경변수를 지정해야 할 필요도 있을 것입니다. 만일 이런 작업 없이 그냥 PostgreSQL 프로그램을 실행 시켰을 때, 데이터베이스에 접근을 못한다는 메시지를 접하게 될 지도 모릅니다. 이때는 먼저 시스템 관리자에게 문의를 해 보거나, 스스로 설치한 것을 사용하고 있는 중이라면, 설치 및 설정 관련 문서를 좀 더 꼼꼼히 읽어 보시길 바랍니다. 그런데, 지금 다루고 있는 내용 자체를 이해하지 못하고 있다면, 다음 섹션을 읽으면서 RDBMS의 서버, 클라이언트, 데이터베이스 같은 구조적인 개념들을 먼저 숙지 하시길 바랍니다.

1.2. 기본 구조

이 섹션에서는 PostgreSQL의 시스템 구조에 대한 기초적인 이야기를 다루고 있습니다. PostgreSQL의 작동 원리를 기초적이나마 이해하고 있는 것은 이 장을 읽어가는데 도움을 줄 것입니다.

 

데이터베이스 전문 용어로, PostgreSQL은 클라이언트/서버 모델을 사용하고 있습니다. 하나의 PostgreSQL 세션(작업)은 다음과 같은 프로세스들(프로그램들)의 상호 작동으로 구성됩니다: 

·         서버 프로세스: 이것은 데이터베이스 파일을 관리하고, 클라이언트 응용 프로그램들이 서버에 연결을 요청할 때, 그 요청들을 처리(수락하거나 거부하는 일)하고, 클라이언트들이 데이터베이스를 사용할 수 있도록 기반 작업들을 준비합니다.이 프로세스의 이름은 postmaster 입니다.

·         클라이언트 프로세스: 이것은 데이터베이스를 사용하려는 사용자 측 응용 프로그램을 말합니다. 클라이언트 응용 프로그램은 자연적으로 매우 다양한 형태를 띠고 있습니다. 어떤 것은 완벽한 텍스트 기반의 프로그램이기도 하고, 어떤 것은 그래픽 환경이기도 하고, 어떤 것은 웹 기반이기도 하지요. 몇 클라이언트 프로그램들은 이미 PostgreSQL 패키지 안에 포함되어서 배포되기도 합니다. 이것들은 대부분 사용자들이 직접 개발한 것들이지요.

클라이언트/서버 환경의 프로그램들은 대부분 그렇듯이, PostgreSQL에서도 클라이언트와 서버가 각기 서로 다른 호스트일 수 있습니다. 이런 경우에는 서로간의 통신이 TCP/IP 네트워크 기반 아래서 이루어 집니다. 이 부분은 아주 중요한 부분을 시사하고 있습니다. 클라이언트와 서버가 서로 틀릴 경우에는 클라이언트에서 접근하고자 하는 데이터베이스 파일에 대해서 직접적으로 접근할 수 없음을 의미합니다. 즉, 클라이언트에서 접근 할 수 있는 파일은 그 클라이언트가 실행되고 있는 호스트의 파일이지 서버가 가동 중인 호스트의 파일이 아님을 인식하고 있어야 합니다.

 

PostgreSQL 서버는 사용자의 다중 접속을 위해서 각 접속에 대해서 새로운 프로세스를 할당합니다(전문적인 용어로 fork를 이용한 자식 프로세스 생성이라고도 하지요). 이 방법은 클라이언트와 새로 만들어진 서버 프로세스간의 작업이 있을 때, 서버의 부모 프로세스 격인 postmaster 프로세스의 간섭 없이 작업이 이루어짐을 의미합니다. 종합하면, postmaster 프로세스는 서버 호스트에서 항상 실행되고 있으면서, 클라이언트의 접속 요청을 처리해서 새로운 자식 서버 프로세스 (일반적으로 postgres 라고 합니다)를 만드는 역할을 합니다. 물론 이 모든 작업들은 사용자가 관심을 가지고 살펴보지 않는 이상 서버 차원에서 자동으로 이루어지는 부분입니다. 단지 여기서는 기초적인 개념 설명을 위해서 언급했습니다.

 

1.3. 데이터베이스 만들기

데이터베이스 서버를 사용할 수 있는지를 확인하기 위한 테스트로 먼저 하나의 데이터베이스를 만들어 보겠습니다.  각각의 사용자 단위나, 프로젝트 단위로 데이터베이스를 분리해서 작업하는 것이 일반적이기에, 하나의 PostgreSQL 서버는 많은 데이터베이스를 관리할 수 있습니다.

 

어쩌면, 이미 시스템 관리자가 일반 사용자들을 위해서 그들이 사용할 수 있는 데이터베이스를 미리 만들어 두었을 수도 있습니다. 이런 경우라면, 시스템 관리자가 일반 사용자들에게 그 사실을 알려서 사용할 수 있는 데이터베이스 이름을 알려주어야겠지요. 이렇게 이미 만들어진 데이터베이스가 있고, 그것의 이름이 어떤 것인지 알고 있다면, 이 섹션을 읽지 않고 넘어가셔도 좋습니다.

 

예를 들어 mydb라는 이름의 새로운 데이터베이스를 만들려면, 다음과 같은 명령을 사용합니다. 

$ createdb mydb

정상적인 결과라면 다음과 같이 나타납니다. 

CREATE DATABASE

직접 해본 결과가 위 메시지와 같다면, 그냥 이 섹션을 넘어가세요.  

createdb: command not found

이런 메시지가 나타났다면, PostgreSQL 패키지가 정성적으로 설치 되지 않은 경우이거나 사용자의 실행 경로 환경 변수(PATH)에 이 PostgreSQL 설치 경로가 포함되어있지 않은 경우입니다. 이것을 알기 위해서는 일단 다음과 같이 절대경로로 다시 한번 호출해 봅니다.

$ /usr/local/pgsql/bin/createdb mydb

위 명령에서의 절대 경로는 각 시스템마다 틀릴 수 있습니다. 정확히 알기 위해서는 시스템 관리자에게 문의해 보든지, OS 배포판 설명서를 참조하세요. 

 

이 명령의 또 다른 결과로 다음과 같은 메시지가 보일 수 있습니다.

psql: could not connect to server: Connection refused
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
createdb: database creation failed

이 경우는 현재 시스템에는 PostgreSQL 서버가 가동 중이지 않다는 것을 의미합니다. 다시 한번 설치, 서버 실행 관련 문서를 살펴보거나, 시스템 관리자에게 자문을 구해보십시오.

 

또 다른 오류 메시지로,

ERROR:  CREATE DATABASE: permission denied
createdb: database creation failed

이런 메시지를 만날 수 있는데, 이 경우는 현재 사용자가 데이터베이스를 만들 권한이 없는 경우입니다. PostgreSQL에서는 새로운 데이터베이스를 만들거나, 새로운 사용자를 만들 사용자를 데이터베이스 관리자라고 해서 그 데이터베이스 관리자에게는 다른 많은 권한들이 부여됩니다. 만일 시스템 관리자가 설치한 PostgreSQL 서버를 이용한다면, 시스템 관리자에게 이 부분에 대한 자문을 구해보시고, 직접 설치한 경우라면, 위 명령을 내리는 사용자와 PostgreSQL 서버를 설치하고, 가동시킨 사용자가 틀려서 발생한 경우입니다. 이런 경우는 다시 PostgreSQL 서버를 설치하고 가동시킨 사용자로 전환하셔서 다시 시도해보세요. - 일반적으로 그 사용자 이름이 postgres 입니다 - 이 자습서에서 계속 언급하는 사용자는 데이터베이스 관리자 즉, PostgreSQL 서버를 설치하고 가동시킨 사용자를 의미합니다. 

 

물론 데이터베이스 이름은 아무거나 다른 이름을 사용해도 괜찮습니다. PostgreSQL에서는 만들 수 있는 데이터베이스 개수 제한이 없습니다. 하지만, 데이터베이스 이름은 첫 글자가 숫자나 기호가 올 수 없고, 63글자까지의 어떠한 이름도 괜찮습니다. 데이터베이스 이름으로 가장 편하게 사용할 수 있는 이름은 그 데이터베이스를 만드는 사용자의 이름과 같은 것입니다. 왜냐하면 많은 프로그램들이 이 사용자와 같은 이름의 데이터베이스 이름을 그 사용자의 기본 데이터베이스로 가정하기 때문입니다. 이런 식으로 사용자의 이름과 같은 데이터베이스를 만들려면, 아래와 같은 명령을 이용하면 됩니다. 간편하지요.

$ createdb

만든 데이터베이스를 더 이상 사용할 일이 없으면, 데이터베이스를 삭제 할 수 있습니다. 예를 들어 mydb 데이터베이스를 만든 사람이 그 데이터베이스를 삭제하려면, 다음과 같은 명령을 사용합니다.

$ dropdb mydb

(dropdb 명령은 그 인자가 없어도 기본으로 삭제할 데이터베이스를 사용자 이름과 같은 데이터베이스를 삭제하지는 않습니다.반드시 삭제할 데이터베이스 이름을 지정해 주어야 합니다) 이 작업은 해당 데이터베이스와 관련된 모든 파일들을 물리적으로 완전히 삭제하는 것입니다. 그러기에 실행 취소를 할 수 없습니다. 이 작업을 할 때는 반드시 신중을 기하십시오.

주석

사용자 이름에 대한 부가 설명을 하면, PostgreSQL 서버의 사용자와 시스템 사용자는 엄격히 틀립니다. 구체적으로 설명하면, 데이터베이스를 만들거나 사용할 수 있는 사용자가 꼭 시스템 사용자로 존재해야 하는 것도 아니며, 특정 시스템 사용자가 반드시 PostgreSQL 서버의 사용자로 존재해야 할 이유도 없습니다. 단지 PostgreSQL 프로그램들은 해당 데이터베이스를 사용하기 위한 사용자를 지정할 때, 기본적으로 현재 사용 중인 시스템 사용자의 이름을 이용합니다. 만일 사용자가 없다거나, 해당 사용자의 권한이 부당한 오류 메시지를 만난다면, 그것은 데이터베이스의 사용자에 대한 이야기입니다. 이 사용자를 바꾸려면,  -U 옵션으로 지정할 수 있습니다. 

 

1.4. 데이터베이스 사용하기

하나의 데이터베이스를 만들었다면, 이제 그 데이터베이스를 다음과 같은 방법으로 사용할 수 있습니다:

  • psql 이라는 PostgreSQL 대화형 터미널 프로그램을 실행하기. 이것은 대화형으로 SQL 명령을 편집/실행 할 수 있습니다.
  • PgAccess와 같은 그래픽 툴을 이용하거나 ODBC를 이용한 다른 툴들로 데이터베이스를 만들거나 사용할 수 있습니다.이 부분에 대해서는 이 자습서에서 다루지 않겠습니다.
  • 직접 응용 프로그램을 만들어서 사용하기. - PostgreSQL에서는 이런 응용 프로그램을 만들 수 있는 다양한 프로그래밍 언어를 지원하고 있습니다. 이 부분에 대한 자세한 내용은 PostgreSQL 7.3.4 프로그래머 안내서를 참조하십시오.

여기서는 psql 프로그램을 이용해서 데이터베이스를 다루는 방법에 대해서 언급하겠습니다. 먼저 앞 섹션에서 이야기한 그mydb 데이터베이스를 성공적으로 만들었다면, 다음과 같이 입력합니다.

$ psql mydb

데이터베이스 이름이 빠지면 기본적으로 현재 시스템 사용자 이름과 동일한 이름의 데이터베이스에 접속을 시도합니다. 이 부분에 대해서는 앞 섹션에서 잠깐 언급했었죠.

 

위 명령이 정상적으로 실행되면 다음과 같은 메시지를 볼 수 있습니다:

Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

 

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help on internal slash commands

       \g or terminate with semicolon to execute query

       \q to quit

 

mydb=>

한편 맨 마지막 줄이 다음과 같을 수도 있습니다. 

mydb=#

psql 프롬프트의 모습이 # 이면, 현재 데이터베이스 사용자가 데이터베이스 관리자임을 나타냅니다. 데이터베이스 관리자가 무엇이며 어떤 일을 할 수 있는지는 이 문서 안에서 별로 중요한 부분이 아니기에 언급하지 않겠습니다.

 

만일 이 psql 프로그램을 시작하는데 문제가 발생한다면, 이전 섹션을 다시 읽어보십시오. psql 프로그램의 실행과 createdb 프로그램의 실행 요건이 비슷합니다. 오류에 대한 대처 방안도 비슷하죠.

 

psql 프로그램을 실행했을 때 맨 마지막 줄에 나오는 프롬프트는 이제부터 SQL 쿼리를 사용할 수 있음을 의미합니다. 이제 다음과 같은 명령들을 입력해 봅시다:

mydb=> SELECT version();

                            version

----------------------------------------------------------------

 PostgreSQL 7.3devel on i586-pc-linux-gnu, compiled by GCC 2.96

(1 row)

 

mydb=> SELECT current date;

    date

------------

 2002-08-31

(1 row)

 

mydb=> SELECT 2 + 2;

 ?column?

----------

        4

(1 row)

psql 프로그램에서는 SQL 명령 말고도 내부적으로 사용할 수 있는 몇 가지 명령들이 있습니다 - 이 명령들을 psql 내장 명령이라고 합니다. 이 명령들은 역슬래쉬("\") 문자로 시작하면, 이미 psql 시작 메시지에서 몇 가지를 소개하고 있지요. 예를 들어, PostgreSQL SQL 구문을 살펴보려면 다음과 같이 입력합니다:  

mydb=> \h

psql을 마치려면, 

mydb=> \q

이 명령이 실행되면, psql 프로그램은 종료되고 다시 시스템 쉘 명령 프롬프트가 나타납니다. (psql 내장 명령들을 모두 살펴보려면, \? 명령을 이용하세요) 한편, 이 명령들의 완전한 설명과 사용 방법을 알고 싶으면, PostgreSQL 7.3.4 참고 설명서를 참조하십시오. 또 한편으로 PostgreSQL 패키지가 정상적으로 설치되어있다면, 쉘 프롬프트에서 man psql  이렇게 입력하면 psql 프로그램에 대한 사용법을 보실 수 있습니다. 이 자습서에서는 이런 것이 있다는 소개 정도로 마칩니다.

 

제2장. SQL 언어

차례 

2.1. 소개

2.2. 개념

2.3. 새 테이블 만들기

2.4. 자료 입력

2.5. 자료 조회

2.6. 테이블 JOIN

2.7. 집계 함수

2.8. 자료 갱신

2.9. 자료 삭제

2.1. 소개

이 장에서는 데이터베이스를 사용할 때 사용하는 SQL 언어에 대해서 개괄하고 있습니다. 이 자습서로는 SQL 언어에 대한 모든 것을 설명하기에는 그 지면이 부족할 뿐 아니라, 처음 접하는 사람들에게 오히려 복잡함만 초래하기에 여기서는 그 대략적인 사용법에 대해서만 언급합니다. PostgreSQL 문서 안에 있는 새 SQL 이해 부분과 표준 SQL 안내 부분에서 다양한 책들을 소개하고 있습니다. 관심 있으신 분은 읽어 보시길 바랍니다. 물론 PostgreSQL에서 사용하고 있는 SQL 언어는 고유 확장된 부분이 있음을 감안하셔야 합니다.

 

이제 실례를 들어 작업하는 것을 살펴보겠습니다. 먼저 앞 장에서 다루었던 mydb 이름의 데이터베이스가 만들어졌다고 가정하고, psql 프로그램을 실행시킬 수 있다는 가정 아래 이야기를 진행하겠습니다.

 

이 자습서에서 사용되는 예제 자료는 PostgreSQL 배포 소스의  src/tutorial/ 디렉토리 안에 있습니다. 이 예제 자료를 사용하는 방법에 대해서는 그 디렉토리 안에 있는 README 파일을 읽어보십시오. 일단 시작부터 하려면, 다음과 같이 진행합니다:

$ cd ..../src/tutorial

$ psql -s mydb

...

 

mydb=> \i basics.sql

 

\i  명령은 지정한 파일의 내용을 실행하는 psql 내장 명령어입니다. - 물론 basics.sql 파일 안에는 PostgreSQL 서버에서 실행될 SQL 명령들이 있겠지요. psql 프로그램을 실행할 때, -s 옵션을 사용하면, 이 SQL 명령들을 하나씩 확인하면서 실행할 수 있습니다. basics.sql 파일 안에는 앞으로 이 자습서에서 사용될 각종 테이블들과 그 자료들을 만드는 명령들이 들어있습니다.

 

2.2. 개념

PostgreSQL은 관계형 데이터베이스 관리 시스템(RDBMS, relational database management system)입니다. 이 말은 관계들로 구성된 자료를 관리하기 위한 시스템이라고 풀이하죠. 이 관계라는 용어는 원래 수학 용어였는데, 이것이 RDBMS에서는 테이블 사이의 수학적 관계를 설명하는데 이용되었고, 결국 관계형 데이터베이스라는 용어로 사용되었습니다(이 관계는 수학의 집합이론의 한 부분입니다). 자료를 테이블로 저장하는 방식은 오늘날 너무도 당연한 것으로 인식되고 있습니다. 하지만 이것은 단지,데이터베이스를 구현하는 여러 가지 방법 가운데 하나일 뿐입니다. 유닉스 계열 운영 체제에서 사용되는 파일과 디렉토리 개념은 구조적 데이터베이스를 구현하는 한 예입니다. 또한 최근에는 객체지향 개념을 이용해서 테이터베이스를 구현합니다.

 

각각의 테이블은 row 라고 불리는 것들의 집합입니다. 테이블을 구성하는 그 각각의 row는 특정 자료 형태로 표현되는 각각의column 들의 집합입니다. 이 column들의 순서가 각각의 row에 일정하게 정해져 있지만, SQL에서는 테이블의 column을 다룰 때 그 순서가 뒤섞여도 전혀 상관 없다는 사실을 기억해 두어야 합니다. 이 점이 관계형 데이터베이스 개념 가운데 꽤 중요한 개념입니다.

 

여러 개의 테이블들을 하나의 데이터베이스 안에서 관리하고, 그런 데이터베이스들을 관리하는 것이 바로 PostgreSQL 서버입니다.

 

2.3. 새 테이블 만들기

새 테이블은 다음과 같이 테이블의 이름과 그 테이블을 구성하는 각 칼럼과 그 칼럼의 자료형을 지정해서 만듭니다:

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

psql 안에서는 하나의 SQL 명령이 여러 줄로 나뉘어져 있어도 되고, 한 줄로 모두 표현 되어도 상관 없습니다. 하지만, 하나의 명령은 반드시 세미콜론(;)으로 끝나야 합니다.

 

SQL 구문을 작성할 때 공백문자(스페이스, 탭, 줄바꿈문자)들이 자유롭게 사용할 수 있습니다. 즉, 굳이 꼭 위와 같이 그대로 입력하지 않아도 됩니다. 한 줄로 길게 늘여 써도 상관 없습니다. 단지 주의할 것은 '--' 문자는 주석을 표현하는 것인데, 이것이 있으면, 그 다음부터 줄 끝까지 무시됩니다. SQL 문법에서는 예약어(keyword, 명령이름, 구문 중간 중간에 나오는 on, default, not null 같은 것들)와 식별자(identifier, 테이블 이름, 칼럼 이름  같은 것들)의 대소문자를 구별하지 않습니다. 단지, 식별자가 큰따옴표(")로 둘러 쌓여지면, 그 식별자는 입력한 그대로의 대소문자를 유지합니다.

 

varchar(80) 이것은 최대길이가 80글자인(주, byte가 아니라, '글자'입니다) 문자열을 저장할 수 있음을 나타내고, int 이것은 일반적인 정수형 숫자, real 이것은 소수점을 사용하는 숫자, date 이것은 말 그대로 날짜형을 나타냅니다. (위 명령에서처럼 칼럼 이름이 자료형 이름과 같을 수도 있습니다. 이런 방식이 편하기도 하겠지만, 헷갈리기도 할 것입니다. 선택은 테이블 만드는 사람의 몫입니다.)

 

PostgreSQL에서 사용할 수 있는 자료형은 int, smallint, real, double precision, char(N), varchar(N), date, time,timestamp, interval 그리고, 일반적인 기타 자료형들과 지리 정보를 담기 위해서 사용되는 다양한 자료형들입니다. 또한PostgreSQL에서는 사용자 정의 자료형을 만들 수 있습니다. 따라서 표준 SQL과의 호환성을 유지하기 위해서 특별히 문법적으로 예약어 기능을 해야 하는 것들을 제외하고는 PostgreSQL에서의 자료형 이름은 문법적인 예약어가 아닙니다. (주, 그래서, create table weather (...., data data); 같은 구문이 가능합니다)

 

다음 예제는 도시 이름과, 그 도시의 위치를 나타내는 지리정보를 담기 위한 테이블을 만드는 것입니다: 

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

point 자료형은 PostgreSQL 고유 자료형입니다. (주, PostgreSQL 프로젝트의 시작이 테이블 상속과 지리정보를 담기 위한 것이었거든요)

 

끝으로, 더 이상 사용하지 않는 테이블을 삭제하거나, 똑 같은 이름의 테이블을 다른 구조로 다시 만들려면, 다음과 같은 명령을 사용합니다:

DROP TABLE tablename;

 

2.4. 자료 입력

특정 테이블에 자료를 입력할 때는 다음과 같이 INSERT 구문이 사용됩니다:

INSERT INTO weather VALUES ('<st1:city w:st="on">San Francisco</st1:city>', 46, 50, 0.25, '1994-11-27');

각 칼럼의 실재 자료들은 그 테이블을 만들 때 지정한 자료형과 완벽하게 일치해야 한다는 것을 명심해야 합니다. 특히 숫자가 아닌 자료일 경우는 위 예제처럼 작은 따옴표(')로 둘러싸야 합니다. 날짜형(date) 자료를 입력해야 할 경우는 그 값이 정확하게 날짜표현이어야합니다. (주, PostgreSQL에서는 날짜형처리가 비교적 엄격한 편입니다. 날짜가 아닌 표현에 대해서는 그 입력 자체가 허용되지 않습니다. 예: 0년 0월 0일, 2003년 2월 29일 같은 것). 날짜형의 입력에 대한 자세한 이야기는 이 자습서의 범위를 벗어나기에 여기서는 이쯤으로 생략합니다.

 

point 자료형은 다음과 같이 하나의 쌍으로 입력되어야 합니다:

INSERT INTO cities  VALUES ('<st1:city w:st="on">San Francisco</st1:city>', '(-194.0, 53.0)');

한편, 각 칼럼의 입력 순서를 사용자가 다음과 같이 칼럼명을 미리 원하는 순서대로 지정하고 그 순서에 맞추어 입력할 수도 있습니다:

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('<st1:city w:st="on">San Francisco</st1:city>', 43, 57, 0.0, '1994-11-29');

이때는 굳이 테이블을 만들 때의 그 칼럼 순서와 같아야 할 이유도 없으며, 특정 칼럼을 생략하고, 입력할 수도 있습니다.

INSERT INTO weather (date, city, temp_hi, temp_lo)
    VALUES ('1994-11-29', '<st1:city w:st="on">Hayward</st1:city>', 54, 37);

많은 개발자들이 처음에 언급한 전체 칼럼을 순서대로 입력하는 방식보다 지금의 칼럼 이름을 같이 지정해서 원하는 값만 입력하는 방식을 선호합니다.

 

이제 윗 방식으로 다음 섹션에서 사용할 데이터를 몇 개 입력해 보십시오.

 

데이터를 입력하는 또 다른 하나의 방법으로  COPY 명령을 사용하는 방법이 있는데, 이것은 대용량의 데이터를 빠르게 입력해야 할 때 사용됩니다. 이 명령은 데이터베이스 백업/복구를 위해서 PostgreSQL 전용으로 특별히 만들어진 명령입니다. 그래서, 자료를 한꺼번에 입력할 때 아주 유용하게 사용됩니다. 사용법은 다음과 같습니다:

COPY weather FROM '/home/user/weather.txt';

이때 지정한 파일은 PostgreSQL 서버가 가동 중인 호스트에 있어야 하며, 이 파일의 내용은 한 줄이 테이블의 한 로우(Row)가 되면 각 칼럼의 구분은 탭문자입니다. (주, 물론 이런 제약조건은 단지 초기값이 그런 것 뿐입니다.)  COPY 명령에 대한 보다 자세한 내용은 PostgreSQL 7.3.4 참조 설명서를 참조하십시오.

 

2.5. 자료 조회

입력한 자료를 찾아보려면, 테이블을 질의해보아야 하는데, 이것을 데이터베이스 용어로 쿼리라고 합니다. (주, 질의, 쿼리 같은 낯선 용어들이 쓰이지만, 여기서는 그냥 자료 조회라고 생각하고 넘어가면 됩니다) 이런 자료 조회를 할 때 사용하는 SQL 구문이SELECT 구문입니다. 이 구문은 크게 세 부분으로 구성되는데, 첫번째는 원하는 자료의 칼럼들을 지정하고, 두번째는 그 칼럼들이 있는 테이블들 - from 절이라고 하고, 그 다음은 원하는 부분을 지정하는 조건절 - where 절이라고 합니다. 이 where 절은 선택적으로 사용됩니다. where 절이 생략되면 지정한 테이블의 모든 자료를 보여줍니다.

SELECT * FROM weather;

(여기서 * 문자는 "모든 칼럼"을 의미합니다.) 위 쿼리의 결과는 다음과 같이 나오겠지요:

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 <st1:city w:st="on">San Francisco</st1:city> |      46 |      50 | 0.25 | 1994-11-27
 <st1:city w:st="on">San Francisco</st1:city> |      43 |      57 |    0 | 1994-11-29
 <st1:city w:st="on">Hayward</st1:city>       |      37 |      54 |      | 1994-11-29
(3 rows)

물론 사용자가 직접 입력한 그 내용으로 말입니다. 그럼 다음 다른 예로:

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

결과는:

     city      | temp_avg |    date
---------------+----------+------------
 <st1:city w:st="on">San Francisco</st1:city> |       48 | 1994-11-27
 <st1:city w:st="on">San Francisco</st1:city> |       50 | 1994-11-29
 <st1:city w:st="on">Hayward</st1:city>       |       45 | 1994-11-29
(3 rows)

각 도시의 일평균 기온을 구하는 방법입니다. 여기서 AS 예약어는 생략되어도 되고, 이것은 그 칼럼의 새로운 이름을 지정할 때 사용합니다.

 

where 절(조건절)에는 각종 불린연산자(boolean operator)들을 사용해서 조건을 지정할 수 있습니다. 다음은 San Francisco에서 비가 온 날을 찾는 쿼리입니다.

SELECT * FROM weather
    WHERE city = '<st1:city w:st="on">San Francisco</st1:city>'
    AND prcp > 0.0;

출력결과:

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 <st1:city w:st="on">San Francisco</st1:city> |      46 |      50 | 0.25 | 1994-11-27
(1 row)

마지막으로, 출력되는 그 결과의 정렬 방식을 지정하거나 똑 같은 값에 대해서는 하나만 보이도록 할 수도 있습니다:

SELECT DISTINCT city
    FROM weather
    ORDER BY city;
     city
---------------
 <st1:city w:st="on">Hayward</st1:city>
 <st1:city w:st="on">San Francisco</st1:city>
(2 rows)

물론 DISTINCT , ORDER BY 구문이 각각 따로 사용되어도 됩니다.

 

2.6. 테이블 JOIN

지금까지 하나의 쿼리에서 하나의 테이블만을 사용했었지요. 지금부터는 하나 이상의 테이블 - 물론 정확히 말해서 테이블이 아닌 뷰가 될 수도 있고, SELECT 구문으로 만들어지는 특정 row들일 수도 있고, 심지어 서버 함수의 리턴값으로 사용된 row들일 수도 있습니다 - 을 같이 연결(JOIN)해서 자료를 뽑아내는 것을 다룰 것입니다. 이것을 RDBMS 용어로 테이블 JOIN이라고 합니다. 지금까지 사용한 테이블들을 예를 들어, 특정 도시의 위치와 날씨를 함께 나열하려면, 가장 단순히 생각해서, 일단 weather테이블의 내용을 하나씩 나열하면서, 그 옆에 weather 테이블에 있는 city 칼럼값을 가지고, cities 테이블을 조회해서 location칼럼값을 같이 보여주면 되겠지요. 

참고: 지금 이야기는 단지 개념적인 이야기일 뿐입니다. 실재 서버의 join작업은 보다 복잡하게 구현됩니다. 여기서는 이 내부 원리까지 굳이 알아야 할 이유는 없겠지요.

아무튼 위에서 원하는 결과를 만들어 내려면 다음과 같은 구문을 사용합니다:

SELECT *
    FROM weather, cities
    WHERE city = name;
     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 <st1:city w:st="on">San Francisco</st1:city> |      46 |      50 | 0.25 | 1994-11-27 | <st1:city w:st="on">San Francisco</st1:city> | (-194,53)
 <st1:city w:st="on">San Francisco</st1:city> |      43 |      57 |    0 | 1994-11-29 | <st1:city w:st="on">San Francisco</st1:city> | (-194,53)
(2 rows)

위 결과에서 다음 두 가지를 주의 깊게 보셔야 합니다:

·         이 결과에서는 Hayward 도시 정보가 빠져있습니다. 이것은 cities 테이블에 이 도시에 대한 정보가 빠져있기 때문입니다. 즉, 위 구문에서는 where 절 다음에 나오는 조건에 일치하는 자료들만 보이게 됩니다. 물론 이 글 아래에서 이 문제는 다른 방법으로 보완할 것입니다.

·         다른 하나는 도시 이름이 두 개가 보이는 것입니다. 잘못된 결과가 아니라, 출력 칼럼을 지정하는 곳에서 * 문자를 사용해서 모든 칼럼을 보겠다고 했으니, weather 테이블과 cities 테이블에 있는 각각의 도시 이름이 모두 보이게 된 것입니다.이 문제는 다음과 같이 보고 싶은 칼럼만 지정함으로 해결 할 수 있습니다:

·         SELECT city, temp_lo, temp_hi, prcp, date, location
·             FROM weather, cities
    WHERE city = name;

연습문제: where 절이 빠지면 어떻게 되는지도 한번 살펴보세요. 그리고 왜 그렇게 보였는지도 한번 생각해보시고.

 

지금까지는 사용된 칼럼 이름이 각각 고유한 이름들이 있었기 때문에, 별 문제없이 자동으로 각각의 칼럼이 있는 테이블에서 자료를 가져왔습니다. 하지만, 만일 같은 칼럼 이름이 여러 테이블에 똑 같이 있다면, 윗 구문처럼 사용하면, 서버는 어떤 테이블에서 그 칼럼을 참조해야 하는 지를 몰라 오류를 냅니다. 그래서, 일반적으로 join 구문을 작성할 때는 다음과 같이 칼럼 이름 앞에 테이블 명을 함께 지정해서 사용합니다:

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

위의 JOIN 구문은 다음과 같이 다른 표현 방식으로 사용될 수도 있습니다:

SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);

물론 위 구문은 흔하게 사용되는 구문은 아니지만, 다음 OUTER JOIN 구문을 이해하는데 도움을 주기에 잠깐 언급했습니다.

 

이제 앞에서 이야기한 Hayward 문제를 풀어봅시다. 위 작업의 원인이 바로 두 테이블간에 도시 이름이 일치하는 자료만 뽑혀진다는 것인데, 원래 의도했던 결과는 한 테이블에 해당 도시가 없으면, 그 구해진 자료만이라도 나타났으면 하는 것입니다. 이런 경우에 바로 OUTER JOIN 구문을 사용합니다:

SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
 
     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 <st1:city w:st="on">Hayward</st1:city>       |      37 |      54 |      | 1994-11-29 |               |
 <st1:city w:st="on">San Francisco</st1:city> |      46 |      50 | 0.25 | 1994-11-27 | <st1:city w:st="on">San Francisco</st1:city> | (-194,53)
 <st1:city w:st="on">San Francisco</st1:city> |      43 |      57 |    0 | 1994-11-29 | <st1:city w:st="on">San Francisco</st1:city> | (-194,53)
(3 rows)

위 구문을 left outer join 구문이라고 합니다. 이것은 왼쪽 자료를 기준으로 오른쪽 자료가 만족하는 것이 없으면, 그 값은 null로 출력됩니다. outer join을 처리하는 방식은 left, right, full 세가지가 있습니다. 이 처리 방식의 지정은 outer join 앞에 지정합니다.윗 예제처럼.

 

연습문제: 이번에는 right outer join과 full outer join을 한 번 작성해서 그 결과가 어떻게 나오는지 살펴보고, 왜 그렇게 되었는지 살펴보세요.

 

하나의 테이블과 다른 하나의 테이블을 join 할 때, 그 다른 하나의 테이블이 바로 그 테이블이 될 수도 있습니다. 이것은 self join이라고 합니다. 예를 들어, 다음 쿼리는 현재 row의 최저기온이 다른 row의 최저기온보다 더 낮고, 최고 기온이 다른 row의 최고기온보다 더 높을 경우 그 row를 출력하는 것입니다 (기운차가 심한 도시를 다른 도시 (또는 같은 도시일 수도 있겠지요)들의 기온차와 함께 비교하려고 한 것이네요): 

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;
 
     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 <st1:city w:st="on">San Francisco</st1:city> |  43 |   57 | <st1:city w:st="on">San Francisco</st1:city> |  46 |   50
 <st1:city w:st="on">Hayward</st1:city>       |  37 |   54 | <st1:city w:st="on">San Francisco</st1:city> |  46 |   50
(2 rows)

여기서 W1, W2 는 각각 weather table의 별명(alias)입니다. 이렇게 같은 테이블을 join 할 때, 서로 구분하기 위해서 이런 별명이 사용되기도 하고, 다음과 같이 테이블이 서로 틀리지만, 구문을 좀더 쓰기 편하게 하기 위해서 사용되기도 합니다:

SELECT *
    FROM weather w, cities c
    WHERE w.city = cinema;

가장 일반적인 join 구문 표현 방법입니다. 주로 테이블의 약식표기를 사용하지요.

 

2.7. 집계 함수(Aggregate Functions)

다른 대부분의 데이터베이스 제품들과 마찬가지로 PostgreSQL에서도 집계 함수(aggregate function)를 사용할 수 있습니다.집계 함수란 입력이 여러 개의 row이고, 출력이 하나인  결과인 것을 말합니다. 테이블의 전체 row 수를 구하는 count, 평균(avg), 총합(sum), 최대값(max), 최소값(min) 등등이 이런 함수들 입니다.

다음은 최고 기온을 구하는 쿼리입니다:

SELECT max(temp_lo) FROM weather;

 max

-----

  46

(1 row)

여기서 이 최고 기온의 해당 도시가 무엇인지 알기 위해서 다음과 같이 생각할 수도 있겠지요:

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     잘못된구문

하지만, 위 쿼리의 결과는 오류를 냅니다. 왜냐하면, WHERE 절 다음에는 집계함수를 사용할 수 없기 때문입니다. (이렇게 되는 이유는 위 예를 들어, max() 함수 자체가 select를 하지 않으면 나올 수 없는 값이기 때문입니다) 이런 문제는 다음과 같은 쿼리로 풀어야지 원하는 결과를 볼 수 있습니다: 

SELECT city FROM weather

    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city

---------------

 <st1:city w:st="on">San Francisco</st1:city>

(1 row)

위 구문에서 where 다음에 나오는 (select ....) 구문을 서브쿼리(subquery)라고 합니다. 하나의 쿼리에서 서브쿼리가 있으면, 서버는 먼저 서브쿼리를 처리하고, 그 결과를 서브쿼리가 있는 자리에 대치시키고 다시 쿼리를 합니다.

 

집계 함수는 일반적으로 GROUP BY 절과 함께 사용하면 보다 다양하고 유용한 결과를 볼 수 있습니다. 예를 들어 각 도시별 최고기온을 살펴보려면 다음과 같은 쿼리를 사용합니다:

SELECT city, max(temp_lo)

    FROM weather

    GROUP BY city;

     city      | max

---------------+-----

 <st1:city w:st="on">Hayward</st1:city>       |  37

 <st1:city w:st="on">San Francisco</st1:city> |  46

(2 rows)

 

여기서 위 결과 가운데, 또 집계된 자료에 대해서 어떤 조건이 주어질 때는  HAVING 구문을 사용하지요:

SELECT city, max(temp_lo)

    FROM weather

    GROUP BY city

    HAVING max(temp_lo) < 40;

  city   | max

---------+-----

 <st1:city w:st="on">Hayward</st1:city> |  37

(1 row)

이것은 각 도시별 최고 기온이 40(주, 아마 화씨일듯)도가 되지 않는 목록들을 보는 것입니다. 마지막으로 도시 이름이 "S"로 시작하는 도시들 가운데서 위와 같은 조건에 일치하는 자료를 찾으려면 다음과 같은 쿼리가 사용됩니다:

SELECT city, max(temp_lo)

    FROM weather

    WHERE city LIKE 'S%'

    GROUP BY city

    HAVING max(temp_lo) < 40;

LIKE 연산자는 패턴 매칭 관련 연산자인데, 자세한 것은 사용자 안내서를 참조하십시오.

집계 함수를 사용 할 때는 where 절과 having 절의 관계를 반드시 숙지하고 있어야 합니다. where 절은 조회할 집계되지 않은 자료에 대한 조건이고, having은 집계된 자료에 대한 조건입니다. 그래서, where 절의 조건으로 having 절이 사용될 수 없습니다. 일반적으로 having 절 다음에는 집계함수가 사용됩니다. 물론 더 깊게 이야기하면, where 다음에 having이 올 수 있고, having 다음에 집계 함수가 오지 않을 수도 있습니다. 하지만, 이런 골치 아픈 부분에 대해서는 이곳에서 언급해야 할 이유가 없을 것 같습니다. 여기서는 단지, where 다음에는 집계할 대상에 대한 조건이, having 다음에 그 자료를 집계한 값에 대한 조건을 사용한다는 것만 이해하시면 됩니다.

(주, 집계 함수를 유연하게 사용한다면 보고서 작성과 응용프로그램의 코딩에 엄청난 도움을 줍니다. select 구문에서 join과 함께 제일 까다로운 부분이기도 하지만 잘 숙지하셔서 유용하게 사용하시길)

 

2.8. 자료 갱신

이미 입력되어 있는 자료를 수정하려면, UPDATE 구문을 사용합니다. 다음 구문은 전체 자료에 대해서 11월 28일 최고/최저 기온을 각각 2도씩 낮추는 구문입니다:

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

이 구문이 제대로 적용되었지 살펴보지요:

SELECT * FROM weather;
 
     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+-----------
 <st1:city w:st="on">San Francisco</st1:city> |      46 |      50 | 0.25 | 1994-11-27
 <st1:city w:st="on">San Francisco</st1:city> |      41 |      55 |    0 | 1994-11-29
 <st1:city w:st="on">Hayward</st1:city>       |      35 |      52 |      | 1994-11-29
(3 rows)

 

2.9. 자료 삭제

weather 테이블에서 더 이상 Hayward 관련 자료가 필요 없어져서 삭제하고자 한다면, 이때, DELETE 구문을 사용합니다:

DELETE FROM weather WHERE city = '<st1:city w:st="on">Hayward</st1:city>';

삭제 되었는지 한 번 살펴보지요. 

SELECT * FROM weather;
     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+-----------
 <st1:city w:st="on">San Francisco</st1:city> |      46 |      50 | 0.25 | 1994-11-27
 <st1:city w:st="on">San Francisco</st1:city> |      41 |      55 |    0 | 1994-11-29
(2 rows)

다음과 같은 구문도 가능합니다: 

DELETE FROM tablename;

아무런 조건 없이 그냥 해당 테이블의 모든 자료를 지우는 쿼리입니다. 결과는 당연히 그 해당 테이블에는 아무런 자료도 없게 되겠지요. 이때, 그 지워야 할 자료가 아주 많이 있다면, 시간이 꽤 걸릴 것이고 그 동안 서버는 클라이언트에게 아무런 응답도 보내지 않습니다.

 

제3장. 고급 기능

3.1. 소개

이전 장에서는 자료를 저장하고, PostgreSQL 안에 있는 데이터를 조작하는 기본적인 SQL 명령들에 대해서 살펴보았습니다. 이 장에서는 좀더 심화된 SQL 명령들에 대해서 간단히 살펴 볼 예정입니다. 이런 명령들은 관리 작업을 단순화 시키며, 자료의 손실,손상을 막을 수 있는 역할을 합니다. 아울러 이 자습서의 끝으로 PostgreSQL만의 고유 기능들에 대해서도 간단히 소개할 예정입니다.

 

여기서도  2장에서 예제로 사용했던 그 자료들을 가지고 예제를 설명합니다. 만일 읽어보지 않으셨다면, 한 번 정도 간단하게나마 읽어 보면 이 장을 읽는데 도움이 많이 될 것입니다. 이 장에서 다루는 몇몇 예제는 소스의 tutorial 디렉토리안에 있는 advanced.sql 파일에서 살펴 볼 수 있습니다. 또한 이 파일에는 이 장에서 사용할 예제 자료들도 함께 있습니다. 이 자료들은2장에서 입력한 자료들은 포함하고 있지 않습니다. (이 파일을 사용하는 방법은 2.1 섹션을 참조하십시오)

 

3.2. 뷰(view)

2.6 섹션에서 사용된 쿼리를 다시 한 번 살펴보지요. 그곳에서 사용되었던 쿼리들은 단일 테이블 조회할 때보다 비교적 복잡한 편입니다. 이런 쿼리에서 특정 원하지 않는 칼럼을 빼고 출력하고 싶다면, 그 복잡한 쿼리를 다시 한번 입력해야 하는 불상사가 생기겠지요. 게다가 이런 작업들이 아주 빈번히 일어난다면, 여간 귀찮은 일이 아닙니다. 그래서, 여러 작업들을 모두 흡수 할 수 있는 범용 쿼리를 하나 만들고 그것을 마치 테이블처럼 사용할 수 있다면 편하겠지요. 여기서 view 라는 새로운 릴레이션(relation, 관계, PostgreSQL에서는 객체라고 합니다) 개념이 등장합니다. 뷰를 만드는 방법은 다음과 같습니다:

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;
 
SELECT * FROM myview;

사용이 유연한 뷰를 만드는 것은 좋은 자료구조 설계의 핵심적인 요소입니다. 일단 기본적인 뷰를 만들고 그것을 응용프로그램에서 사용한다면, 뷰가 참조하는 원래 테이블의 자료구조가, 만들어진 뷰에 영향을 미치지 않는 범위에서는 얼마든지 확장되고 수정될 수 있겠지요. 이렇게 되면 응용프로그램의 개발 부담을 줄여주는 것이 됩니다.

 

뷰는 테이블이 사용되는 자리에 그대로 사용될 수 있습니다. 하지만 뷰를 만드는 테이블 자리에 다른 뷰를 사용하는 경우는 일반적이지 않죠.

 

3.3. Foreign Keys

(주, 흔히 외래키라고 옮기기는 하는데, 외래열쇠라고 하는 것도 웃기고, 그냥 영문 그대로 쓰기로 했습니다)

 

2장에서 사용한 weather, cities 테이블의 관계를 다시 한 번 살펴봅시다. 이 두 테이블 간에 아무런 관계 조건이 없다고 가정한다면, 다음과 같은 문제점들이 발생할 것입니다. 두 테이블을 도시명으로 조인해서 자료를 찾고자 하는데, inner join 일 경우는 사용자가 한 테이블에서 도시명을 잘못 입력했다면, 그 자료는 완벽하지 찾을 수 없는 사태가 발생하겠지요. 또 한 테이블에서 도시명을 바꾸었을 경우도 그렇게 될 것이고, 또 cities 테이블에서 특정 도시 정보가 삭제가 되어버리면 weather 테이블 안에 있는 그 도시의 날씨 정보가 전혀 쓸 모 없게 되어버립니다. 그래서, weather 테이블에 자료를 입력할 때는 반드시 cities 테이블에 있는 도시명을 사용해야지만 입력할 수 있도록 해야겠다고 테이블 관계를 다시 정의합니다. 이것을 RDBMS 용어로 참조 무결성(referential integrity) 이라고 합니다. 이것을 구현하기 위해서 단순하게 생각해서 weather 테이블에 자료를 입력하기 전에, 먼저 입력될 도시명이 cities 테이블에 있는지 살펴보고 있다면 입력작업을 계속 진행하고, 없다면 입력작업은 중지하도록 프로그램 만들 수 있겠지요. 처음에 아무런 참조 무결성 조건이 없는 상태보다는 좀 더 안전해졌지만, 아직도 많은 문제점들을 안고 있습니다 (앞에서 이야기한 update, delete 관련 작업에서). 이런 문제를 PostgreSQL에서는 다음과 같이 간단히 Foreign Key를 지정함으로 해결할 수 있습니다:

CREATE TABLE cities (
  city     varchar(80) primary key,
  location point
);
 
CREATE TABLE weather (
 city      varchar(80) references cities,
 temp_lo   int,
 temp_hi   int,
 prcp      real,
 date      date
);

두 테이블을 다시 만들고 다음과 같이 입력하면:

INSERT INTO weather VALUES ('<st1:city w:st="on">Berkeley</st1:city>', 45, 53, 0.0, '1994-11-28');
ERROR:  <unnamed> referential integrity violation 
        - key referenced from weather not found in cities

이렇게 오류를 내면서 입력작업이 중지됩니다. <unnamed> 제약조건이 참조 무결성 조건에 위배 되었다 - weather 에서 지정한key들 cities 에서 못 찾았다. 이런 뜻입니다.

 

foreign key 기능은 응용 프로그램에서 해야 할 일들을 대폭 줄여줌과 동시에 자료의 관계성을 보다 견고하게 만들어줍니다. foreign key 기능은 여기서 언급한 것보다 훨씬 많은 것을 제공하고 있습니다. 이 자습서에서 그 세세한 기능들에 대해서는 언급하지 않겠습니다. 자세한 사항은 사용자 안내서를 참조하십시오.

 

(주, foreign key의 사용의 시스템의 속도를 저하시키고, 유지 보수가 어렵기 때문에 가능한 한 사용하지 않는 것이 좋다고 혹자들은 이야기 합니다. 하지만, 그 반대로 잘 만들어진 foreign key는 확실히 응용프로그램의 코딩량을 줄여주며, 자료를 안전하게 관리할 수 있도록 합니다. - 아주 중요한 이야기임, 시험에 나옴)

 

그래서, 이 부분에 대해서 꼭 사용자 안내서를 읽어가면서 꼼꼼히 공부해 둡시다

 

3.4. 트랜잭션

트랜잭션 이란 모든 데이터베이스 시스템에서 기본적인 한 개념입니다. 트랜잭션의 핵심은 여러 개의 작업이 최종적으로는 하나로 취급된다는 것입니다. 이것을 전부 적용 아니면 전부 취소 작업(all-or-nothing operation)이라고 부릅니다. 각각의 트랜잭션은 독립적이어서 동시에 발생한 트랜잭션에 대해서는 그 트랜잭션 안에서의 데이터만 적용됩니다. 또, 하나의 트랜잭션 안에서 어떤 작업 도중 오류가 발생 되었다면, 이전에 적용되었던 모든 작업들에 대해서는 모두 취소됩니다.

 

예를 들어서, 간단한 은행 데이터베이스를 생각해봅시다. 이 은행 데이터베이스에는 각 계좌의 현 잔액을 보관하는 accounts 테이블과, 각 계좌의 거래 내역을 보관하는 branches 테이블로  구성되어있습니다. 이때, Alice 계좌에서 $100.00 빼서 Bob 계좌로 이체하려고 합니다. 이것을 구현한다면, 가장 간단하게 아래와 같이 처리하면 되겠지요.

UPDATE accounts SET balance = balance - 100.00
    WHERE name = '<st1:city w:st="on">Alice</st1:city>';
UPDATE branches SET balance = balance - 100.00
    WHERE name = 
        (SELECT branch_name FROM accounts WHERE name = '<st1:city w:st="on">Alice</st1:city>');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = 
        (SELECT branch_name FROM accounts WHERE name = 'Bob');

물론 계좌 이체가 이렇게 간단하게 움직여지지는 않겠지만, 여기서 중요한 것은 계좌 이체 작업을 하려면, 적어도 네 개의 독립된 update 구문으로 작업해야 한다는 것입니다. 이런 작업을 하는데, 만일 위의 네 개의 작업 도중 부분적으로 작업을 성공하고,그 나머지는 실패한다면, 각 계좌에 남아있는 금액이 이상해지겠지요. 이런 사태를 막기 위해서, 하나의 작업군(여러 개의 작업)이 전부 처리가 되든지, 아니면, 전부 취소가 되는 이런 작업이 필요합니다. 이런 작업을 할 때, 트랜잭션을 이용합니다. 이런 하나의 트랜잭션 내에서의 일군의 작업이 하나로 처리되는 것을 데이터베이스 용어로 트랜잭션의 원자성(atomic)이라고 합니다.

 

또한 트랜잭션 작업이 정상적으로 끝났을 경우에는 그 변경된 자료가 어떠한 간섭 없이 저장되어야 하고, 저장된 자료는 손상되지 않아야 합니다. 예를 들어 Bob의 현금 인출 작업이 진행되고 있는 사이에도 Bob에게 현금을 주기 전까지 어떠한 오류도 발생되어서는 안되겠지요. 이것을 구현하기 위해서 트랜잭션 기능을 제공하는 데이터베이스에서는 트랜잭션이 정상적으로 종료되었다고 알려주기 전에도 이미 하나의 트랜잭션에서 발생하는 모든 작업들은 영구저장장치(예, 하드디스크)에 기록을 해둡니다. (주,이를 내구성(durability)이라고 합니다)

 

트랙잭션에서 중요한 또 하나의 속성은 하나의 트랜잭션은 다른 트랜잭션에 의해서 간섭 받지 않아야 합니다. 즉, 동시에 여러 개의 트랜잭션이 발생했을 때, 각각의 트랜잭션은 다른 트랜잭션에서 변경되고 있는 자료들에 대해서는 참조하거나 간섭할 수 없어야 합니다.(주, 이것을 고립성(isolation) 이라고 합니다) 예를 들어서,  모든 계좌의 현 잔액 합계를 구하는 트랜잭션이 작업 중인데, Alice나 Bob의 현 잔액을 바꾸는 다른 트랜잭션에 의해서 그 계좌의 현 잔액이 바뀌게 된다면, 정확한 그 시점의 현 잔액 합계를 구할 수가 없겠지요. 그래서, 트랜잭션은 각각의 명령이 수행 될 때 마다 그 변경 사항이 데이터베이스의 원래 자료에 영향을 주는 것이 아니라, 트랜잭션 영역 안에 있는 모든 작업이 끝났을 때, 한꺼번에 그 변경 사항이 데이터베이스에 적용됩니다. 이때부터 다른 트랜잭션이 그 변경된 데이터를 참조 할 수 있게 됩니다. (주, 이것을 정합성(consistency)이라고 합니다) 

 

PostgreSQL에서 트랜잭션 작업을 하려면, 그 해당 작업 앞뒤로 BEGIN 명령과 COMMIT 명령을 지정합니다. 다음과 같은 형태가 트랙잭션을 사용하는 예입니다:

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = '<st1:city w:st="on">Alice</st1:city>';
-- etc etc
COMMIT;

물론, 이 트랜잭션 작업 도중 의도적으로 모든 작업을 취소 해야 할 상황이 발생하면, COMMIT 명령 대신에 ROLLBACK 명령을 사용하면 됩니다.

 

PostgreSQL에서는 내부적으로 모든 각각의 SQL 작업에 대해서 각각의 트랜잭션을 만들고, 그 명령이 성공하면, COMMIT 처리를 합니다. 내부적으로 일어나는 일이어서 사용자는 모르고 있겠지만, 개념적인 이해를 위해서 설명 드립니다. PostgreSQL에서는 단지, 하나의 작업군이 모두 처리 되든지, 아니면 모두 처리 되지 않던지 하는 작업이 필요할 경우에 사용자가 직접 BEGIN,COMMIT 명령을 지정해서 트랜잭션 영역을 지정하고 작업하면 됩니다.

참고: S몇몇 클라이언트 라이브러리는 자동으로 BEGIN, COMMIT c명령을 붙여 실행되기 때문에, 사용자가 트랜잭션 지정하면 오류를 내는 경우도 있습니다. 자세한 것은 해당 라이브러리 문서를 참조하십시오.

 

3.5. 상속

상속은 객체지향 데이터베이스에서 사용하는 개념입니다. 상속은 데이터베이스 설계 방법에 대한 새로운 가능성을 제시합니다.

 

cities (도시) 테이블과 capitals (주도 州都) 테이블 두 개를 만들어봅시다. 실 세계의 개념으로 본다면, 도시를 모두 찾아보자면, 당연히 주도도 포함되겠지요. 여기서 테이블 상속 개념은 출발합니다. 만일 전통적인 개념으로 자료구조를 설계한다면 다음과 같이 하겠지요:

CREATE TABLE capitals (
  name       text,
  population real,
  altitude   int,    -- (in ft)
  state      char(2)
);
 
CREATE TABLE non_capitals (
  name       text,
  population real,
  altitude   int     -- (in ft)
);
 
CREATE VIEW cities AS
  SELECT name, population, altitude FROM capitals
    UNION
  SELECT name, population, altitude FROM non_capitals;

이렇게 하고, cities 뷰를 이용하면, 모든 도시를 찾을 수 있습니다. 하지만, 하나의 이름으로 자료를 바꾸려고 할 때는 결국 두 번의 쿼리가 있어야 합니다. 이 문제를 다음과 같이 풀어봅니다:

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);
 
CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

이때, capitals 테이블에서 지정한 inherits 예약어가 바로 테이블을 상속 받겠다는 것을 나타냅니다. 위 경우라면, cities 테이블의 모든 칼럼을 상속 받는 것을 의미합니다. 윗 name 칼럼의 text 자료형은 PostgreSQL에서 사용하는 고유한 가변 길이 문자열 자료형입니다. PostgreSQL에서는 하나의 테이블은 자기 자신이 상위 테이블 (하나도 상속 받지 않은 테이블)이 되거나 여러 개의 상위 테이블을 상속 받을 수 있습니다. 또한 그 테이블의 자료 조회는 하위 테이블의 모든 자료를 포함해서 조회합니다.

 

예를 들어, 고도가 500 ft. 보다 큰 도시들을 모두 찾을 때, 물론 이때 주도도 포함하고자 할 때는 다음과 같은 쿼리를 이용합니다:

SELECT name, altitude
  FROM cities
  WHERE altitude > 500;

이 쿼리의 결과는 다음과 같습니다:

   name    | altitude
-----------+----------
 <st1:city w:st="on">Las Vegas</st1:city> |     2174
 Mariposa  |     1953
 <st1:city w:st="on">Madison</st1:city>   |      845
(3 rows)
   

이때, 주도를 빼고 검색하려면 다음과 같이 ONLY 예약어를 사용합니다:

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;
   name    | altitude
-----------+----------
 <st1:city w:st="on">Las Vegas</st1:city> |     2174
 Mariposa  |     1953
(2 rows)

이 ONLY 예약어는 앞에서 이야기한 모든 자료 조작 명령 -- SELECT, UPDATE, DELETE -- 에서 그대로 적용됩니다. 물론 빠진다면, 그 테이블의 하위 테이블 모두 적용됩니다.

 

3.6. 끝맺음

이 자습서는 PostgreSQL을 처음 접하는 이들에게 개략적인 안내를 하기 위해서 작성되었습니다. PostgreSQL은 이 자습서에서 미쳐 소개하지 못한 수 많은 기능들을 제공하고 있습니다. 이런 기능들에 대한 설명은 사용자 안내서와 프로그래머 안내서에서 자세히 다루고 있습니다.

 

한편, PostgreSQL에 대한 보다 근본적인 개론에 관심이 있으면, PostgreSQL 공식 홈페이지를 방문해 보십시오. 다양한 자료들을 접할 수 있을 것입니다.

저작자표시 비영리 변경금지

'개발' 카테고리의 다른 글

Generics:제네릭스에 대하여 스터디 #1  (0) 2013.10.23
윈도우8 프로모션 업그레이드 구입한뒤 윈도우 재설치 할때.  (0) 2013.01.20
xPlatform 유효성 체크  (0) 2013.01.11

관련글 더보기

티스토리툴바