Skip to main content

ALTER TABLE

Redefines a table.

Syntax​

ALTER TABLE [IF EXISTS] [ONLY] <name> [ * ]
    <action> [, ... ]

ALTER TABLE [IF EXISTS] [ONLY] <name> [ * ]
    RENAME [COLUMN] <column_name> TO <new_column_name>

ALTER TABLE [IF EXISTS] [ ONLY ] <name> [ * ]
    RENAME CONSTRAINT <constraint_name> TO <new_constraint_name>

ALTER TABLE [IF EXISTS] <name> 
    RENAME TO <new_name>

where <action> can be:
  ADD [COLUMN] [IF NOT EXISTS] <column_name> <data_type> [<column_constraint> [ ... ]]
      [ ENCODING ( <storage_directive> [,...] ) ]
  DROP [COLUMN] [IF EXISTS] <column_name> [RESTRICT | CASCADE]
  ALTER [COLUMN] <column_name> [ SET DATA ] TYPE <data_type> [USING <expression>]
  ALTER [COLUMN] <column_name> SET DEFAULT <expression>
  ALTER [COLUMN] <column_name> DROP DEFAULT
  ALTER [COLUMN] <column_name> { SET | DROP } NOT NULL
  ALTER [COLUMN] <column_name> SET ( <attribute_option> = <value> [, ... ] )
  ALTER [COLUMN] <column_name> RESET ( <attribute_option> [, ... ] )
  ALTER [COLUMN] <column_name> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
  ALTER [COLUMN] <column_name> SET ENCODING ( storage_directive> [, ...] )
  DROP CLUSTERING KEY

Description​

You can run ALTER TABLE to redefine an existing table. An ACCESS EXCLUSIVE lock is acquired unless explicitly noted. When multiple subcommands are provided, Relyt acquires the strictest lock required by any subcommand.

ALTER TABLE has several variants, including:

  • ALTER TABLE ... RENAME [COLUMN] <column_name> TO: renames a column in a given table.
  • ALTER TABLE ... RENAME CONSTRAINT ... TO: renames a constraint in a given table.
  • ALTER TABLE ... RENAME TO renames a given table.

Parameters​

  • IF EXISTS

    If you include IF EXISTS in your command and the specified view does not exist, no error will be reported. A notice will be issued instead.

  • ONLY

    If you include ONLY in your command, only the table will be altered. Otherwise, the table and its descendant tables (if any) will be updated. You can optionally specify * after the table name to explicitly indicate that descendant tables are included.

  • <name>

    The name of the table. You can specify the name with the schema qualification.

  • <action>

    The action to perform. Supported actions include:

    • ADD [COLUMN]: adds a column.

    To perform this action, you must specify the <column_name> and <data_type>, where <column_name> indicates the name of the column that you want to add and <data_type> indicates the data type of the column.

    • DROP [COLUMN]: drops a column.

    To perform this action, you must specify the <column_name> which indicates the name of the column that you want to delete. If any database objects depend on the column, such as a view that references the column, the CASCADE keyword is required to drop the column.

    • ALTER [COLUMN] ... [SET DATA] TYPE: changes the data type of a column.

    The optional USING clause specifies how to compute the new column value from the old. If omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

    • ALTER [COLUMN] ... {SET | DROP} DEFAULT: sets or removes the default value for a column. Default values apply only in subsequent INSERT or UPDATE commands. They do not cause rows already in the table to change.

    • ALTER [COLUMN] ... {SET | DROP} NOT NULL: changes whether a column is marked to allow null values or to reject null values.

    SET NOT NULL may only be applied to a column provided none of the records in the table contain a NULL value for the column.

    If this table is a partition, you cannot DROP NOT NULL on a column if it is marked NOT NULL in the parent table. To drop the NOT NULL constraint from all the partitions, perform DROP NOT NULL on the parent table. Even if there is no NOT NULL constraint on the parent, such a constraint can still be added to individual partitions, if desired; that is, the children can disallow nulls even if the parent allows them, but not the other way around.

    • ALTER [COLUMN] ... SET STATISTICS: sets the per-column statistics-gathering target for subsequent ANALYZE operations. The target can be set in the range 0 to 10000, or set to -1 to revert to using the system default statistics target default_statistics_target. When set to 0, no statistics are collected.

    SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.

    • ALTER [COLUMN] ... SET ENCODING: sets column encoding options for Relyt tables.

    • ADD <table_constraint>: adds a new constraint to a table using the same syntax as CREATE TABLE.

    Most forms of ADD <table_constraint> require an ACCESS EXCLUSIVE lock.

    • DROP CLUSTERING KEY: drops the clustering key.

  • CASCADE | RESTRICT

    Used with the DROP COLUMN action to specify whether to drop the column if the column has dependent database objects. CASCADE drops all dependent objects along with the column. RESTRICT rejects the entire drop operation. RESTRICT is the default.

  • <new_name>

    The new name of the table. This parameter is required when you use ALTER TABLE ... RENAME TO.

  • <column_name>

    The name of the column that you want to modify.

  • <new_column_name>

    The new name of the column. This parameter is required when you use ALTER TABLE ... RENAME [COLUMN] ... TO.

  • <constraint_name>

    The name of the constraint.

  • <new_constraint_name>

    The new name of the constraint. This parameter is required when you use ALTER TABLE ... RENAME CONSTRAINT ... TO.

  • <data_type>

    The data type of the column.

  • <table_constraint>

    The new table constraint for the table.

