PostgreSQL | ALTER 제약조건 및 테이블 이름 변경

Tags:

• PostgreSQL
• Database

테이블명 변경

문법

ALTER TABLE {테이블명} RENAME TO {새 테이블명};

예제

ALTER TABLE test RENAME TO test_bak;

제약조건 변경

문법

ALTER INDEX {제약조건명} RENAME TO {새 제약조건명};

예제

ALTER INDEX pk_test RENAME TO pk_test_bak;

컬럼 추가

ALTER TABLE books ADD publication date;

컬럼명 변경

ALTER TABLE books RENAME COLUMN in_stock TO is_in_stock;

컬럼 삭제

ALTER TABLE books DROP COLUMN IF EXISTS publication;

컬럼 default값 추가

ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval('books_idx');

컬럼 default값 제거

ALTER TABLE books ALTER id DROP DEFAULT;

컬럼 NOT NULL 세팅

ALTER TABLE books ALTER COLUMN id SET NOT NULL;

컬럼 NOT NULL 제거

ALTER TABLE books ALTER COLUMN id DROP NOT NULL;

컬럼 데이터 타입 변경

ALTER TABLE books ALTER COLUMN publication TYPE text;

constraint 추가

ALTER TABLE editions ADD CONSTRAINT foreign_book FOREIGN KEY (book_id) REFERENCES books (id);
ALTER TABLE editions ADD CONSTRAINT hard_or_paper_back CHECK (type='p' OR type='h');

constraint 변경 (변경은 없고 DROP -> ADD 해야 함)

ALTER TABLE editions DROP CONSTRAINT editions_type_check;
ALTER TABLE editions ADD CONSTRAINT editions_type_check CHECK (type=ANY);

테이블 소유자 변경

ALTER TABLE employees OWNER TO corwin;

테이블 재구축

테이블 재구축

방법1)

CREATE TABLE new_books (id, title, author_id, subject_id) AS SELECT id, title, author_id, subject_id FROM books;  
ALTER TABLE books RENAME TO old_books;  
ALTER TABLE books RENAME TO books;  
DROP TABLE old_books;

방법2)

CREATE TABLE new_books (id integer UNIQUE, title text NOT NULL, author_id integer, subject_id integer, CONSTRAINT books_id_pkey PRIMARY KEY);  
INSERT INTO new_books SELECT id, title, author_id, subject_id FROM books;  
ALTER TABLE books RENAME TO old_books;  
ALTER TABLE new_books RENAME TO books;  
DROP TABLE old_books;