PostgreSQL - ALTER

ALTER

-- 컬럼 추가
ALTER TABLE books ADD publication date;

-- 컬럼 삭제
ALTER TABLE books DROP 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 RENAME TO literature;

-- 컬럼명 변경
ALTER TABLE books RENAME COLUMN in_stock TO is_in_stock;

-- 컬럼 데이터 타입 변경
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(ARRAY[0::smallint, 1::smallint, 2::smallint])); 

-- 테이블 소유자 변경
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(id));
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;