PolarDB:ALTER TABLE

Overview

You can use the ALTER TABLE statement to change the definition of an existing table. The following sections describe the clauses that you can use in the ALTER TABLE statement. Take note that the lock levels required by the clauses may be different. An ACCESS EXCLUSIVE lock is applied unless explicitly specified. If you use multiple clauses in an ALTER TABLE statement, the strictest lock required by the clauses is applied.

ADD COLUMN [ IF NOT EXISTS ]: adds a new column to the table. This clause uses the same syntax as the CREATE TABLE statement. If you specify the IF NOT EXISTS option in the statement and a column with the specified name already exists, no error is thrown.

DROP COLUMN [ IF EXISTS ]: removes a column from the table. Indexes and table constraints that are dependent on or associated with the removed column are automatically removed. Multivariate statistics that reference the removed column is also removed if the removal of the column causes the statistics to contain data for only a single column. You must add the CASCADE keyword if other objects outside the table depend on the column, such as foreign key references or views. If you specify the IF EXISTS option in the statement and the specified column does not exist, no error is thrown, but a message notification appears.

SET DATA TYPE: changes the data type of a column in the table. Indexes and simple table constraints that are dependent on or associated with the column are automatically converted to use the new column data type by reparsing the originally supplied expression. The optional COLLATE clause specifies a collation for the new column data type. If you do not specify a collation, the default collation is used for the new column data type. The USING clause specifies how to convert the column data from the current data type into the new data type. If you do not specify a USING clause, the default assignment cast rules that are used to convert data from the current data type into a new data type are applied. You must use a USING clause if no implicit or assignment cast rules are available to convert data from the current data type into a new data type.

SET/DROP DEFAULT: configures or removes the default value of a column. To remove the default value of a column, you can use the DROP DEFAULT clause or set the default value to NULL. The new default value takes effect only in subsequent INSERT or UPDATE statements, and does not cause existing rows in the table to change.

SET/DROP NOT NULL: adds or removes a NOT NULL constraint from a column.

You can perform the SET NOT NULL operation on a column only if the column does not have a NULL value in the rows. In most cases, this is checked against the entire table when you execute the ALTER TABLE statement. However, if a valid CHECK constraint exists on the table that logically guarantees that none of the values in the column can be NULL, the full table scan is skipped.

If the table is a partition, you cannot perform the DROP NOT NULL operation on a column if the NOT NULL constraint is added to the column in the parent table. To remove the NOT NULL constraint from the partitions, perform the DROP NOT NULL operation on the parent table. You can add a NOT NULL constraint to individual partitions even if the constraint does not exist on the parent table. The child tables can disallow null values even if the parent table allows null values, but not the other way around.

DROP EXPRESSION [ IF EXISTS ]: turns a stored generated column into a normal base column. Existing data in the column is retained, but the generation expression is not applied to future changes.

If you use the DROP EXPRESSION IF EXISTS clause and the column is not a stored generated column, no error is thrown, but a message notification appears.

ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY: adds a new identity column to an existing table. SET GENERATED { ALWAYS | BY DEFAULT }: changes the generation attribute of an existing identity column. DROP IDENTITY [ IF EXISTS ]: removes the identity property from a column. For more information, see the CREATE TABLE documentation. The preceding clauses, similar to the SET DEFAULT clause, only affect the behavior of subsequent INSERT and UPDATE statements. The preceding clauses do not cause existing rows in the table to change.

If you use the DROP IDENTITY IF EXISTS clause in the statement and the column is not an identity column, no error is thrown, but a message notification appears.

SET sequence_option RESTART: changes the sequence that underlies an existing identity column. sequence_option is an option supported in the ALTER SEQUENCE statement such as INCREMENT BY.

SET STATISTICS: sets the per-column statistics-gathering target for subsequent ANALYZE operations. You can set the target to a value in the range of 0 to 10,000. Alternatively, you can set the target to -1 to use the system default statistics target (default_statistics_target).

The SET STATISTICS clause requires a SHARE UPDATE EXCLUSIVE lock.

