• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. ALTER TABLE

ALTER TABLE

Function

There are several clauses to modify the definition of a table:

  • ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]

    Adds a new column into a table.

    The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column.

    When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified).

  • ADD ( { column_name data_type [ compress_mode ] } [, ...] )

    Adds columns in the table.

  • MODIFY ( { column_name data_type } [, ...] )

    Modifies the data type of an existing column in the table.

  • DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

    Drops a column from a table. Index and constraint related to the column are automatically dropped. If an object not belonging to the table depends on the column, CASCADE must be specified, such as foreign key reference and view.

    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. Therefore, column deletion takes a short period of time but does not immediately release the table space on the disks, because the space occupied by the deleted column is not reclaimed. The space will be reclaimed when VACUUM is run.

  • ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

    Modifies the type of a column in a table. Indexes and simple table constraints on the column will be automatically converted to use the new column type by reparsing the originally supplied expression.

    The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column.

    The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is 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 TYPE requires rewriting features in an entire table. This is an advantage sometimes, because it eliminates useless space in a table. For example, to reclaim the space occupied by a deleted column, the fastest method is to use the command.

    ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

    In this command, anycol indicates any column existing in the table and anytype indicates the type of the prototype of the column. The result is that there is no visible change of meanings in the table, but the table is forcibly rewritten. In this way, the data that is no longer used is deleted.

    The USING option of ALTER TYPE can actually specify any expression involving the old values of the row; that is, it can refer to other columns as well as the one being converted. This allows general conversions to be done with the ALTER TYPE syntax. Because of this flexibility, the USING expression is not applied to the column's default value (if any); the result might not be a constant expression as required for a default. This means that when there is no implicit or assignment cast from old to new type, ALTER TYPE might fail to convert the default even though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET DEFAULT to add a suitable new default. Similar considerations apply to indexes and constraints involving the column.

  • ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }

    Sets or removes the default value for a column. The default values only apply to subsequent INSERT commands; they do not cause rows already in the table to change. Defaults can also be created for views, in which case they are inserted into INSERT statements on the view before the view's ON INSERT rule is applied.

  • ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

    Changes whether a column is marked to allow null values or to reject null values. You can only use SET NOT NULL when the column contains no null values.

  • ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer

    This form sets the per-column statistics-gathering target for subsequent ANALYZE operations. The target can be set in the range 0 to 10,000. Set it to -1 to revert to using the system default statistics target.

    When using PERCENT, the range of integer is 0 to 100.

  • {ADD | DELETE} STATISTICS ((column_1_name, column_2_name [, ...]))

    Adds or deletes the declaration of collecting multi-column statistics. After the collection is declared, multi-column statistics can be collected when ANALYZE is performed for a table or a database. The statistics on a maximum of 32 columns can be collected at a time. You are not allowed to add or delete such declaration for system catalogs or foreign tables.

  • ALTER [ COLUMN ] column_name SET ( {attribute_option = value} [, ... ] )

    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )

    Sets or resets per-attribute options.

    Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited. n_distinct affects statistics of table, while n_distinct_inherited affects the statistics of table and its subtables. Currently, only SET/RESET n_distinct is supported, and SET/RESET n_distinct_inherited is forbidden.

  • ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

    Sets the storage mode for a column. This controls whether this column is held inline or in a secondary TOAST table, and whether the data should be compressed or not. It only supports the setting of row-store table, and has no meaning for column-store table, error will occur in this case.

    • PLAIN must be used for fixed-length values such as integer and is inline, uncompressed.
    • MAIN is for inline, compressible data.
    • EXTERNAL is for external, uncompressed data. Use of EXTERNAL will make substring operations on text and bytea values run faster, at the penalty of increased storage space.
    • EXTENDED is for external, compressed data. EXTENDED is the default for most data types that support non-PLAIN storage.
    NOTE:

    SET STORAGE itself does not change anything in the table, it sets the strategy to be pursued during future table updates.

  • ADD table_constraint [ NOT VALID ]

    Adds a new table constraint.

  • ADD table_constraint_using_index

    Adds primary key constraint or unique constraint based on the unique index.

  • VALIDATE CONSTRAINT constraint_name

    Validates a foreign key or check constraint that was previously created as NOT VALID, by scanning the table to ensure there are no rows for which the constraint is not satisfied. Nothing happens if the constraint is already marked valid.

  • DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

    Drops a table constraint.

  • CLUSTER ON index_name

    Selects the default index for future CLUSTER operations. It does not actually re-cluster the table.

  • SET WITHOUT CLUSTER

    Removes the most recently used CLUSTER index specification from the table. This operation affects future cluster operations that do not specify an index.

  • SET ( {storage_parameter = value} [, ... ] )

    Changes one or more storage parameters for the table.

  • RESET ( storage_parameter [, ... ] )

    Resets one or more storage parameters to their defaults. As with SET, a table rewrite might be needed to update the table entirely.

  • OWNER TO new_owner

    Changes the owner of the table, sequence, or view to the specified user.

  • SET {COMPRESS|NOCOMPRESS}

    Sets the compression feature of a table.

    The table compression feature affects only the storage mode of data inserted in a batch subsequently and does not affect storage of existing data. Setting the table compression feature will result in the fact that there are both compressed and uncompressed data in the table.

    • NOCOMPRESS: If the NOCOMPRESS keyword is specified, the existing compression feature of the table is not changed.
    • COMPRESS: If the COMPRESS keyword is specified, the table compression feature is triggered if tuples are inserted in a batch.
  • RENAME TO new_table_name

    Renames a table. The renaming does not affect stored data.

  • RENAME [ COLUMN ] column_name TO new_column_name

    Renames a column.

  • RENAME CONSTRAINT constraint_name TO new_constraint_name

    Renames the table constraint.

  • SET SCHEMA new_schema

    Moves the table into another schema. Associated indexes and constraints owned by table columns are migrated as well. Currently, the schema for sequences cannot be changed. If the table has sequences, delete the sequences, and create them again or delete the ownership between the table and sequences. In this way, the table schema can be changed.

    To change the schema of a table, you must also have CREATE privilege on the new schema. To add the table as a new child of a parent table, you must own the parent table as well. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE permission on the table's schema. These restrictions enforce that altering the owner does not do anything you could not do by dropping and recreating the table. However, a system administrator can alter ownership of any table anyway.

    NOTE:
    • All the actions except for RENAME and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. For example, it is possible to add several columns or alter the type of several columns in a single command. This is useful with large tables, since only one pass over the table need be made.
    • Adding a CHECK or NOT NULL constraint requires scanning the table to verify that existing rows meet the constraint.
    • Adding a column with a non-null default or changing the type of an existing column will require the entire table to be rewritten. Table rebuilding may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.
  • TO { GROUP groupname | NODE ( nodename [, ... ] ) }

    The syntax is only available in extended mode (when GUC parameter support_extended_features is on). Exercise caution when enabling the mode. It is used for tools like internal dilatation tools. Common users should not use the mode.

  • ADD NODE ( nodename [, ... ] )

    It is only available for internal scale-out tools. Common users should not use the mode.

  • DELETE NODE ( nodename [, ... ] )

    It is only available for internal scale-in tools. Common users should not use the mode.

