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;