SET ( attribute_option = value [, ... ] ) RESET ( attribute_option [, ... ] ): configures or resets per-attribute options. The only defined per-attribute options are n_distinct and n_distinct_inherited, which override the number-of-distinct-values estimates made by subsequent ANALYZE operations. The n_distinct option affects the statistics about the table itself. The n_distinct_inherited option affects the statistics gathered for the table and its child tables. If you set the option to a positive value, ANALYZE assumes that the column contains exactly the specified number of distinct non-null values. If you set the option to a negative value, which must be greater than or equal to -1, ANALYZE assumes that the number of distinct non-null values in the column has a linear relationship with the size of the table. The exact count of distinct non-null values is calculated by multiplying the estimated size of the table by the absolute value of the specified negative number. For example, a value of -1 indicates that all values in the column are distinct. A value of -0.5 indicates that each value appears twice on the average. This can be useful when the size of the table changes over time, since the multiplication by the number of rows in the table is performed only during query planning. To use the normal estimation method for the number of non-null distinct values, specify a value of 0.

Changing per-attribute options requires a SHARE UPDATE EXCLUSIVE lock.

SET STORAGE: configures the storage mode for a column. This clause determines whether the column is held inline or in a secondary TOAST table, and whether the data is compressed. The PLAIN storage mode is designed for fixed-length types such as integer. In this mode, data is stored within the main row structure without compression. The MAIN storage mode stores data within the main row structure and allows for compression. The EXTERNAL storage mode stores data outside the main row structure without compression. The EXTENDED storage mode stores the data outside the main table structure and provides data compression to reduce storage usage. The EXTENDED storage mode is the default storage mode for most data types that support non-PLAIN storage. To enhance the performance of substring operations on large text and bytea values, you can specify the EXTERNAL storage mode. However, this mode can lead to an increase in the storage space. Take note that the SET STORAGE clause configures the storage mode for future table updates and does not make changes to the existing data.

ADD table_constraint [ NOT VALID ]: adds a new constraint to a table. The syntax follows the way constraints are defined in the CREATE TABLE statement, but with an additional NOT VALID option. You can use the NOT VALID option only for foreign key and CHECK constraints.

In most cases, this clause causes a scan of the table to verify that all existing rows in the table satisfy the new constraint. If you specify the NOT VALID option, the potentially-lengthy scan is skipped. The added constraint is enforced against subsequent INSERT and UPDATE statements. If you add a foreign key constraint, the operations succeed only if a matching row exists in the referenced table. If you add a CHECK constraint, the operations succeed only if the new row matches the specified check condition. The database does not assume that the constraint is applied to all rows in the table, until the constraint is validated by using the VALIDATE CONSTRAINT option.

Most forms of the ADD table_constraint clause require an ACCESS EXCLUSIVE lock, but the ADD FOREIGN KEY clause requires only a SHARE ROW EXCLUSIVE lock. Take note that the ADD FOREIGN KEY clause also acquires a SHARE ROW EXCLUSIVE lock on the referenced table in addition to the lock on the table to which the constraint is added.

Additional restrictions apply when you add unique or primary key constraints to partitioned tables. For more information, see the CREATE TABLE documentation. Foreign key constraints on partitioned tables may not be declared as NOT VALID.

ADD table_constraint_using_index: adds a new PRIMARY KEY or UNIQUE constraint to a table based on an existing unique index. All columns of the index are included in the constraint.

The index cannot have expression columns or be a partial index. The index must be a B-tree index that has default sort ordering. The preceding restrictions ensure that the index is equivalent to one that is built by a regular ADD PRIMARY KEY or ADD UNIQUE clause.

If you add the PRIMARY KEY constraint, and the columns of the index are not marked as NOT NULL, the statement attempts to perform an ALTER COLUMN SET NOT NULL operation on each of the columns. The operation requires a full table scan to verify that the columns do not contain null values. In all other cases, the operation requires a short period of time to complete.

If you specify a constraint name, the index is renamed to match the specified constraint name. Otherwise, the constraint uses the same name as the index.

