::: IT인터넷 :::

PostgreSQL의 Table 관리

곰탱이푸우 2022. 5. 12. 08:20
오픈소스 데이터베이스인 PostgreSQL의 Table을 관리하는 방법에 대해 정리한다.
 
PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.
PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.
GitHub과 DockerHub 사이트는 다음과 같다.
 
PostgreSQL은 다음과 같은 구조를 가지고 있다.
각 기능은 다음과 같다.
  • Users/Groups - 사용자 정보 관리
  • Databases - 실제 레코드가 저장되는 Database 관리
  • Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
  • Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
  • Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
  • Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간

Table 관리

PostgreSQL의 데이터가 실제로 저장되는 공간이다. 
 
앞서 다룬 PostgreSQL 구조의 Tables 기능에 해당하며, Row와 Column으로 구성 된 공간이다.
 

PostgreSQL 데이터 타입

PostgreSQL에서 제공하는 데이터 타입은 아래 문서를 참고한다
주로 사용하거나 사용할 것으로 예상되는 데이터 타입은 아래와 같다.
사용 환경에 따라 다르기 때문에 주관적으로 정리한 것임을 밝혀둔다.
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 항목에 해당한다.
문자의 정렬 규칙에 관한 것으로, 자세한 내용은 아래 페이지를 참고한다.
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 [ 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 옵션을 지정하면 모두 삭제되므로, 사용에 주의해야 한다.
 
 

'::: IT인터넷 :::' 카테고리의 다른 글

PostgreSQL의 JOIN 사용하기  (0) 2022.05.19
PostgreSQL의 SQL 기본 사용법  (0) 2022.05.16
PostgreSQL의 Schema 관리  (0) 2022.05.09
PostgreSQL의 Database 관리  (0) 2022.05.05
PostgreSQL의 사용자 관리  (101) 2022.05.02