ALTER TABLE

Description

Modifies tables, including modifying table definitions, renaming tables, renaming specified columns in tables, setting table schemas, and adding or updating multiple columns.

Precautions

• The owner of a table, users granted the ALTER permission on the table, or users granted the ALTER ANY TABLE permission can run the ALTER TABLE command. System administrators have the permission to run the command by default. To modify the owner or schema of a table, you must be the table owner or system administrator and a member of the new owner role.
• The tablespace of a partitioned table cannot be modified, but the tablespace of a 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.

Syntax

• Modify the definition of a table.

  ALTER TABLE { table_name [*] | ONLY table_name | ONLY ( table_name ) }
      action [, ... ];

  There are several clauses of action:

  column_clause
      | ADD [CONSTRAINT] table_constraint
      | DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
      | SET ( {storage_parameter = value} [, ... ] )
      | RESET ( storage_parameter [, ... ] )
      | OWNER TO new_owner
      | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
      | ADD NODE ( nodename [, ... ] )
      | DELETE NODE ( nodename [, ... ] )
      | UPDATE SLICE LIKE table_name
      | COMMENT [ = ] 'string'
      | [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] charset_name] [ [ DEFAULT ] COLLATE [ = ] collation_name]
      | ADD index_clause
      | DROP PRIMARY KEY
      | REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } 

  

  • ADD [CONSTRAINT] table_constraint

    Adds a table constraint.

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

    Deletes a table constraint.

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

    Changes one or more storage parameters for the table. If the value of table_name is an index name, ACTIVE_PAGES specifies the number of index pages, which may be less than the actual number of physical file pages and can be used for optimizer optimization. Currently, this parameter is valid only for the local index of the Ustore partitioned table and will be updated by VACUUM (including AUTOVACUUM) and ANALYZE. You are advised not to manually set this parameter because it is invalid in distributed mode.

  • 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 a table, sequence, or view to a specified user. For details about the username requirements, see •user_name.

  • 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 mainly 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 syntax.

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

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

  • UPDATE SLICE LIKE table_name

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

  • [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation ]

    Changes the default character set and collation of a table to the specified values. The modification does not affect the existing columns in the table.

  • DROP PRIMARY KEY

    Deletes a primary key constraint from a table.

  • REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
    Specifies the record level of old tuples in UPDATE and DELETE statements on a table in logical replication scenarios.

    • DEFAULT records the old value of the primary key column. If there is no primary key, DEFAULT does not record the old value.
    • USING INDEX records the old values of columns covered by the named indexes. These values must be unique, non-local, and non-deferrable, and contain the values of columns marked NOT NULL.
    • FULL records the old values of all columns in the row.
    • NOTHING does not record information in old rows.

    In logical replication scenarios, when the UPDATE and DELETE statements of a table are parsed, the parsed old tuples consist of the information recorded in this method. For tables with a primary key, this option can be set to DEFAULT or FULL. For a table without a primary key, set this parameter to FULL. Otherwise, the old tuple will be parsed as empty during decoding. Generally, you are advised not to set this parameter to NOTHING because old tuples are always parsed as empty.

    For Ustore tables, the NOTHING option is invalid, and the actual effect is the same as that of FULL. If DEFAULT does not have a primary key, all columns in the row are recorded.

  • There are several clauses of column_clause:

    ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET}  charset ][ COLLATE collation ] [ column_constraint [ ... ] ]    
    | ADD ({ column_name data_type [ compress_mode ]} [, ...] )
    | DROP [ COLUMN ]  column_name [ RESTRICT | CASCADE ]    
    | ALTER [ COLUMN ] column_name { SET DEFAULT default_expr | DROP DEFAULT }  

    

    • ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [ COLLATE collation ] [ column_constraint [ ... ] ]

      Adds a column to a table. If a column is added using 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 multiple columns to a table.

    • DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ]
      • Drops a column from a table. Indexes and constraints related to the column are automatically dropped. The CASCADE option is supported only as syntax but does not take effect when the version compatibility control parameter m_format_dev_version of an M-compatible database is set to 's1' or later.
      • The DROP COLUMN statement 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 tablespace on the disks, because the space occupied by the deleted column is not recycled. The space will be recycled when VACUUM is executed.
    • ALTER [ COLUMN ] column_name { SET DEFAULT default_expr | DROP DEFAULT }

      Sets or removes the default value for a column. The default values only apply to subsequent INSERT operations; 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.

      For details about the value range of default_expr, see •DEFAULT default_expr.

    • column_constraint is as follows:

      COMMENT 'string'
      |[ CONSTRAINT constraint_name ]
          { NOT NULL |
            NULL |
            CHECK ( expression ) |
            DEFAULT default_expr  |
            ON UPDATE update_expr |
            UNIQUE [KEY] index_parameters |
            [PRIMARY] KEY index_parameters
       }

    • compress_mode of a column is as follows:

      [ DICTIONARY ]

    • update_expr is as follows:

      { CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }

  • index_clause

    {INDEX | KEY} [ [schema_name.] index_name ] [ USING method ] ({column_name [(length)] | (expr) [ASC | DESC]}[,...]) [[COMMENT 'string' | USING method][...]]

    For details about the parameters, see CREATE INDEX.

  • table_constraint is as follows:

    [ CONSTRAINT [ constraint_name ] ]
        { CHECK ( expression ) |
          UNIQUE [INDEX|KEY][index_name] [USING access_method] ( { { column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_parameters [USING access_method] [comment 'string'] [ BY GLOBAL INDEX ]|
          PRIMARY KEY [ USING access_method ] [index_name] ( { column_name [ ASC | DESC ] }[, ... ] ) index_parameters [USING access_method] [comment 'string']
    }
            }  

    The value of access_method is as follows:

    BTREE

    index_parameters is as follows:

    [ WITH ( {storage_parameter = value} [, ... ] ) ]   

    Specifies whether to create a global secondary index.

    [BY GLONAL INDEX]

Parameters

• IF EXISTS

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

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

  table_name is the name of the table to be modified.

  If ONLY is specified, only the table is modified. If ONLY is not specified, the table and all subtables are modified. You can add the asterisk (*) option following the table name to specify that all subtables are scanned, which is the default operation. Currently, the syntax of ONLY with the * option is reserved, but the function is not supported.

• constraint_name
  • Specifies the name of an existing constraint to be dropped in the DROP CONSTRAINT operation.
  • Specifies the name of a new constraint in the ADD CONSTRAINT operation.

• index_name

  Specifies the index name.

  

  In the ADD CONSTRAINT operation, if both constraint_name and index_name are set for the PRIMARY/UNIQUE KEY constraint, the index name is specified by index_name.

• USING method

  Specifies the method of creating an index.

  For details about the value range, see "USING method" in Parameters.

  

  In the ADD CONSTRAINT operation:

  • If USING method is not specified, the default index method is btree for ASTORE or UB-tree for USTORE.
  • If the storage mode of a table is USTORE and the constraint in the SQL statement is specified as USING BTREE, the underlying layer automatically creates the constraint as USING UBTREE.
• ASC | DESC

  ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.

• expression

  Specifies an expression index constraint based on one or more columns of the table. It must be written in parentheses.

• storage_parameter

  Specifies the name of a storage parameter.

  TDE options:

  • enable_tde (Boolean)

    Specifies whether to set a table as an encrypted table. This parameter supports only row-store tables, segment-page tables, temporary tables, and unlogged tables. When setting enable_tde to on, ensure that the TDE function has been enabled using the GUC parameter enable_tde and the information for accessing the key service has been set using the GUC parameter tde_key_info. For details about how to use this parameter, see section "Transparent Data Encryption" in Feature Guide.

    Value range: on and off.

    • on: Transparent data encryption is enabled.
    • off: Transparent data encryption is disabled.
      

      • After the value is changed from on to off, the inserted or updated data is still encrypted when being written to the old page, and is not encrypted when being written to the new page generated after the switchover.
      • After the value is changed from off to on, the inserted or updated data is not encrypted when being written to the old page, and is automatically encrypted when being written to the new page generated after the switchover.

      That is, the encryption status on the new data page is the same as that after the encryption switchover, and the encryption status on the old data page is the same as that before the switchover. You are advised to manually perform VACUUM FULL on the table after the encryption switchover to ensure that the encryption status of all data pages is consistent.

    Default value: off

  • encrypt_algo (string type)

    Specifies the encryption algorithm of the encryption table.

    Value range: a string. The value can be AES_128_CTR or SM4_CTR.

    Default value: If enable_tde is set to on and the subparameter table_algorithm in the GUC parameter tde_encrypt_config is not empty, the default value is the same as that of table_algorithm. If table_algorithm is empty, the default value is AES_128_CTR. If enable_tde is set to off, the default value is null.

  The following option is added for creating an index:

  • parallel_workers (int type)

    Number of bgworker threads started when an index is created. For example, value 2 indicates that two bgworker threads are started to create indexes concurrently.

    Value range: [0,32]. The value 0 indicates that concurrent index creation is disabled.

    Default value: If this parameter is not set, the concurrent index creation function is disabled.

  • hasuids (Boolean type)

    Default value: off

    If this parameter is set to on, a unique table-level ID is allocated to a tuple when the tuple is updated.

• new_owner

  Specifies the name of the new table owner. For details about the username requirements, see •user_name.

• 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 whether to compress a table column. The clause specifies the compression algorithm preferentially used by the column. The current option cannot be used because row-store tables do not support compression.

• charset

  Specifies the character set of a table column. If this parameter is specified separately, the collation of the table column is set to the default collation of the specified character set.

• collation

  Specifies the collation rule name of a column. The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column. You can run the select * from pg_collation; command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result.

  The utf8mb4_bin, utf8mb4_general_ci, utf8mb4_unicode_ci, binary, gbk_chinese_ci, gbk_bin, gb18030_chinese_ci, and gb18030_bin collations are also supported. For details, see Table-level Character Sets and Collations.

  

  • Only the character type supports the specified character set. If the BINARY character set or collation is specified, the character type is converted to the corresponding binary type. If the type mapping does not exist, an error is reported. Currently, only the mapping from the TEXT type to the BLOB type is available.
  • Except the BINARY character set and collation, only the character set that is the same as the database encoding can be specified.
  • If the character set or collation of a column is not explicitly specified and the default character set or collation of the table is specified, the character set or collation of the column is inherited from the table.
  • If the modified character set or the character set corresponding to the collation is different from the character set of the current column, the data in the column is converted to the specified character set for encoding.
• NOT NULL | NULL

  Sets whether the column allows null values.

• ENABLE

  Specifies that the constraint is enabled. By default, the constraint is enabled.

• CHECK ( expression )

  New rows or rows to be updated must satisfy for an expression to be true. If any row produces a false result, an error is raised and the database is not modified.

  Currently, the CHECK expression cannot contain subqueries.

• DEFAULT default_expr
  • The DEFAULT clause is used to specify a default expression for a 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.
  • The following content can be specified: constants, numeric constants with positive and negative signs, and update_expr.
  • The value of update_expr can be used as the default value only for columns of the TIMESTAMP or DATETIME type, and the precision of the columns must be the same as that of update_expr.
• ON UPDATE update_expr

  The ON UPDATE clause is an attribute constraint of a column.

  When an UPDATE operation is performed on a tuple in a table, if new values of updated columns are different from old values in the table, column values with this attribute but not in updated columns are automatically updated to the current timestamp. If new values of updated columns are the same as old values in the table, column values with this attribute but not in updated columns remain unchanged. If columns with this attribute are in updated columns, column values are updated according to the specified update value.

  

  • In terms of syntax, update_expr supports four keywords: CURRENT_TIMESTAMP, LOCALTIMESTAMP, LOCALTIME, and NOW(). You can also specify or not specify the precision of a keyword with parentheses. For example, ON UPDATE CURRENT_TIMESTAMP(), ON UPDATE CURRENT_TIMESTAMP(5), ON UPDATE LOCALTIMESTAMP(), and ON UPDATE LOCALTIMESTAMP(6). If the keyword does not contain parentheses or contains empty parentheses, the precision is 0. The NOW keyword cannot contain parentheses. The four types of keywords are synonyms of each other and have the same attribute effect.
  • This attribute can be specified on columns of the TIMESTAMP and DATETIME types.
  • The precision specified by this attribute must be the same as that specified by the type in the corresponding column. Otherwise, an error is reported. For example, CREATE TABLE t1 (col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6));. If the precision is inconsistent, ERROR: Invalid ON UPDATE clause for "col1" is reported.
  • The same column cannot be specified for this attribute and the generated column constraint at the same time.
  • This attribute cannot be specified for the partition key in a partitioned table.
• COMMENT [ = ] 'string'
  • The COMMENT [ = ] 'string' clause is used to add comments to a table.
  • The COMMENT 'string' in column_constraint indicates that comments are added to a column.
  • The COMMENT 'string' in table_constraint indicates that comments are added to the indexes corresponding to the primary key and unique key.

  For details, see •COMMENT [ = ] 'string'.

• Column-level unique constraint: UNIQUE [KEY] index_parameters

  UNIQUE KEY has the same semantics as UNIQUE.

  Specifies that a group of one or more columns of a table can contain only unique values.

  

  If the source table is hash distributed table, creating a column-level unique key constraint will fail. You can use CREATE TABLE ADD to create a table-level unique constraint. The table-level unique constraint supports the BY GLOBAL INDEX option.

• Table-level unique constraint: UNIQUE [INDEX | KEY] [ index_name ][ USING method ]( {{ column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters [ BY GLOBAL INDEX]

  Specifies that a group of one or more columns of a table can contain only unique values.

  column_name (length) indicates the prefix key. For details, see •column_name ( length ).

  index_name indicates the index name.

  

  For a unique key constraint, if both constraint_name and index_name are specified, the value of index_name is used as the index name.

• Column-level primary key constraint: [PRIMARY] KEY index_parameters

  Table-level primary key constraint: PRIMARY KEY [index_name] [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters

  Specifies that a column or columns of a table can contain only unique (non-duplicate) and non-null values.

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

  Specifies an optional storage parameter for an index.

• new_table_name

  Specifies the new table name.

• new_column_name

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

• new_index_name

  Specifies the new index name in a table.

• new_schema

  Specifies the new schema name.

• CASCADE | RESTRICT

  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.

  

  This attribute is supported only as syntax but does not take effect when the version compatibility control parameter m_format_dev_version of an M-compatible database is set to 's1' or later.

• schema_name

  Specifies the schema name of a table.

Examples of Modifying a Table

• Rename a table.

  m_db=# CREATE TABLE aa(c1 int, c2 int);
  m_db=# ALTER TABLE aa RENAME TO test_alt1;

• Modify the schema of a table.

  -- Create the test_schema schema.
  m_db=# CREATE SCHEMA test_schema;
  -- Change the schema of the test_alt1 table to test_schema.
  m_db=# ALTER TABLE test_alt1 SET SCHEMA test_schema;
  -- Query table information.
  m_db=# SELECT schemaname,tablename FROM pg_tables WHERE tablename = 'test_alt1';
   schemaname  | tablename 
  -------------+-----------
   test_schema | test_alt1
  (1 row)

• Change the owner of a table.

  -- Create user test_user.
  m_db=# CREATE USER test_user PASSWORD 'XXXXXXXXXX';
  -- Change the owner of the test_alt1 table to test_user.
  m_db=# ALTER TABLE test_schema.test_alt1 OWNER TO test_user;
  -- Query.
  m_db=# SELECT tablename, schemaname, tableowner FROM pg_tables WHERE tablename = 'test_alt1';
   tablename | schemaname  | tableowner 
  -----------+-------------+------------
   test_alt1 | test_schema | test_user
  (1 row)

Examples of Modifying a Column

• Add columns.

  -- Create a table.
  m_db=# CREATE TABLE test_alt2(id INT,areaid INT);
  -- Add a column to the test_alt2 table.
  m_db=# ALTER TABLE test_alt2 ADD COLUMN name VARCHAR(20);
  -- Query.
  m_db=# \d test_alt2
             Table "public.test_alt1"
    Column  |         Type          | Modifiers 
  ----------+-----------------------+-----------
   id       | integer               | 
   areacode | integer               | 
   name     | varchar(20)           | collate utf8mb4_general_ci

• Delete a column.

  -- Delete the areaid column from test_alt2.
  m_db=# ALTER TABLE test_alt2 DROP COLUMN areaid;
  -- Query.
  m_db=# \d test_alt2
            Table "public.test_alt2"
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   id     | integer               | 
   name   | varchar(50)           | 

Examples of Modifying a Constraint

• Modify the default value of a column.

  -- Create a table.
  m_db=# CREATE TABLE test_alt3(pid INT, areaid CHAR(5), name VARCHAR(20));
  -- Modify the default value of id in the test_alt1 table.
  m_db=# ALTER TABLE test_alt3 ALTER COLUMN areaid SET DEFAULT '00000';
  -- Query.
  m_db=# \d test_alt3
                   Table "public.test_alt3"
   Column |         Type          |        Modifiers        
  --------+-----------------------+-------------------------
   pid    | integer               | 
   areaid | char(5)               | default '00000'::bpchar
   name   | varchar(20)           |

  -- Delete the default value of id.
  m_db=# ALTER TABLE test_alt3 ALTER COLUMN areaid DROP DEFAULT;
  -- Query.
  m_db=# \d test_alt3
            Table "public.test_alt3"
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   pid    | integer               | 
   areaid | char(5)               | 
   name   | varchar(20)           |

• Add a table-level constraint.
  Directly add a constraint.

  -- Add a primary key constraint to the table.
  m_db=# ALTER TABLE test_alt3 ADD CONSTRAINT pk_test3_pid PRIMARY KEY (pid);
  -- Query.
  m_db=# \d test_alt3
            Table "public.test_alt3"
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   pid    | integer               | not null
   areaid | integer               | 
   name   | varchar(20)           | 
  Indexes:
      "pk_test3_pid" PRIMARY KEY, btree (pid) TABLESPACE pg_default

Helpful Links

CREATE TABLE and DROP TABLE