PostgreSQL의 Table 관리

오픈소스 데이터베이스인 PostgreSQL의 Table을 관리하는 방법에 대해 정리한다.

 

PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.

Docker로 PostgreSQL 사용하기

 

PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.

공식 PostgreSQL Docker 이미지에 한글 적용하기

 

GitHub과 DockerHub 사이트는 다음과 같다.

• DockerHub - https://hub.docker.com/_/postgres
• GitHub - https://github.com/docker-library/postgres

 

PostgreSQL은 다음과 같은 구조를 가지고 있다.

각 기능은 다음과 같다.

• Users/Groups - 사용자 정보 관리
• Databases - 실제 레코드가 저장되는 Database 관리
• Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
• Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
• Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
• Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간

Table 관리

PostgreSQL의 데이터가 실제로 저장되는 공간이다. 

 

앞서 다룬 PostgreSQL 구조의 Tables 기능에 해당하며, Row와 Column으로 구성 된 공간이다.

 

PostgreSQL 데이터 타입

PostgreSQL에서 제공하는 데이터 타입은 아래 문서를 참고한다

Chapter 8. Data Types

 

주로 사용하거나 사용할 것으로 예상되는 데이터 타입은 아래와 같다.

사용 환경에 따라 다르기 때문에 주관적으로 정리한 것임을 밝혀둔다.

Name	Aliases	Description
Boolean	Bool	Boolean연산 (true/false)
Bytea		이진 data ("byte array")
character varying [ (n) ]	varchar [ (n) ]	가변길이 character string
Text		가변길이 character string
Json		문자 JSON data
Jsonb		이진 JSON data, decomposed
Macaddr		MAC (Media Access Control) address
Inet		IPv4, IPv6 host address
Cidr		IPv4 or IPv6 network address
Smallint	int2	signed 2bytes 정수형
Integer	int, int4	signed 4bytes 정수형
Bigint	int8	signed 8bytes 정수형
Smallserial	serial2	자동증가 2bytes 정수형
Serial	serial4	자동증가 4bytes 정수형
Bigserial	serial8	자동증가 8bytes 정수형
Real	float4	single precision floating-point number (4 bytes)
double precision	float8	double precision floating-point number (8 bytes)
Date		달력 날짜 (year, month, day)
time [ (p) ] [ without time zone ]		time of day (no time zone)
time [ (p) ] with time zone	timetz	time of day, including time zone
timestamp [ (p) ] [ without time zone ]		날짜와 시간 (no time zone)
timestamp [ (p) ] with time zone	timestamptz	날짜와 시간, including time zone
Uuid		universally unique identifier

 

참고로 Date와 Time 타입은 고유의 포맷을 가지므로 사용할 때 주의해야 한다.

 

 

Table 목록 확인

MySQL의 경우 SHOW TABLES; 라는 단순한 명령어로 Table의 목록을 확인할 수 있었다.

 

PostgreSQL의 경우 psql의 명령어를 사용하거나, SQL 명령어를 통해 확인할 수 있다.

postgres=# \dt
Did not find any relations.

 

위의 명령은 psql 소개 부분에서 연결 관계 테이블 조회에 사용한 명령어이다.

Table간에 ForienKey로 등록되어 연결 된 경우만 출력된다.

따라서 처음 실행하면 연결 된 테이블이 없으므로 위의 메시지가 출력된다.

 

SQL 명령어는 아래와 같이 사용한다.

postgres=# \c DATABASE_NAME  # 사용하려는 DB로 전환
postgres=# SELECT * FROM pg_catalog.pg_tables;

 

pg_로 시작하는 Schema와 Table은 Postgres에서 메타 정보를 저장하는데 사용한다.

특정 DB의 Table 목록은 pg_catalog라는 Schema 하위의 pg_tables 테이블에 이 저장되어 있다.

 

Table 생성

다른 RDBMS와 마찬가지로 Table을 생성하면서 특정 컬럼에 제약 조건을 지정할 수 있다.

이러한 제약 조건은 DB에 레코드를 입력하기 전에 타입이나 값의 조건을 충족하는지 확인한다.

값이나 타입에 대한 일관성을 유지하는데 매우 중요하다.

 

생성 방법

Table 생성 방법은 다른 RDBMS와 거의 동일하다.

 

아래 명령으로 생성한다.

CREATE TABLE table_name ( [
  column_name_1 data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
  column_name_2 data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
  …
] )

 

COLLATE는 Database 관리의 옵션 항목에서 다뤘던 LC_COLLATE 항목에 해당한다.

문자의 정렬 규칙에 관한 것으로, 자세한 내용은 아래 페이지를 참고한다.

