PostgreSQL

Part I. Tutorial

목차

1. 시작하기
1.1. 설치
1.2. 아키텍 기초
1.3. 데이터베이스 생성
1.4. 데이터베이스에 액세스
2. SQL 언어
2.1. 소개
2.2. 개념
2.3. 새 테이블 만들기
2.4. 행으로 테이블 채우기
2.5. 테이블 쿼리
2.6. 테이블 간 조인
2.7. 집계 함수
2.8. 업데이트
2.9. 삭제
3. 고급 기능
3.1. 소개
3.2. 뷰
3.3. 외래 키
3.4. 트랜잭션
3.5. Window 함수
3.6. 계층
3.7. 결론 

 

1. 시작하기

1.1. 아키텍처

 PostgreSQL은 클라이언트/서버 모델을 사용합니다. PostgreSQL 세션 은 다음과 같은 프로세스(프로그램)로 구성됩니다.

• 데이터베이스 서버 프로그램  : 데이터베이스 파일을 관리하는 서버 프로세스는 클라이언트의 연결을 수락하고 데이터베이스 작업을 수행합니다. 
• 데이터베이스 작업을 수행하려는 사용자의 클라이언트(프런트엔드) 애플리케이션 : 클라이언트는 텍스트 도구, 그래픽 애플리케이션, 웹 페이지를 표시하기 위해 데이터베이스에 액세스하는 웹 서버 또는 데이터베이스 유지.관리 도구입니다.

일반적인 클라이언트/서버 응용 프로그램과 마찬가지로 클라이언트와 서버는 TCP/IP 네트워크 연결을 통해 통신합니다. 

PostgreSQL 서버 는 클라이언트의 여러 동시 연결을 처리할 수 있습니다. 이를 위해 각 연결에 대해 새로운 프로세스를 시작( "fork" )합니다. 그 시점부터 클라이언트와 fork된 서버 프로세스는 원래 postgres프로세스의 개입 없이 통신합니다.

1.2. 설치

https://www.postgresql.org/download/ 에서 설치 환경(OS, Windows, MacOS, Linux, Solaris 등) 에 맞는 설치파일을 다운로드 한 후 설치한다.

윈도우에 설치 시 아래 4개 컴포넌트가 설치된다.

• PostgreSQL Server : PostgreSQL Database Server
• pgAdmin : PostgreSQL database 관리와 작업을 위한 GUI 툴
• Stack Builder : 추가 툴이나 driver 그리고 Application 등을 다운로드를 위한 
• Command Line Tools : DB접속을 위한 Cli 툴

윈도우에 설치 시 superuser(posgres)에 대한 패스워드, DB Port, Locale 입력화면이 나오면 원하는 값 입력한다. 

Linux나 MacOS 에 설치는 https://www.postgresql.org/download  페이지를 참고한다.

1.3. DB 접속

1) psql 로 접속

Database가 설치된 서버에서 접속 시에는 host를 생략할 수 있고, default port를 사용하는 경우에는 port 생략 가능

$ psql -h {host} -p {port} -U {user} -d {database}

2) SQL Shell을 이용한 접속

Windows 에 설치된 "SQL Shell(psql)" 을 실행시킨 후 DB 접속을 위한 정보를 차례로 입력한다.

 

3) client 툴로 접속

클라이언트 툴(TablePlus, Psequel, PgAdmin, Postico, Azure Data Studio, DBeaver, Command Line Tool 등)을 이용하여 DB에 접속한다. 본 문서에서는 TablePlus로 접속하는 방법을 설명한다.

 

- TablePlus 실행 화면에서 마우스 오른쪽 버튼을 클릭한 다음 "[New] - PostgreSQL" 선택한다

 

- 아래와 같이 DB 접속을 위한 화면에 DB 정보를 입력한다.

-

• Name : 임의의 별칭(Alias) 입력
• Host : DB서버의 IP나 hostname, endpoint 등을 입력. 본 문서에서는 PC에 설치했으므로 localhost 입력
• Port : 설치 시 입력했던 Port 입력(Default Port는 5432)
• User : postgress 입력
• Password : 설치 시 입력했던 password 입력
• Database : 기본적으로 postgres database 가 설치되므로 postgresql을 입력해도 되고 공란으로 유지해 된다.

입력이 완료되면 [Test] 를 수행한 후 정상 접속이 확인되면 [Save] 버튼을 클릭하여 저장한다.

 

4) java, python 등의 프로그램으로 접속

 

1.3. 데이터베이스 생성

mydb 데이터베이스 생성:

CREATE DATABASE mydb;

 

 

salesspace 테이블스페이스를 사용하여 사용자 salesapp이 소유한 데이터베이스 sales 생성

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

 

1.4. 도메인 생성

postgreSQL의 자료형에는 Domain Types이 있습니.

도메인은 선택적 제약 조건(허용되는 값 집합에 대한 제한 사항)이 있는 데이터 유형입니다. 도메인을 정의하는 사용자가 도메인의 소유자가 됩니다. 

 

DOMAIN 생성 문법

CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

• name: 사용자 정의 데이터 유형의 이름
• data_type: 데이터 유형
• COLLATE : 도메인에 대한 데이터 정렬입니다. 데이터 정렬이 지정되지 않은 경우 도메인은 기본 데이터 유형과 동일한 데이터 정렬 동작을 합니다. 
• DEFAULT default_value: 기본값 (선택 사항)
• CONSTRAINT constraint_name: 제약 조건의 이름 (선택 사항)
• NOT NULL: NULL 값을 허용하지 않음 (선택 사항)
• CHECK (expression): 값 유효성 검사를 위한 표현식 (선택 사항)

Domain Examples

