Alter table name. Modify column name

PostreSQL tutorial – Alter Table

Alter postgresql table

Alter table. Modify column name or column data type. Drop column. Disable constraint or trigger

Syntax

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name RENAME [ COLUMN ] 
column_name TO new_column_name;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name RENAME CONSTRAINT 
constraint_name TO new_constraint_name;

ALTER TABLE [ IF EXISTS ] 
table_name RENAME TO new_table_name;

ALTER TABLE [ IF EXISTS ] 
table_name SET SCHEMA new_schema;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name ADD [ COLUMN ] column_name data_type;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name DROP [ COLUMN ] [ IF EXISTS ] 
column_name [ RESTRICT | CASCADE ];

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name ALTER [ COLUMN ] 
column_name [ SET DATA ] TYPE data_type;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name ALTER [ COLUMN ] 
column_name SET DEFAULT expression;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name ALTER [ COLUMN ] 
column_name DROP DEFAULT;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name ALTER [ COLUMN ] 
column_name { SET | DROP } NOT NULL ;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name ADD table_constraint [ NOT VALID ];

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name VALIDATE CONSTRAINT constraint_name;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name DROP CONSTRAINT [ IF EXISTS ] 
constraint_name [ RESTRICT | CASCADE ];

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name DISABLE TRIGGER 
[ trigger_name | ALL | USER ];

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name ENABLE TRIGGER 
[ trigger_name | ALL | USER ];

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name OWNER TO new_owner;

ALTER TABLE [ IF EXISTS ] [ ONLY ] 
table_name SET TABLESPACE new_tablespace;

Example

ALTER TABLE customers RENAME TO buyers;

ALTER TABLE customers ADD CONSTRAINT 
ch_first_name CHECK (char_length(first_name) <= 20);

ALTER TABLE customers DROP CONSTRAINT ch_first_name;

Examples

Add primary key to existing table

Add foreign key constraint to table

Add unique key constraint to table

Add column to existing table

Add multiple columns to existing table

Drop column from existing table

Drop multiple columns from table

Rename column table

Rename name of existing table

Add check constraint to a table

Remove check constraint from table

Create table |
Drop table