PostgreSQL ALTER TABLE - Alter existing tables

In PostgreSQL, ALTER TABLE statements are used to modify an existing table, including: rename tables, add columns, drop columns, modify column, add constraint, delete constraint, etc.

PostgreSQL `ALTER TABLE` Syntax

Since the ALTER TABLE statement can modify all aspects of a table, so its syntax is very complex.

This is the basic syntax of this ALTER TABLE statement:

ALTER TABLE [IF EXISTS] table_name
   [alter_action options]
   [, ...];

Explanation:

The table_name is the name of the table to modify. IF EXISTS is optional,
The alter_action is a alter action, which mainly includes the following keywords:
- The ADD keyword can be used to add columns or constraints.
- The DROP keyword can be used to drop columns or constraints.
- The ALTER keyword can be used to modify existing columns or constraints.
- The RENAME keyword can be used to rename tables, columns, or constraints.
- The SET keyword can be used to modify the schema or tablespace of a table.
- The ENABLE keyword can be used to enable triggers, rules, and row security policies.
- The DISABLE keyword can be used to disable triggers, rules, and row security policies.

Rename tables

To rename a table in PostgreSQL, use the following syntax:

ALTER TABLE table_name
  RENAME TO new_name

Modify table schema

To modify the schema of a table in PostgreSQL, use the following syntax:

ALTER TABLE table_name
  SET SCHEMA new_schema

Modify tablespace

To modify a table’s tablespace, use the following syntax:

ALTER TABLE table_name
  SET TABLESPACE new_tablespace

Add columns

To add a column to a table, use the following syntax:

ALTER TABLE table_name
  ADD [COLUMN] [IF NOT EXISTS] column_name data_type [ column_constraint [ ... ] ]

Drop columns

To drop a column from a table, use the following syntax:

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

Rename columns

To rename a column, use the following syntax:

ALTER TABLE table_name
  RENAME [ COLUMN ] column_name TO new_column_name

Modify the data type of a column

To modify the data type of a column, use the following syntax:

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type

Set default values for columns

To modify the default value of a column, use the following syntax:

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name SET DEFAULT expression

Remove column defaults

To remove the default value for a column, use the following syntax:

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP DEFAULT

Add `NOT NULL` constraint for a column

To add the NOT NULL constraint for a column, use the following syntax:

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name SET NOT NULL

Drop `NOT NULL` constraint for a column

To remove NOT NULL constraints, use the following syntax:

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP NOT NULL

Identity column

To modify a column to an identity column, use the following syntax:

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT }
      AS IDENTITY [ ( sequence_options ) ]

To modify an identity column to a normal column, use the following syntax:

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]

Add constraints to a table

To add a constraint to a table, use the following syntax:

ALTER TABLE table_name
  ADD [ CONSTRAINT constraint_name ]
      { CHECK ( expression ) [ NO INHERIT ] |
        UNIQUE ( column_name [, ... ] ) index_parameters |
        PRIMARY KEY ( column_name [, ... ] ) index_parameters |
        EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
        FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
          [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
      [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Drop constraints from a table

To remove a constraint from a table, use the following syntax:

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

Rename constraints

To rename a constraint, use the following syntax:

ALTER TABLE table_name
  RENAME CONSTRAINT constraint_name TO new_constraint_name

Conclusion

This article illustrates the usages of the PostgreSQL ALTER TABLE statement to modify the definition of an existing table.

PostgreSQL ALTER TABLE Syntax