Skip to main content

Alter statement

The ALTER 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_statement:

         ALTER TABLE [IF EXISTS] qname ADD [COLUMN] column_name { data_type [ column_option ... ] | SERIAL | BIGSERIAL }
      |  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 ident ]  table_constraint_type
      |  ALTER TABLE [IF EXISTS] qname DROP CONSTRAINT ident [RESTRICT | CASCADE]

As of Apr2019 release (11.33.3) you can also 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.

alter_rename_statement:

         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

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

alter_access_statement:

         ALTER TABLE [IF EXISTS] qname SET READ ONLY
      |  ALTER TABLE [IF EXISTS] qname SET INSERT ONLY
      |  ALTER TABLE [IF EXISTS] qname SET READ WRITE

The ALTER statement has been extended for merge tables 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_statement_for_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 ]