[PostgreSQL] DDL - Table 조회/생성/변경/삭제

2020/09/08 - [STUDY/SQL] - [PostgreSQL] DDL - DB 조회/생성/변경/삭제에 이어,
기본 PostgreSQL DDL 문법(테이블 조회/생성/변경/삭제)에 대해 알아볼게요!

 

pgAdmin에서 쿼리 작성 툴은 아래처럼 마우스 오른쪽 버튼을 클릭해 'Query Tool'로 실행할 수 있습니다.

 

 

---

Table 목록 조회

SELECT * FROM PG_STAT_USER_TABLES;

위의 쿼리를 입력하면, 사용자가 만든 테이블 목록을 조회할 수 있습니다.

[결과]

 

---

Table 생성(기본키, 외래키 설정 포함)

CREATE TABLE {{TABLE_NAME}}
(
    {{COL_NAME}} {{DATA_TYPE}} {{OPTION}},
    ....
);

{{TABLE_NAME}} : 생성할 테이블의 이름
{{COL_NAME}} : 컬럼 이름
{{DATA_TYPE}} : 컬럼의 데이터타입
{{OPTION}} : 옵션[생략가능], NULL, NOT NULL, DEFAULT 등

*자주 사용하는 데이터타입 목록*

dataType 명	dataType 설명
Serial	자동증가 4bytes 정수형, 보통 primary idx로 많이 사용 AUTO INCREMENT 속성이 포함되어 있다.
Integer	signed four-byte 정수형
Boolean	Boolean연산 (true/false)
varchar(n)	가변길이 문자형
varchar(n)[]	가변길이 문자형 배열
Text	가변길이 문자형, 글자 수가 많은 곳에 사용.
Date	달력 날짜 (year, month, day)

예제) 'USER_TB'라는 이름의 테이블을 생성하자. 컬럼은 Idx(기본키), email, password, name, friends로 한다.

CREATE TABLE USER_TB
(
    Idx SERIAL NOT NULL, 
    email VARCHAR(45) NULL , 
    password VARCHAR(100) NULL,
    name VARCHAR(45) NULL,
    friends VARCHAR(45)[] NULL,
    PRIMARY KEY (Idx)
);

[결과]

외래키 설정 방법

외래키 설정 예제를 위해, 게시물 테이블을 하나 만들어보겠습니다.

CREATE TABLE POST_TB
(
    Idx SERIAL NOT NULL, 
    title VARCHAR(45) NULL , 
    content TEXT NULL,
    author Integer NOT NULL,
    createdAt Date NULL, 
    PRIMARY KEY (Idx)
);

 

게시물 테이블에는 작성자(author)가 필요합니다. 이 작성자는 유저 테이블의 1명이 될 것입니다.
그렇기 때문에, author 컬럼에는 유저 테이블의 Idx 컬럼을 이용할 것입니다. 
=> POST_TB의 외래키(AUTHOR)는 USER_TB의 Idx와 연결

* 외래키가 의존하려는 컬럼은 데이터타입이 같아야하며, UNIQUE 또는 PRIMARY KEY로 설정되어있어야합니다! 꼭 주의해주세요!

[ALTER문을 이용해 외래키 설정] -> 제약조건은 CASCADE로 설정

ALTER TABLE POST_TB 
    ADD CONSTRAINT FK_POST_TB_AUTHOR FOREIGN KEY (author)
        REFERENCES USER_TB(Idx) ON DELETE CASCADE ON UPDATE CASCADE;

 

 

 

---

Table 변경

* 테이블 이름 변경

ALTER TABLE {{TB_NAME}} RENAME TO {{NEW_TB_NAME}};

{{TB_NAME}} : 원래 테이블 이름
{{NEW_TB_NAME}} : 테이블의 새로운 이름

예제) MJ_TB의 테이블 명을 NEW_MJ_TB로 변경해라.

ALTER TABLE MJ_TB RENAME TO NEW_MJ_TB;

 

* 컬럼명 변경

ALTER TABLE {{TB_NAME}} RENAME COLUMN {{COL_NAME}} TO {{NEW_COL_NAME}};

{{DB_NAME}} : 컬럼이 속해져있는 테이블 이름
{{COL_NAME}} : 원래 컬럼 이름
{{NEW_COL_NAME}} : 새로운 컬럼 이름

예제) USER_TB의 EMAIL 컬럼을 ID 라는 이름을 가진 컬럼으로 변경해라.

ALTER TABLE USER_TB RENAME COLUMN EMAIL TO ID;

 

* 컬럼 타입 변경

ALTER TABLE {{TB_NAME}} ALTER COLUMN {{COL_NAME}} TYPE {{NEW_TYPE}};

{{TB_NAME}} : 컬럼이 속해져있는 테이블 이름
{{COL_NAME}} : 자료형을 바꿀 컬럼 이름
{{NEW_TYPE}} : 새로운 컬럼 이름

예제) POST_TB의 TITLE 컬럼의 자료형을 'TEXT'로 바꾸어라.

ALTER TABLE POST_TB ALTER COLUMN TITLE TYPE TEXT;

 

---

Table 삭제

* table 완전 삭제(구조와 데이터 모두 삭제)

DROP TABLE {{TB_NAME}};

{{TB_NAME}} : 삭제할 TB의 이름

예제) POST_TB 테이블을 삭제하라.

DROP TABLE POST_TB;

 

* table 데이터만 삭제(구조는 남겨두고 데이터만 모두 삭제)

TRUNCATE TABLE {{TB_NAME}};

{{TB_NAME}} : 삭제할 테이블의 이름

예제) POST_TB의 구조는 살려두고, 데이터만 모두 삭제하라.

TRUNCATE TABLE POST_TB;

 

 

 

 

 

 

 

 

---

참고) www.gurubee.net/lecture/2947

데이터 타입