Alter Table Definition

The ALTER TABLE statement can be used to change the TABLE properties, it requires authorization to do so. Addition of a column follows the same syntax and functionality as the CREATE TABLE statement. If the table is used in a foreign key relationship the actions to be take can be further specified as RESTRICT to forbid it if there are external references to the column values. The CASCADE nullifies all references to the values being removed.

alter_table_statement:
     ALTER TABLE [IF EXISTS] qname ADD   [COLUMN] table_element
   | ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET [NOT] NULL
   | ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET DEFAULT value
   | ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name DROP DEFAULT
   | ALTER TABLE [IF EXISTS] qname ALTER [COLUMN] column_name SET STORAGE {string | NULL}
   | ALTER TABLE [IF EXISTS] qname DROP  [COLUMN] column_name [RESTRICT | CASCADE]
   | ALTER TABLE [IF EXISTS] qname ADD [CONSTRAINT constraint_name] table_constraint_type
   | ALTER TABLE [IF EXISTS] qname DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]
   | ALTER TABLE [IF EXISTS] qname RENAME [COLUMN] column_name TO new_column_name
   | ALTER TABLE [IF EXISTS] qname RENAME TO new_table_name
   | ALTER TABLE [IF EXISTS] qname SET SCHEMA schema_name
   | ALTER TABLE [IF EXISTS] qname SET READ ONLY
   | ALTER TABLE [IF EXISTS] qname SET INSERT ONLY
   | ALTER TABLE [IF EXISTS] qname SET READ WRITE
   | alter_merge_tables

qname:
   [ schema_name '.' ] table_name

table_element:
     column_name   data_type  [ column_option [ ',' ... ] ]
   | column_name { SERIAL | BIGSERIAL }
   | column_name   WITH OPTIONS '(' column_option [ ',' ... ] ')'

column_option:
     DEFAULT default_value_expr
   | [ CONSTRAINT constraint_name ] column_constraint_type
   | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ '(' sequence_parameters ')' ]
   | AUTO_INCREMENT

column_constraint_type:
     NOT NULL
   | NULL
   | CHECK '(' search_condition ')'
   | UNIQUE
   | UNIQUE NULLS DISTINCT
   | UNIQUE NULLS NOT DISTINCT
   | PRIMARY KEY
   | REFERENCES qname [ '(' column_name [ ',' ... ')' ] ]
         [ match_option ]  [ ref_actions ]

table_constraint_type:
     CHECK '(' search_condition ')'
   | UNIQUE '(' column_name [ ',' ... ] ')'
   | UNIQUE NULLS DISTINCT '(' column_name [ ',' ... ] ')'
   | UNIQUE NULLS NOT DISTINCT '(' column_name [ ',' ... ] ')'
   | PRIMARY KEY '(' column_name [ ',' ... ] ')'
   | FOREIGN KEY '(' column_name [ ',' ... ] ')'
       REFERENCES qname [ '(' column_name [ ',' ... ] ')' ]
         [ match_option ]  [ ref_actions ]

match_option:
     MATCH [ FULL | PARTIAL | SIMPLE ]

ref_actions:
     on_update_action
   | on_delete_action
   | on_update_action  on_delete_action
   | on_delete_action  on_update_action

on_update_action:
     ON UPDATE { RESTRICT | CASCADE | NO ACTION | SET NULL | SET DEFAULT }

on_delete_action:
     ON DELETE { RESTRICT | CASCADE | NO ACTION | SET NULL | SET DEFAULT }

See Table Elements for a description of column options and constraints.

Change of the data type of a column is not supported. Instead use command sequence:

ALTER TABLE tbl ADD COLUMN new_column _new_data_type_;
UPDATE tbl  SET new_column = CONVERT(old_column, _new_data_type_);
ALTER TABLE tbl DROP COLUMN old_column RESTRICT;
ALTER TABLE tbl RENAME COLUMN new_column TO old_column;

Note: You cannot drop a column if it is referenced (e.g. from a view, an index, a merge table, a trigger, a foreign key constraint, a function or procedure or another db object except comments). Use option CASCADE to specify to also drop those referencing objects.

You can change the name of a column, the name of the table itself or the schema the table is belonging to, provided there are no objects which depend on the table or column name. To move a table to a different schema use command: ALTER TABLE ... SET SCHEMA ...

To add a NOT NULL column constraint use:
ALTER TABLE sch.tbl ALTER COLUMN column_name SET NOT NULL.
To remove a NOT NULL column constraint use:
ALTER TABLE sch.tbl ALTER COLUMN column_name SET NULL.

Only one 'PRIMARY KEY' constraint can be defined per table. When a primary key constraint is added, all the primary key columns will become NOT NULLable implicitly.

If no constraint_name is specified a constraint_name will be composed internally from the table name, column name(s) and constraint type.

An individual table can be protected using the ALTER TABLE qname SET READ ONLY and ALTER TABLE qname SET INSERT ONLY mode. All attempts to update are flagged as a SQL error.
The reverse operation is ALTER TABLE qname SET READ WRITE, which makes the table accessible for all update operations.

Alter merge table

For merge tables The ALTER TABLE statement has been extended with ADD TABLE and DROP TABLE options to attach and detach a partition table to/from a MERGE TABLE and with SET TABLE p AS PARTITION to change the partitioning specification of the partition table.

alter_merge_tables:
     ALTER TABLE [IF EXISTS] qname ADD TABLE qname [ AS PARTITION partition_spec ]
   | ALTER TABLE [IF EXISTS] qname SET TABLE qname AS PARTITION partition_spec
   | ALTER TABLE [IF EXISTS] qname DROP TABLE qname [ RESTRICT | CASCADE ]

See for more info merge-tables.

Examples

ALTER TABLE "tblnm" ADD PRIMARY KEY ("C1_id");

ALTER TABLE if exists "schnm"."tblnm" ADD CONSTRAINT "tblnm_uc" UNIQUE ("name", "desc");

ALTER TABLE "sch4"."tblnm" ADD CONSTRAINT "tblnm_fk1"
      FOREIGN KEY ("f_id", "f_seq")
      REFERENCES "sch2"."fun" ("id", "seq")
      ON UPDATE RESTRICT  ON DELETE CASCADE;