[PostgreSQL] PostgreSQL DDL - DB, 테이블 생성 수정 삭제 등

DDL

DDL의 개념

• Data Definition Language 데이터 정의 언어
• 데이터베이스의 구조(객체)를 생성, 변경, 삭제하는 명령어

DDL 명령문의 종류

명령어	설명
CREATE	객체를 생성하는 명령어
ALTER	객체를 변경하는 명령어(추가, 수정, 삭제 등)
DROP	객체 자체를 삭제하는 명령어
TRUNCATE	객체와 그 구조는 유지하되, 데이터만 삭제하는 명령어
RENAME	객체의 명칭(이름)을 수정하는 명령어

DDL의 대상

대상(영문)	대상(한글)	설명
DATABASE	데이터베이스	최상위 저장 단위인 DB 자체
SCHEMA	스키마	객체(테이블 등)을 그룹화하는 네임스페이스
TABLE	테이블	데이터를 행(row)과 열(column)로 저장하는 기본 객체
INDEX	인덱스	검색 성능 향상을 위한 자료구조
VIEW	뷰	SELECT 결과를 저장한 가상 테이블 (실제 데이터 저장은 아님)
SEQUENCE	시퀀스	자동 증가 숫자를 생성하는 객체
ROLE	역할(권한 그룹)	권한을 묶어서 관리하는 객체 (사용자 개념을 포함)
USER	사용자	로그인 가능한 계정 (ROLE 중 로그인 가능한 ROLE)

• 컬럼(COLUMN)과 제약조건(CONSTRAINT)은 테이블의 구성요소로 DDL에서 사용된다.

DDL의 특징

• 데이터가 아닌, 데이터베이스의 구조를 다루는 명령문
• Auto Commit : 기본적으로는 명령어 실행시 즉시 데이터베이스에 반영된다.
• Rollback 불가 : 기본적으로는 실행한 명령을 되돌릴 수 없다.

Rollback 이 가능케 하려면 BEGIN + COMMIT 키워드를 사용해서 트랜잭션을 만들어줘야 한다.
이는 본 포스팅의 10. 실무 참고에서 추가로 다룬다.

1. Database

(1) 생성

• 기본 문법

CREATE DATABASE mydb;

• 옵션

CREATE DATABASE 데이터베이스_이름
[ WITH
    [ OWNER [=] user_name ]
    [ TEMPLATE [=] template ]
    [ ENCODING [=] encoding ]            # e.g. 'UTF8'
    [ LC_COLLATE [=] lc_collate ]        # e.g. 'ko_KR.UTF-8'
    [ LC_CTYPE [=] lc_ctype ]            # e.g. 'ko_KR.UTF-8'
    [ TABLESPACE [=] tablespace ]
    [ ALLOW_CONNECTIONS [=] boolean ]
    [ CONNECTION LIMIT [=] integer ]     # e.g. -1
    [ IS_TEMPLATE [=] boolean ]
] ;

(2) 삭제

DROP DATABASE mydb;

※ 접속 중인 세션이 있으면 삭제 불가

(3) 주석

COMMENT ON DATABASE mydb IS '내 DB';

2. Schema

(1) 생성

CREATE SCHEMA myschema;

(2) 삭제

DROP SCHEMA myschema;

(3) CASCADE 삭제 (하위 객체 포함)

DROP SCHEMA myschema CASCADE;

(4) 주석

COMMENT ON SCHEMA myschema IS '나의 스키마';

3. Table

(1) 생성

• 기본 예시

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

COMMENT ON TABLE users IS '사용자 기본 정보';
COMMENT ON COLUMN users.id IS '사용자 ID (PK)';
COMMENT ON COLUMN users.name IS '사용자 이름';
COMMENT ON COLUMN users.email IS '이메일 주소';

(2) 수정

• 컬럼 추가

ALTER TABLE users ADD COLUMN age INT;

• 컬럼 타입 변경

ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

• 컬럼 삭제

ALTER TABLE users DROP COLUMN age;

• 컬럼 이름 변경

ALTER TABLE users RENAME COLUMN name TO username;

• 테이블 이름 변경

ALTER TABLE users RENAME TO app_users;

(3) 삭제

• 삭제

DROP TABLE users;

• 존재할 때만 삭제

DROP TABLE IF EXISTS users;

• 주석 삭제

COMMENT ON TABLE 테이블이름 IS NULL;
COMMENT ON COLUMN 테이블이름.컬럼명1 IS NULL;

4. CONSTRAINT 제약조건

(1) Primary Key

CREATE TABLE orders (
  id SERIAL PRIMARY KEY
);

(2) Foreign Key

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT,
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

(3) Unique

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

(4) Check

ALTER TABLE users ADD CONSTRAINT age_check CHECK (age >= 0);

5. Index

(1) 생성

• 기본 인덱스

CREATE INDEX idx_users_email ON users(email);

• Unique Index

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

(2) 삭제

DROP INDEX idx_users_email;

6. View

(1) 생성

CREATE VIEW user_email AS
SELECT id, email FROM users;

(2) 수정

CREATE OR REPLACE VIEW user_emails AS
SELECT id, email FROM users WHERE email IS NOT NULL;

(3) 삭제

DROP VIEW user_emails;

7. Sequence

CREATE SEQUENCE my_seq;

SELECT nextval('my_seq');

DROP SEQUENCE my_seq;

8. Truncate vs Drop

(1) Truncate : 데이터만 삭제

TRUNCATE TABLE users;

(2) DROP : 데이터와 객체 삭제

DROP TABLE users;

9. CASCADE vs RESTRICT

DROP TABLE users CASCADE;    -- 의존 객체도 같이 삭제  
DROP TABLE users RESTRICT;   -- 의존 객체가 존재할 경우 삭제 실패

10. 실무 참고

• 없을 때만 생성

CREATE TABLE IF NOT EXISTS users ...;

• 있을 때만 삭제

DROP TABLE IF EXISTS users;

• 명시적인 DDL 트랜잭션 만들기

BEGIN;
CREATE TABLE test (id INT);
ROLLBACK; -- 문제 있으면 롤백
COMMIT;   -- 문제 없으면 커밋