이 예에서는 us_postal_code 데이터 유형을 생성한 다음 테이블 정의에 해당 유형을 사용합니다. 정규식 테스트는 값이 유효한 미국 우편번호인지 확인하는 데 사용됩니다. 아래 도메인 제약은 5자리 정수 입력 허용과 5자리-4자리 정수 입력만을 허용하는 규칙입니다.

CREATE DOMAIN us_postal_code AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

CREATE TABLE us_snail_addy (
  address_id SERIAL PRIMARY KEY,
  city TEXT NOT NULL,
  postal us_postal_code NOT NULL
);

 

INSERT INTO us_snail_addy(city, postal) VALUES('LOS ANGELES', '12345-1234');  /* 입력성공 */
INSERT INTO us_snail_addy(city, postal) VALUES('LOS ANGELES', '12345-12345'); /* 입력실패 */

INSERT INTO us_snail_addy(city, postal) VALUES('LOS ANGELES', 'a2345-1234'); /* 입력실패 */

 

현재 접속한 Database 확인

현재 접속한 데이터베이스를 확인한다.

SELECT CURRENT_DATABASE();

 

현재 접속한 User 확인

현재 접속한 user 를 확인한다.

SELECT CURRENT_USER;

 

1.5. 스키마(Schema) 생성

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

where role_specification can be:

    user_name
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

현재 데이터베이스에 새 스키마를 생성합니다.

스키마 이름은 현재 데이터베이스에 있는 기존 스키마의 이름과 달라야 합니다.

스키마는 본질적으로 네임스페이스입니다. 

AUTHORIZATION절이 사용되는 경우 생성된 모든 객체가 해당 사용자의 소유라는 점을 제외하면 스키마를 생성한 후 실행되는 별도의 명령과 기본적으로 동일하게 처리됩니다 .

--  스키마 경로 설정

set search_path To postgres, public;

 

위와 같은 설정에서 테이블 명칭만 입력하면 DBMS 가 해당 테이블을 찾을 때
postgres ➔ public 순으로 찾게 됩니다.

 

PostgreSQL에서는 모든 연결이 하나의 데이터베이스에서만 작동합니다. 따라서 데이터베이스를 변경하려면 다른 데이터베이스를 지정하여 다시 연결해야 합니다. MySQL 에 존재하는 'use dbname' 명령어가 없습니다.

 

 

1.6. 계정(User) 생성

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

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

 

name : 계정명

SYSID : SYSID 절을 사용하여 새 사용자의 PostgreSQL 사용자 ID를 선택할 수 있습니다. 일반적으로 필요하지 않지만 고아 개체( orphaned object)의 소유자를 다시 만들어야 하는 경우 유용할 수 있습니다.
이를 지정하지 않으면 할당된 가장 높은 사용자 ID에 1을 더한 값(최소 100)이 기본값으로 사용됩니다.

CREATEDB | NOCREATEDB : 

데이터베이스 생성 권한을 정의합니다. default는 NOCREATEDB입니다.

 

CREATEUSER | NOCREATEUSER

데이터베이스 생성 권한을 정의합니다. default는 NOCREATEUSER 입니다.

 

IN GROUP groupname

사용자의 그룹 이름입니다. 여러 그룹 이름이 나열될 수 있습니다.

 

PASSWORD

사용자의 비밀번호를 설정합니다. 비밀번호 인증을 사용할 계획이 없다면 이 옵션을 생략할 수 있지만, 비밀번호 인증으로 전환하기로 결정하면 사용자는 연결할 수 없습니다. 비밀번호는 나중에 ALTER USER를 사용하여 설정하거나 변경할 수 있습니다.

 

ENCRYPTED | UNENCRYPTED

비밀번호가 시스템 카탈로그에 암호화되어 저장되는지 여부를 제어합니다. (두 가지 모두 지정되지 않은 경우 매개변수 password_encryption에 의해 결정됩니다.) 제시된 비밀번호 문자열이 이미 MD5 암호화 형식인 경우 ENCRYPTED 또는 UNENCRYPTED가 지정되었는지 여부에 관계없이 있는 그대로 암호화되어 저장됩니다. 시스템은 지정된 암호화된 비밀번호 문자열을 해독할 수 없습니다). 이를 통해 덤프/복원 중에 암호화된 비밀번호를 다시 로드할 수 있습니다.

 

VALID UNTIL : 비밀번호가 더 이상 유효하지 않게 되는 시간을 설정합니다. 생략하면 비밀번호는 항상 유효합니다.

 

--Create User

CREATE USER my_user_test WITH LOGIN NOSUPERUSER NOCREATEDB  NOCREATEROLE    INHERIT NOREPLICATION   CONNECTION LIMIT -1 PASSWORD 'xxxxxxx';

-- Grant connect to my data base

GRANT CONNECT ON DATABASE my_db_test TO my_user_test;

-- Grant usage the schema

GRANT USAGE ON SCHEMA my_sch_test TO my_user_test ;

-- Grant all table for SELECT, INSERT, UPDATE, DELETE

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_sch_test TO my_user_test;

 

 

1.7. 권한 부여

schema에 읽기 권한 주기

GRANT USAGE ON SCHEMA public TO <계정명>;

 

schema의 모든 table에 읽기 권한 주기

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

 

 

 

 

2. SQL 언어

 

2.1. 소개

 

2.2. 개념

 

2.3. 새 테이블 만들기

 

2.4. 행으로 테이블 채우기

 

2.5. 테이블 쿼리

 

2.6. 테이블 간 조인

 

2.7. 집계 함수

 

2.8. 업데이트

 

2.9. 삭제

 

3. 고급 기능

 

3.1. 소개

 

3.2. 뷰

 

3.3. 외래 키

 

3.4. 트랜잭션

 

3.5. Window 함수

 

3.6. 계층

 

3.7. 결론