24.2. Collation Support

 

column_constraint는 특정 컬럼에 대한 제약 조건을 의미한다.

제약 조건은 아래 항목에 별도로 다룬다.

 

가장 쉬운 예제는 다음과 같다.

postgres=# CREATE TABLE departments(      # departments 테이블 생성
    Department_id    integer PRIMARY KEY,  # 정수형, 중복과 Null 값 비허용
    Name    varchar(50)                    # 가변문자열 (영문기준 최대 50자)
);

 

 

제약조건

테이블을 생성하면서 특정 컬럼에 조건을 지정할 수 있다.

 

다음과 같은 유형이 있다.

구분	내용
Check	조건문으로 True 또는 False 확인
Not Null	Null 값을 허용하지 않음
Unique	중복 허용하지 않음, 유일해야 함
Primary Key	Unique + Not Null로 유일한 식별자
Foreign Key	외부 테이블에 있는 특정 컬럼의 값만 사용 외부 컬럼은 해당 테이블의 Primary Key이다.

 

Check

입력 값이 특정 조건을 만족하는지 확인한다.

postgres=# CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0));

 

CHECK 부분에서 price가 0보다 작거나 같으면 오류가 발생한다.

 

Not Null

해당 컬럼에는 Null 값을 허용하지 않는다.

postgres=# CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric);

 

""는 empty string이지 Null이 아니다. 참고해야 한다.

 

Unique

해당 컬럼 값은 유일해야 한다. 중복을 허용하지 않는다.

postgres=# CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric);

 

Unique에서는 Null이 허용된다.

 

Primary Key

해당 컬럼은 Not Null이고 Unique이다. 항상 값이 있어야 하고 유일해야 한다.

postgres=# CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric);

 

유일한 식별자를 필요로 하는 경우, 특히 해당 테이블의 레코드를 유일하게 구분할 때 사용한다.

 

Foreign Key

외부 테이블에 있는 Primary Key 컬럼의 값을 사용한다.

해당 컬럼에 존재하지 않는 값은 오류가 발생한다.

postgres=# CREATE TABLE products (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES products2 (c1, c2));

 

외부 테이블과 연결 관계를 생성하는데 사용한다.

 

 

Table 수정

생성한 테이블의 정보를 수정하기 위해서는 ALTER TABLE 명령을 사용한다.

 

대표적인 경우만 소개하고, 나머지 내용들은 아래 페이지를 참고한다.

ALTER TABLE

 

아래 명령으로 변경한다.

# 컬럼과 제약 조건 추가 / 제거
ALTER TABLE [ IF EXISTS ] table_name 
    [ ADD | DROP ] [ COLUMN | CONSTRAINT ] 
    name [ data_type ]
    [ COLLATE collation ] [ column_constraint [ ... ] ];

# 컬럼과 제약 조건 이름 변경
ALTER TABLE [ IF EXISTS ] table_name
    RENAME [ COLUMN | CONSTRAINT ] name TO newname;

# 테이블을 지정한 Tablespace나 Schema에 할당
ALTER TABLE table_name 
    SET [ TABLESPACE | SCHEMA ] name;

 

대표적인 예제는 다음과 같다.

# 테이블에 컬럼 추가
postgres=# ALTER TABLE employee
    ADD COLUMN address varchar(50);

# 테이블의 컬럼명 변경
postgres=# ALTER TABLE employee
    RENAME COLUMN address TO city;

# 테이블의 제약 조건 삭제
postgres=# ALTER TABLE employee
    DROP CONSTRAINT chk_birth;

# 테이블의 특정 컬럼 데이터 타입 변경
postgres=# ALTER TABLE employee
    ALTER COLUMN city TYPE varchar(50),
    ALTER COLUMN name TYPE varchar(30);

# 테이블의 Schema 변경
postgres=# ALTER TABLE emplyee
    SET SCHEMA company;

 

 

Table  삭제

테이블을 삭제하는 것은 Schema 삭제 명령과 비슷하다.

 

다음 명령을 사용한다.

DROP TABLE [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]

 

테이블을 소유한 계정과 SUPERUSER만 가능하다.

 

대표적인 option은 다음과 같다.

Option	Default	Description
IF EXISTS		Table이 존재하는 경우만 삭제 (없으면 메시지만 출력)
CASCADE | RESTRICT	RESTRICT	삭제할 Table에 연결 된 객체에 대한 강제 삭제 여부

 

기본 값은 RESTRICT이므로 View나 Foriegn Key와 같이 연결 된 객체가 있으면 오류가 발생한다.

CASCADE 옵션을 지정하면 모두 삭제되므로, 사용에 주의해야 한다.