PostgreSQL | ALTER Constraints and Table Names

Tags:

• PostgreSQL
• Database

Rename a table

Syntax

ALTER TABLE {table name} RENAME TO {new table name};

Example

ALTER TABLE test RENAME TO test_bak;

Rename a constraint

Syntax

ALTER INDEX {constraint name} RENAME TO {new constraint name};

Example

ALTER INDEX pk_test RENAME TO pk_test_bak;

Add a column

ALTER TABLE books ADD publication date;

Rename a column

ALTER TABLE books RENAME COLUMN in_stock TO is_in_stock;

Delete a column

ALTER TABLE books DROP COLUMN IF EXISTS publication;

Add a column default value

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

Remove a column default value

ALTER TABLE books ALTER id DROP DEFAULT;

Set column NOT NULL

ALTER TABLE books ALTER COLUMN id SET NOT NULL;

Remove column NOT NULL

ALTER TABLE books ALTER COLUMN id DROP NOT NULL;

Change a column data type

ALTER TABLE books ALTER COLUMN publication TYPE text;

Add constraints

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');

Change constraints (drop and add, without direct modification)

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

Change table owner

ALTER TABLE employees OWNER TO corwin;

Rebuild a table

Rebuild a table.

Method 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;

Method 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;