Usage notes​

The keyword COLUMN is noise and can be omitted.

When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column’s default value, or NULL if no DEFAULT clause is specified. Adding a column with a non-null default or changing the type of an existing column will require the entire table and indexes to be rewritten.

Adding a NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint, but does not require a table rewrite.

Relyt provides the option to specify multiple changes in a single ALTER TABLE so that multiple table scans or rewrites can be combined into a single pass over the table.

The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. If you drop the system oid column, however, the table is rewritten immediately.

To force immediate reclamation of space occupied by a dropped column, you can run one of the forms of ALTER TABLE that performs a rewrite of the whole table. This results in reconstructing each row with the dropped column replaced by a null value.

This table lists the ALTER TABLE operations that require a table rewrite when performed on tables defined with the specified type of table storage.

caution

The forms of ALTER TABLE that perform a table rewrite are not MVCC-safe. After a table rewrite, the table will appear empty to concurrent transactions if they are using a snapshot taken before the rewrite occurred. See MVCC Caveats for more details.

If a table has any descendant tables, it is not permitted to add, rename, or change the type of a column in the parent table without doing the same to the descendants. This ensures that the descendants always have columns matching the parent. Also, because selecting from the parent also selects from its descendants, a constraint on the parent cannot be marked valid unless it is also marked valid for those descendants. In all of these cases, ALTER TABLE ONLY will be rejected.

A recursive DROP COLUMN operation will remove a descendant table’s column only if the descendant does not inherit that column from any other parents and never had an independent definition of the column. A nonrecursive DROP COLUMN (ALTER TABLE ONLY ... DROP COLUMN) never removes any descendant columns, but instead marks them as independently defined rather than inherited.

Examples​

Rename table new to freshmen:

ALTER TABLE new RENAME TO freshmen;

Add a column named year to table freshmen:

ALTER TABLE freshmen
ADD COLUMN year integer;

Drop column c1 from table freshmen:

ALTER TABLE freshmen
DROP COLUMN c1

Change the data type of column age in table freshmen to smallint:

ALTER TABLE freshmen
ALTER COLUMN age TYPE smallint;

SQL standard compatibility​

The forms ADD, DROP [COLUMN], SET DEFAULT, and [SET DATA] TYPE (without USING) conform with the SQL standard. The other forms are Relyt extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER TABLE command is an extension.

ALTER TABLE ... DROP COLUMN can be used to drop the only column of a table, leaving a zero-column table. This is an extension of SQL, which disallows zero-column tables.