After you execute this statement, the index is “owned" by the constraint, which is also the result when you use a regular ADD PRIMARY KEY or ADD UNIQUE clause to build the index. In this case, the index disappears if you remove the constraint.

You cannot use this clause on partitioned tables.

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
    ALTER TABLE [ IF EXISTS ] name
        SET SCHEMA new_schema
    ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
        SET TABLESPACE new_tablespace [ NOWAIT ]
    ALTER TABLE [ IF EXISTS ] name
        ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
    ALTER TABLE [ IF EXISTS ] name
        DETACH PARTITION partition_name

    Actions:

        ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
        DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
        ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ 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 DROP EXPRESSION [ IF EXISTS ]
        ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
        ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
        ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
        ALTER [ COLUMN ] column_name SET STATISTICS integer
        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 }
        ADD table_constraint [ NOT VALID ]
        ADD table_constraint_using_index
        ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
        VALIDATE CONSTRAINT constraint_name
        DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
        DISABLE TRIGGER [ trigger_name | ALL | USER ]
        ENABLE TRIGGER [ trigger_name | ALL | USER ]
        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
        DISABLE ROW LEVEL SECURITY
        ENABLE ROW LEVEL SECURITY
        FORCE ROW LEVEL SECURITY
        NO FORCE ROW LEVEL SECURITY
        CLUSTER ON index_name
        SET WITHOUT CLUSTER
        SET WITHOUT OIDS
        SET TABLESPACE new_tablespace
        SET { LOGGED | UNLOGGED }
        SET ( storage_parameter [= value] [, ... ] )
        RESET ( storage_parameter [, ... ] )
        INHERIT parent_table
        NO INHERIT parent_table
        OF type_name
        NOT OF
        OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
        REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

    partition_bound_spec:

    IN ( partition_bound_expr [, ...] ) |
    FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
      TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
    WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

    column_constraint:

    [ CONSTRAINT constraint_name ]
    { NOT NULL |
      NULL |
      CHECK ( expression ) [ NO INHERIT ] |
      DEFAULT default_expr |
      GENERATED ALWAYS AS ( generation_expr ) STORED |
      GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
      UNIQUE index_parameters |
      PRIMARY KEY index_parameters |
      REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
        [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

    table_constraint:

    [ 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 action ] [ ON UPDATE action ] }
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

    table_constraint_using_index:

        [ CONSTRAINT constraint_name ]
        { UNIQUE | PRIMARY KEY } USING INDEX index_name
        [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

    index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints:

    [ INCLUDE ( column_name [, ... ] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ USING INDEX TABLESPACE tablespace_name ]

    exclude_element in an EXCLUDE constraint:

    { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

If you want to add a new constraint, but do not want to block table updates for an extended period of time, you can add the constraint by using an existing index. To add a constraint by using an existing index, create the index by using the CREATE INDEX CONCURRENTLY statement, and then apply or enforce the constraint as an official constraint by using the following syntax. See the following example.

ALTER CONSTRAINT: changes the attributes of a constraint that was previously created. Only foreign key constraints may be changed.

VALIDATE CONSTRAINT: validates a foreign key or check constraint for which the NOT VALID option is specified, by scanning the table to ensure that no rows for which the constraint is not satisfied exist. This operation does not take effect if the constraint is already marked as valid.

DROP CONSTRAINT [ IF EXISTS ]: removes a constraint from the table together with the indexes that underlie the constraint. If you specify the IF EXISTS option and the constraint does not exist, no error is thrown, but a message notification appears.

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER: enables or disables triggers that belong to the table. A disabled trigger is still known to the system, but is not executed when the corresponding triggering event occurs. For a deferred trigger, its status is checked when the corresponding triggering event occurs, but not when the trigger function is actually executed. You can disable or enable a single trigger (by specifying its name), all triggers on the table, or only user triggers. The user trigger option excludes internally generated constraint triggers, such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints. You must have superuser privileges to disable or enable internally generated constraint triggers. If the triggers are not executed, the integrity of the constraint cannot be guaranteed.

The trigger firing mechanism is also affected by the session_replication_role configuration variable. Simply enabled triggers are fired when the replication role is "origin" (default) or "local". Triggers configured as ENABLE REPLICA are fired only if the session is in "replica" mode, and triggers configured as ENABLE ALWAYS are fired regardless of the current replication role.

As a result, triggers are not automatically fired on replicas. If a trigger is used on the origin to propagate data between tables, the replication system also replicates the propagated data. In this case, the trigger must not be fired a second time on the replica, to prevent duplication. However, if you use a trigger for a different purpose such as creating external alerts, you must set the trigger to ENABLE ALWAYS. This way, the trigger is fired on replicas.

A SHARE ROW EXCLUSIVE lock is applied when you perform this operation.

DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE: enables or disables rewrite rules for the table. A disabled rewrite rule is still known to the system, but the rule is not applied during query rewriting. The semantics are the same as the semantics for disabled or enabled triggers. This configuration is ignored by ON SELECT rules, which are always applied to keep views working even if the current session is in a non-default replication role.

The rule firing mechanism is also affected by the session_replication_role configuration variable, similar to triggers as described in the preceding section.

DISABLE/ENABLE ROW LEVEL SECURITY: enables or disables the row security policies for the table. If you enable the row security policies and no policies exist for the table, the default-deny policy is applied. Take note that policies can exist for a table even if you disable row-level security policies. In this case, the policies are not applied and the policies are ignored.

NO FORCE/FORCE ROW LEVEL SECURITY: enables or disables row security policies for the table if the user is the table owner. If you enable the row security policies, row-level security policies are applied if the user is the table owner. If the policies are disabled (default), row-level security policies are not applied when the user is the table owner.

CLUSTER ON: selects the default index for future CLUSTER operations. This clause does not actually re-cluster the table.

A SHARE UPDATE EXCLUSIVE lock is applied when you modify the cluster options.

SET WITHOUT CLUSTER: removes the most recently used CLUSTER index specification from the table. The clause does not affect future cluster operations that do not specify an index.

A SHARE UPDATE EXCLUSIVE lock is applied when you modify the cluster options.

SET WITHOUT OIDS: a backward-compatible syntax for removing the oid system column. This clause does not take effect because oid columns can no longer be added.

SET TABLESPACE: changes the tablespace of the table to the specified tablespace and moves the data files associated with the table to the specified tablespace. Indexes on the table are not moved, but you can separately move the indexes by using additional SET TABLESPACE statements. This clause does not take effect on a partitioned table. However, partitions subsequently created by using CREATE TABLE PARTITION OF uses the specified tablespace, unless overridden by a TABLESPACE clause.

To move all tables in the current database in a tablespace, use the ALL IN TABLESPACE clause, which locks all tables to be moved first and then moves each one. If you specify the OWNED BY option, only tables owned by the specified roles are moved. If you specify the NOWAIT option, the statement fails when all required locks cannot be immediately acquired. Take note that this clause does not move system catalogs. To move system catalogs, use the ALTER DATABASE or explicit ALTER TABLE invocations. The information_schema relations are not considered as part of the system catalogs and are moved.

SET { LOGGED | UNLOGGED }: changes the table from unlogged to logged or vice-versa (see UNLOGGED). You cannot use this clause on a temporary table.

SET ( storage_parameter [= value ] [, ... ] ): changes one or more storage parameters for the table. For information about the available parameters, see the "Storage Parameters" section in the CREATE TABLE documentation. Take note that this clause may not immediately change the table content, depending on the parameter you use to rewrite the table. To perform a forced table rewrite, you can use the VACUUM FULL or CLUSTER statement, or one of the clauses of the ALTER TABLE statement. For planner-related parameters, the changes take effect the next time the table is locked. This way, the queries that are being performed are not affected.

The SHARE UPDATE EXCLUSIVE lock is used for the fillfactor, toast, and autovacuum storage parameters, and the parallel_workers planner parameter.

RESET ( storage_parameter [, ... ] ): resets one or more storage parameters to their default values. Similar to the SET clause, a table rewrite may be needed to entirely update the table.

INHERIT parent_table: adds a table as a new child table of the specified parent table. Then, queries for the parent table will include records of the child table. To add a table as a child table, make sure that the table contains the same columns as the parent table. The child table can have additional columns. The columns must use matching data types. If a column has NOT NULL constraints in the parent table, it must also have NOT NULL constraints in the child table.

For all CHECK constraints of the parent table, except those marked as non-inheritable (ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT) in the parent table, the child table must have matching constraints. All child-table constraints matched must not be marked as non-inheritable. This clause does not consider or affect the UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints in the statement.

NO INHERIT parent_table: removes a table from the list of child tables of the specified parent table. In this case, queries for the parent table will no longer include records from the child table.

OF type_name: associates the table with a composite type, similarly to what is accomplished with the CREATE TABLE OF statement. The columns and their data types must exactly match the columns and data types defined in the composite type. The table must not inherit from other tables. The preceding restrictions ensure that the CREATE TABLE OF statement allows an equivalent table definition.

NOT OF: dissociates a typed table from its type.

OWNER TO: changes the owner of the table, sequence, view, materialized view, or foreign table to the specified user.

REPLICA IDENTITY: changes the information that is written to the write-ahead log to identify updated or deleted rows. This option does not take effect, except when logical replication is used. DEFAULT: records the previous values of the columns of the primary key. This is the default option for non-system tables. USING INDEX: records the previous values of the columns covered by the named index. The index must be unique, not partial, not deferrable, and include only columns to which the NOT NULL constraint is added. FULL: records the previous values of all columns in the row. NOTHING: does not record information about the old row. This is the default option for system tables. In all cases, the previous values are not recorded, except when at least one of the columns whose previous values can be recorded changed between the old and new row versions.

RENAME: changes the name of the table (or an index, sequence, view, materialized view, or foreign table), the name of an individual column in the table, or the name of a constraint of the table. When you rename a constraint that has an underlying index, the index is also renamed. This clause does not take effect on the stored data.

SET SCHEMA: moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are also moved.

ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }: attaches an existing table, which may be partitioned, as a partition of the destination table. You can attach the table as a partition for specific values by using the FOR VALUES option or as a default partition by using the DEFAULT option. For each index in the destination table, this clause creates a corresponding index in the attached table. If an equivalent index already exists, it is attached to the index of the destination table, as if the ALTER INDEX ATTACH PARTITION statement had been executed. If the existing table is a foreign table and UNIQUE indexes exist on the destination table, the existing table cannot be attached as a partition of the destination table. For each user-defined row-level trigger that exists in the destination table, this clause creates a corresponding trigger in the attached table.

A partition that uses the FOR VALUES option uses the same syntax for partition_bound_spec as the CREATE TABLE statement. The partition bound specification must correspond to the partitioning strategy and partition key of the destination table. The table that you want to attach must have the same columns as the destination table and must not have additional columns. In addition, the column data types must also match. The existing table must have all the NOT NULL and CHECK constraints of the destination table. This clause does not consider or affect FOREIGN KEY constraints. If the UNIQUE and PRIMARY KEY constraints from the parent table do not exist in the partition, the constraints are created in the partition. If one of the CHECK constraints of the table that is being attached is marked as NO INHERIT, the operation fails. You must recreate the constraint without the NO INHERIT clause.

If the new partition is a regular table, a full table scan is performed to verify that existing rows in the table do not violate the partition constraint. You can avoid the scan by adding a valid CHECK constraint to the table that allows only rows that meet the partition constraint before executing the statement. The CHECK constraint ensures that a full table scan is not required to validate the partition constraint. However, if a partition key is an expression and the partition does not accept NULL values, the CHECK constraint does not take effect. If you attach a list partition that does not accept NULL values, add a NOT NULL constraint to the partition key column. If it is an expression, you do not need to perform the preceding operation.

If the new partition is a foreign table, you do not need to verify that all rows in the foreign table comply with the partition constraint.

When a table has a default partition, defining a new partition changes the partition constraint for the default partition. The default partition cannot contain rows that need to be moved to the new partition, and is scanned to verify that the preceding rows do not exist. You can avoid the scan by adding an appropriate CHECK constraint. If the default partition is a foreign table, the scan is skipped.

When you add a partition to the parent table, a SHARE UPDATE EXCLUSIVE lock is applied to the parent table and an ACCESS EXCLUSIVE lock is applied to the table that is being attached and the default partition.

DETACH PARTITION partition_name: detaches the specified partition of the destination table. The detached partition continues to exist as a standalone table, but is no longer has any ties to the table from which it is detached. Any indexes that are attached to the indexes of the destination table are detached. Any triggers that are created as clones of the triggers in the destination table are removed.

All clauses of the ALTER TABLE statement that act on a single table, except RENAME, SET SCHEMA,ATTACH PARTITION, and DETACH PARTITION, can be combined into a list of multiple alternations to be applied together. For example, you can add several columns and change the type of several columns in a single statement. This is useful in managing large tables because only one pass over the tables must be made.

You must own the table to use ALTER TABLE. To change the schema or tablespace of a table, you must have the CREATE permission on the new schema or tablespace. To add the table as a new child table of a parent table, you must own the parent table. To attach a table as a new partition of the table, you must own the table that you want to attach. To change the owner, you must have the necessary privileges to assume the identity and permissions of the role to which you want to transfer ownership. The new role must have the CREATE permission on the schema of the table. These restrictions ensure that changing the owner of a table does not give you new capabilities by deleting and then recreating the table. Only a superuser can change the ownership of any table. To add a column, change a column type, or use the OF clause, you must also have the USAGE privilege on the data type.

Parameters

IF EXISTS: does not throw an error if the specified table does not exist. A message notification appears instead.

name: the name of an existing table that you want to change. The name can be schema-qualified. If you specify ONLY before the name of the table, only the table is changed. If you do not specify ONLY before the name of the table, the table and all its child tables (if any) are changed. You can specify * after the table name to explicitly indicate that the child tables are included.

column_name: the name of a new or existing column.

new_column_name: the new name of an existing column.

new_name: the new name of the table.

data_type: the data type of the new column, or the new data type of an existing column.

table_constraint: the new table constraint for the table.

constraint_name: the name of a new or existing constraint.

CASCADE: automatically removes objects that depend on the removed column or constraint, such as views that reference the column. All objects that depend on the removed objects are also removed.

RESTRICT: does not remove the column or constraint on which objects depend. This is the default option.

trigger_name: the name of a single trigger that you want to disable or enable.

ALL: disables or enables all triggers that belong to the table. If any of the triggers are internally generated constraint triggers, such as triggers that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints, the superuser privilege is required.

USER: disables or enables all triggers that belong to the table except for internally generated constraint triggers, such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.

index_name: the name of an existing index.

storage_parameter: the name of a table storage parameter.

value: the new value of a table storage parameter. The value varies based on the parameter and can be a number or a string.

parent_table: a parent table that you want to associate with or disassociate from the table.

new_owner: the username of the new owner of the table.

new_tablespace: the name of the tablespace to which the table is moved.

new_schema: the name of the schema to which the table is moved.

partition_name: the name of the table that you want to attach as a new partition or detach from the table.

partition_bound_spec: the partition bound specification for a new partition.

Description

The COLUMN keyword can be omitted.

When you add a column by using the ADD COLUMN clause and specify a non-volatile DEFAULT value, the default value is evaluated at the time the statement is executed. The result is stored in the metadata of the table. That value is used for the column of all existing rows. If you do not specify the DEFAULT option, NULL is used. A rewrite of the table is not required in both cases.

If you add a column with a volatile DEFAULT clause or change the type of an existing column, the entire table and its indexes are rewritten. When you change the type of an existing column, if the USING clause does not change the column content and the old type is binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not required. However, indexes must always be rebuilt unless the system can verify that the new index is logically equivalent to the existing one. Table or index rebuilds may require a significant amount of time for a large table and temporarily require almost twice the disk space.

When you add a CHECK or NOT NULL constraint, the table is scanned to verify that existing rows meet the constraint. A table rewrite is not required.

When you attach a new partition, the partition may be scanned to verify that existing rows meet the partition constraint.

The option to specify multiple changes in a single ALTER TABLE statement is provided to allow multiple table scans or rewrites to be combined into a single pass over a table.

Scanning a large table to verify a new foreign key or check constraint can take a long time, and other updates to the table are suspended until the ALTER TABLE ADD CONSTRAINT statement is committed. The main purpose of the NOT VALID constraint option is to reduce the impact of adding a constraint on concurrent updates. The NOT VALID option prevents the ADD CONSTRAINT clause from scanning the table. In this case, the ADD CONSTRAINT clause can be committed immediately. Then, you can specify the VALIDATE CONSTRAINT clause to verify that existing rows satisfy the constraint. The validation step does not need to suspend concurrent updates because other transactions enforce the constraint for rows that the transactions insert or update. Only pre-existing rows must be checked. Therefore, a SHARE UPDATE EXCLUSIVE lock is applied to the table that is being changed during validation. For a foreign key constraint, a ROW SHARE lock is also applied to the table referenced by the constraint. In addition to improving concurrency, you can use NOT VALID and VALIDATE CONSTRAINT in scenarios in which the table contains pre-existing violations. When the constraint takes effect, no new violations can occur, and the existing problems can be corrected until VALIDATE CONSTRAINT is successful.

DROP COLUMN does not remove the column, but makes the column invisible to SQL operations. Subsequent INSERT and UPDATE statements in the table store a null value for the column. Therefore, removing a column is quick but does not immediately reduce the on-disk size of your table, because the space occupied by the removed column is not reclaimed. The space is reclaimed over time as existing rows are updated.

To force immediate reclamation of space occupied by a removed column, you can execute one of the clauses of ALTER TABLE that are used to rewrite the whole table. This reconstructs each row by replacing the removed column with a null value.

The rewriting clauses of ALTER TABLE are not MVCC-safe. After a table rewrite, the table appears empty to concurrent transactions that use a snapshot taken before the rewrite occurs.

The USING option of the SET DATA TYPE clause can specify an expression that can convert the column data from the current type to the new type. The option can refer to other columns in addition to the column that is being converted. This allows general conversions to be performed by using the SET DATA TYPE syntax. However, because of this flexibility, the USING expression is not applied to the default value of a column because the result may not be a constant expression required for a default value. This means that when no implicit or assignment cast rules are available to convert data from the current data type into a new data type, SET DATA TYPE may fail to convert the default value even if a USING clause is supplied. In the preceding cases, remove the default value with DROP DEFAULT, perform the ALTER TYPE operation, and then use SET DEFAULT to add a suitable new default value. Similar considerations apply to indexes and constraints related to the column.

When a table has child tables, you cannot add, rename, or change the type of a column only in the parent table. You must perform the same operation on the child tables. This ensures that the child tables always have columns that match the columns of the parent table. You cannot rename a CHECK constraint in a parent table without renaming the constraint in all its child tables. This ensures that CHECK constraints also match between the parent table and its child tables. This restriction does not apply to index-based constraints. When you perform a SELECT operation on a parent table that has child tables, the query retrieves data from the parent table and automatically retrieves data from its child tables. Therefore, a constraint in the parent table cannot be marked as valid unless the constraint is also marked as valid in the child tables. ALTER TABLE ONLY cannot be performed in all of the preceding cases.

A recursive DROP COLUMN operation removes a column of a child table only if the child table does not inherit the column from its parent tables and never had an independent definition of the column. A non-recursive DROP COLUMN (ALTER TABLE ONLY ... DROP COLUMN) operation does not remove child table columns but marks them as independently defined rather than inherited. A non-recursive DROP COLUMN operation fails for a partitioned table, because all partitions of a table must have the same columns as the partitioning root.

The actions for identity columns (ADD GENERATED, SET, and DROP IDENTITY), and the actions TRIGGER, CLUSTER, OWNER, and TABLESPACE, do not recurse to the child tables, which means that they always act as though ONLY is specified. Adding a constraint recurses only for CHECK constraints that are not marked as NO INHERIT.

You cannot change any part of a system catalog.

Examples

To add a column of the varchar type to a table, execute the following statement:

    ALTER TABLE distributors ADD COLUMN address varchar(30);

This statement fills all existing rows in the table with null values for the new column.

To add a column with non-null default values, execute the following statement:

    ALTER TABLE measurements
      ADD COLUMN mtime timestamp with time zone DEFAULT now();

This statement fills existing rows with the current time as the value of the new column, and fills new rows with the time at which they are inserted as the value for the new column.

To add a column and fill it with a value different from the default value to be used later, execute the following statement:

    ALTER TABLE transactions
      ADD COLUMN status varchar(30) DEFAULT 'old',
      ALTER COLUMN status SET default 'current';

This statement fills existing rows with old as the value of the new column, and fills new rows with current as the default value of the new column. To achieve the preceding effects, you can issue the two clauses in separate ALTER TABLE statements.

To remove a column from a table, execute the following statement:

    ALTER TABLE distributors DROP COLUMN address RESTRICT;

To change the types of two existing columns in one ALTER TABLE statement, execute the following statement:

    ALTER TABLE distributors
        ALTER COLUMN address TYPE varchar(80),
        ALTER COLUMN name TYPE varchar(100);

To change the data type of a column to timestamp with the time zone by using a USING clause, execute the following statement:

    ALTER TABLE foo
        ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
        USING
            timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

If the column has a default expression that cannot be automatically cast to the new data type, execute the following statement:

    ALTER TABLE foo
        ALTER COLUMN foo_timestamp DROP DEFAULT,
        ALTER COLUMN foo_timestamp TYPE timestamp with time zone
        USING
            timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
        ALTER COLUMN foo_timestamp SET DEFAULT now();

To rename an existing column, execute the following statement:

    ALTER TABLE distributors RENAME COLUMN address TO city;

To rename an existing table, execute the following statement:

    ALTER TABLE distributors RENAME TO suppliers;

To rename an existing constraint, execute the following statement:

    ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

To add a non-null constraint to a column, execute the following statement:

    ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

To remove a non-null constraint from a column, execute the following statement:

    ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

To add a check constraint to a table and its child tables, execute the following statement:

    ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

To add a check constraint to a table but not to its child tables, execute the following statement:

    ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

The check constraint is not inherited by future child tables.

To remove a check constraint from a table and its child tables, execute the following statement:

    ALTER TABLE distributors DROP CONSTRAINT zipchk;

To remove a check constraint from a table but not from its child tables, execute the following statement:

    ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

The check constraint remains effective for the child tables of the table named distributors.

To add a foreign key constraint to a table, execute the following statement:

    ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);

To add a foreign key constraint to a table and minimize the impact on the table performance, execute the following statements:

    ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
    ALTER TABLE distributors VALIDATE CONSTRAINT distfk;

To add a (multicolumn) unique constraint to a table, execute the following statement:

    ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

To add an automatically named primary key constraint to a table, execute the following statement. Take note that a table can have only one primary key.

    ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

To move a table to a different tablespace, execute the following statement:

    ALTER TABLE distributors SET TABLESPACE fasttablespace;

To move a table to a different schema, execute the following statement:

    ALTER TABLE myschema.distributors SET SCHEMA yourschema;

To recreate a primary key constraint and allow the index to be built without preventing write operations on the table, execute the following statement:

    CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
    ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
        ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

To attach a partition to a range-partitioned table, execute the following statement:

    ALTER TABLE measurement
        ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

To attach a partition to a list-partitioned table, execute the following statement:

    ALTER TABLE cities
        ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

To attach a partition to a hash-partitioned table, execute the following statement:

    ALTER TABLE orders
        ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

To attach a default partition to a partitioned table, execute the following statement:

    ALTER TABLE cities
        ATTACH PARTITION cities_partdef DEFAULT;

To detach a partition from a partitioned table, execute the following statement:

    ALTER TABLE measurement
        DETACH PARTITION measurement_y2015m12;