Precautions

  • You must own the table to use ALTER TABLE. A system administrator has the permission by default.
  • The tablespace of the partition table cannot be modified. However, the tablespace of the partition can be modified.
  • The storage parameter ORIENTATION cannot be modified.
  • Currently, SET SCHEMA can only set schemas to user schemas. It cannot set a schema to a system internal schema.
  • The distribution column of a table cannot be modified.
  • The column-store table only supports PARTIAL CLUSTER KEY.
  • In a column-store table, you can perform ADD COLUMN, ALTER TYPE, SET STATISTICS, DROP COLUMN operations, and change table name and space. The column and column type should be Data Types supported by column store. The USING option of ALTER TYPE only supports constant expression and expression involved in the column.
  • The column constraints supported by column-store table include NULL, NOT NULL, and DEFAULT constant values. Only DEFAULT value can be modified (SET DEFAULT and DROP DEFAULT). Currently, NULL/NOT NULL constraints cannot be modified.
  • Auto-increment columns cannot be added, or a column in which the DEFAULT value contains the nextval() expression cannot be added either.

Syntax

  • ALTER TABLE modifies the definition of a table.
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
        action [, ... ];
    There are several clauses of action:
    column_clause
        | ADD table_constraint [ NOT VALID ]
        | ADD table_constraint_using_index
        | VALIDATE CONSTRAINT constraint_name
        | DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
        | CLUSTER ON index_name
        | SET WITHOUT CLUSTER
        | SET ( {storage_parameter = value} [, ... ] )
        | RESET ( storage_parameter [, ... ] )
        | OWNER TO new_owner
        | SET TABLESPACE new_tablespace
        | SET {COMPRESS|NOCOMPRESS}
        | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
        | ADD NODE ( nodename [, ... ] )
        | DELETE NODE ( nodename [, ... ] )
    • There are several clauses of column_clause:
      ADD [ COLUMN ] column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]    
      | MODIFY column_name data_type    
      | 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 | DROP DEFAULT }    
      | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL    
      | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer    
      | ADD STATISTICS (( column_1_name, column_2_name [, ...] ))    
      | DELETE STATISTICS (( column_1_name, column_2_name [, ...] ))    
      | 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 }
      • column_constraint is as follows:
        [ CONSTRAINT constraint_name ]
            { NOT NULL |
              NULL |
              CHECK ( expression ) |
              DEFAULT default_expr  |
              UNIQUE index_parameters |
              PRIMARY KEY index_parameters }
            [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
      • compress_mode of a column is as follows:
        [ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS ]
    • table_constraint_using_index used to add the primary key constraint or unique constraint based on the unique index is as follows:
      [ CONSTRAINT constraint_name ]
          { UNIQUE | PRIMARY KEY } USING INDEX index_name
          [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    • table_constraint is as follows:
      [ CONSTRAINT constraint_name ]
          { CHECK ( expression ) |
            UNIQUE ( column_name [, ... ] ) index_parameters |
            PRIMARY KEY ( column_name [, ... ] ) index_parameters }
          [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
      index_parameters is as follows:
      [ WITH ( {storage_parameter = value} [, ... ] ) ]
          [ USING INDEX TABLESPACE tablespace_name ]
  • Rename the table.
    ALTER TABLE [ IF EXISTS ] table_name 
        RENAME TO new_table_name;
  • Rename the specified column in the table.
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
        RENAME [ COLUMN ] column_name TO new_column_name;
  • Rename the constraint of the table.
    ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
        RENAME CONSTRAINT constraint_name TO new_constraint_name;
  • Set the schema of the table.
    ALTER TABLE [ IF EXISTS ] table_name 
        SET SCHEMA new_schema;
  • Add columns.
    ALTER TABLE [ IF EXISTS ] table_name
        ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );
  • Update columns.
    ALTER TABLE [ IF EXISTS ] table_name 
        MODIFY ( { column_name data_type } [, ...] );

Parameter Description

  • IF EXISTS

    Sends a notification instead of an error if no tables have identical names. The notification prompts that the table you are querying does not exist.

  • table_name [*] | ONLY table_name | ONLY ( table_name )

    table_name is the name of table that you need to modify.

    If ONLY is specified, only the table is modified. If ONLY is not specified, the table and all subtables will be modified. You can add the asterisk (*) option following the table name to specify that all subtables are scanned, which is the default operation.

  • constraint_name

    Specifies the name of an existing constraint to drop.

  • index_name

    Name of this index

  • storage_parameter

    Indicates the name of a storage parameter.

  • new_owner

    Specifies the name of the new table owner.

  • new_tablespace

    Specifies the new name of the tablespace to which the table belongs.

  • column_name, column_1_name, column_2_name

    Specifies the name of a new or existing column.

  • data_type

    Specifies the type of a new column or a new type of an existing column.

  • compress_mode

    Specifies the compress option of the table, only available for row-store tables. The clause specifies the algorithm preferentially used by the column.

  • collation

    Specifies the collation rule name of a column.

  • NOT NULL | NULL

    Sets whether the column allows null values.

  • integer

    Specifies the constant value of an integer with a sign.

  • attribute_option

    Specifies an attribute option.

  • CHECK ( expression )

    New or updated rows must satisfy for an insert or update operation to succeed. Expressions evaluating to TRUE succeed. If any row of an insert or update operation produces a FALSE result, an error exception is raised and the insert or update does not alter the database.

    A check constraint specified as a column constraint should reference only the column's values, while an expression appearing in a table constraint can reference multiple columns.

    Currently, CHECK expression does not include subqueries and cannot use variables apart from the current column.

  • DEFAULT default_expr

    Assigns a default data value for a column.

    The data type of the default expression must match the data type of the column.

    The default expression will be used in any insert operation that does not specify a value for the column. If there is no default value for a column, then the default value is NULL.

  • UNIQUE index_parameters

    UNIQUE ( column_name [, ... ] ) index_parameters

    The UNIQUE constraint specifies that a group of one or more columns of a table can contain only unique values.

  • PRIMARY KEY index_parameters

    PRIMARY KEY ( column_name [, ... ] ) index_parameters

    The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate) and non-null values.

  • DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE

    Sets whether the constraint can be deferrable.

    • DEFERRABLE: deferrable can be postponed until the end of the transaction using the SET CONSTRAINTS command.
    • NOT DEFERRABLE: checks immediately after the execution of each command.
    • INITIALLY IMMEDIATE: checks immediately after the execution of each statement.
    • INITIALLY DEFERRED: checks when the transaction ends.
  • WITH ( {storage_parameter = value} [, ... ] )

    Specifies an optional storage parameter for a table or an index.

  • tablespace_name

    Specifies the name of the tablespace where the index locates.

  • new_table_name

    Specifies the new table name.

  • new_column_name

    Specifies the new name of a specific column in a table.

  • new_constraint_name

    Specifies the new name of a table constraint.

  • new_schema

    Specifies the new schema name.

  • CASCADE

    Automatically drops objects that depend on the dropped column or constraint (for example, views referencing the column).

  • RESTRICT

    Refuses to drop the column or constraint if there are any dependent objects. This is the default behavior.

  • schema_name

    Specifies the schema name of a table.

Example

See Example in CREATE TABLE.

Helpful Links

CREATE TABLE, DROP TABLE