ALTER TABLE in PostgreSQL Database
PostgreSQL supports the ALTER TABLE statement to modify the structure of the existing tables.
ALTER table can be used to
- Add column
- Rename column
- Modify column datatype
- Set or Remove default value to column
- Add or Drop constraint to column
- Drop column in the table
The general syntax of the ALTER TABLE statements:
ALTER TABLE action
Where action can be:
ADD [ COLUMN ]
DROP COLUMN
ALTER COLUMN TYPE
ALTER COLUMN SET DEFAULT
ALTER COLUMN DROP DEFAULT
ALTER COLUMN SET attribute_option
ALTER COLUMN RESET
ADD table_constraint
VALIDATE CONSTRAINT
DISABLE TRIGGER
ENABLE TRIGGER
ENABLE REPLICA TRIGGER trigger_name
ENABLE ALWAYS TRIGGER trigger_name
DISABLE RULE rewrite_rule_name
ENABLE RULE rewrite_rule_name
ENABLE REPLICA RULE rewrite_rule_name
ENABLE ALWAYS RULE rewrite_rule_name
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITH OIDS
SET WITHOUT OIDS
SET ( storage_parameter = value [, ... ] )
RESET ( storage_parameter [, ... ] )
INHERIT parent_table
NO INHERIT parent_table
OF type_name
NOT OF
OWNER TO new_owner
SET TABLESPACE new_tablespace
Consider the following employee table:
Let's see the overview of ALTER TABLE statements:
To add a column of type varchar to a table:
ALTER TABLE employee ADD COLUMN address varchar(50);
To drop a column from a table:
ALTER TABLE employee DROP COLUMN employee CASCADE;
To change the types of two existing columns in one operation:
ALTER TABLE employee
ALTER COLUMN first_name TYPE varchar(80),
ALTER COLUMN last_name TYPE varchar(80);
To rename an existing column:
ALTER TABLE employee RENAME COLUMN birthdate TO BoD;
To rename an existing table:
ALTER TABLE employee RENAME TO employee_info;
To add a NOT NULL constraint to a column:
ALTER TABLE employee ALTER COLUMN gender SET NOT NULL;
To remove a NOT NULL constraint from a column:
ALTER TABLE employee ALTER COLUMN salary DROP NOT NULL;
To add a check constraint to a table and all its children:
ALTER TABLE employee ADD CONSTRAINT first_name CHECK (char_length(first_name) > 1);
To remove a check constraint from a table and all its children:
ALTER TABLE employee DROP CONSTRAINT first_name;
ALTER TABLE ONLY employee DROP CONSTRAINT first_name; -- remove check constraint from one table only
To add a primary key in a table:
ALTER TABLE employee ADD PRIMARY KEY (id);
To move a table to a different tablespace:
ALTER TABLE employee SET TABLESPACE emptablespace;
To move a table to a different schema:
ALTER TABLE myschema.employee SET SCHEMA yourschema;
Learn about all the ALTER TABLE statements in next chapters.