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 [, ... ] )
      | AUTO_INCREMENT [ = ] value
      | COMMENT [ = ] 'string'
      | [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] charset_name] [ [ DEFAULT ] COLLATE [ = ] collation_name]
      | CONVERT TO {CHARACTER SET | CHAR SET | CHARSET} charset_name [COLLATE collation_name ]
      | ADD index_clause
      | DROP {INDEX | KEY} index_name
      | DROP PRIMARY KEY
      | DROP FOREIGN KEY fk_name
      | 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.

  • RESET ( storage_parameter [, ... ] )

    Resets one or more storage parameters to their defaults.

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

  • AUTO_INCREMENT [ = ] value

    Sets the next auto-increment value of the auto-increment column. The configured value takes effect only when it is greater than the current auto-increment counter.

    The value must be a non-negative integer and cannot be greater than 2¹²⁷ – 1.

  • [ [ 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.

  • CONVERT TO {CHARACTER SET | CHAR SET | CHARSET} charset [ COLLATE collation ]

    Changes the default character set and default collation of a table to the specified values, sets the character set and collation of all columns with character type to the specified value and converts the data in the column to new character set encoding.

    This syntax cannot be used to change the collation of a partitioned table.

  • DROP {INDEX | KEY} index_name

    Deletes an index from a table. INDEX and KEY are synonyms.

  • DROP PRIMARY KEY

    Deletes a primary key constraint from a table.

  • DROP FOREIGN KEY fk_name

    Deletes a specified foreign key constraint.

  • 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 [ ... ] ] [ FIRST | AFTER column_name ]   
    | ADD ({ column_name data_type [ compress_mode ]} [, ...] )
    | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
    | MODIFY column_name [ CONSTRAINT constraint_name ] NULL
    | MODIFY [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET}  charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [ FIRST | AFTER column_name ]
    | CHANGE [ COLUMN ] old_column_name new_column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET}  charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [ FIRST | AFTER column_name ]
    | DROP [ COLUMN ]  column_name [ RESTRICT | CASCADE ]    
    | ALTER [ COLUMN ] column_name { SET DEFAULT default_expr | DROP DEFAULT }  
    | MODIFY column_name data_type [GENERATED ALWAYS] AS generation_expr [STORED]

    

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

      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). FIRST | AFTER column_name indicates that a column is added to a certain position.

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

      Adds multiple columns to a table.

    • MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] )

      Modifies the data type of an existing column in a table. Running this command will clear the statistics of this column. You are advised to collect the statistics of this column again after the modification.

    • MODIFY [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [FIRST | AFTER column_name]
      • Replaces the definition of existing field in a table with a new definition. The indexes and independent object constraints, such as primary keys, unique key and CHECK constraints in the previous field are not deleted. The [FIRST | AFTER column_name] syntax indicates that the position of a column in a table is changed when the column definition is modified.
      • The partition key information cannot be modified. That is, column_name cannot be specified as the partition key.
      • If a column whose data type or collation rule is modified is referenced by a generated column, the data in the generated column is regenerated.
      • When a column is modified, objects (such as indexes, independent object constraints, views, and triggers) dependent on the column are rebuilt. If the definition of the modified column violates the constraints of this type of object, the modification fails. For example, the data type of the column that is used as the view result column cannot be modified. Evaluate the impact before modification.
      • If a column is called by some objects, modifying the column does not affect these objects. After the columns are modified, these objects may be unavailable. Evaluate the impact before modification.
      • Changing the character set or collation of a column converts the data in the column to the new character set for encoding.
      • Running this command will clear the statistics of this column. You are advised to collect the statistics of this column again after the modification.
    • CHANGE [ COLUMN ] old_column_name new_column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [FIRST | AFTER column_name]
      • Replaces the definition and name of existing column in a table with a new definition and name. The new column name must be different from the original one. Indexes and independent object constraints (such as primary keys, unique keys, and CHECK constraints) on original columns are not deleted. The [FIRST | AFTER column_name] syntax indicates that the position of a column in a table is changed when the name and definition of the column are modified.
      • The data type and collation rule of partition key columns cannot be modified. The data type and collation of columns referenced by rules cannot be modified.
      • If a column whose data type or collation rule is modified is referenced by a generated column, the data in the generated column is regenerated.
      • When a column is modified, objects (such as indexes, independent object constraints, views, and triggers) dependent on the column are rebuilt. If the definition of the modified column violates the constraints of this type of object, the modification fails. For example, the data type of the column that is used as the view result column cannot be modified. Evaluate the impact before modification.
      • If a column is called by some objects, modifying the column does not affect these objects. After the column names are modified, these objects may be unavailable. Evaluate the impact before modification.
      • Changing the character set or collation of a column converts the data in the column to the new character set for encoding.
    • 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.

    • MODIFY column_name data_type [GENERATED ALWAYS] AS generation_expr [STORED]

      Modifies the data type and expression of a generated column. For details, see [GENERATED ALWAYS] AS ( generation_expr ) [STORED].

    • column_constraint is as follows:

      AUTO_INCREMENT
      | COMMENT 'string'
      |[ CONSTRAINT constraint_name ]
          { NOT NULL |
            NULL |
            CHECK ( expression ) |
            DEFAULT default_expr  |
            ON UPDATE update_expr |
            [GENERATED ALWAYS] AS ( generation_expr ) [STORED] |
            UNIQUE [KEY] index_parameters |
            [PRIMARY] KEY index_parameters |
            REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL  | MATCH SIMPLE ]
                [ ON DELETE action ] [ ON UPDATE action ] }

    • 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']|
          PRIMARY KEY [ USING access_method ] [index_name] ( { column_name [ ASC | DESC ] }[, ... ] ) index_parameters [USING access_method] [comment 'string']
          }
          FOREIGN KEY [ idx_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
             [ MATCH FULL  | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }  

    The value of access_method is as follows:

    BTREE

    index_parameters is as follows:

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

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:

  • For a foreign key constraint, if constraint_name and index_name are specified at the same time, constraint_name is used as the index name.
  • If both constraint_name and index_name are specified for the UNIQUE KEY constraint, the value of index_name will be used as the 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.

  The following storage parameters can be modified using the ALTER TABLE SET/RESET syntax:

  • parallel_workers

    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 type: int

    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.

• 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. Row-store tables do not support compression. This is unavailable in the M-compatible scenario.

• 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.
  • When the default expression is not enclosed in parentheses, the following content can be specified: constants, numeric constants with positive and negative signs, and update_expr.
  • When the default expression is enclosed in parentheses, the following content can be specified: constants, numeric constants with positive and negative signs, update_expr, CURRENT_TIME/CURTIME functions, and CURRENT_DATE/CURDATE functions. (CURRENT_TIME/CURRENT_DATE can be called without parentheses.)
  • 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.
• [GENERATED ALWAYS] AS ( generation_expr ) [STORED]

  This clause creates a column as a generated column. The value of the generated column is calculated by generation_expr when data is written (inserted or updated). STORED indicates that the value of the generated column is stored as an ordinary column.

  

  • The STORED keyword can be omitted, which has the same semantics as not omitting STORED.
  • The generated expression cannot reference data other than the current row in any way. The generation expression cannot reference other generation columns or system columns. The generation expression cannot return a result set. No subquery or aggregate function can be used. Functions called by generated expressions can only be immutable functions.
  • Default values cannot be specified for generated columns.
  • The generated column cannot be used as a part of the partition key.
  • Do not specify the generated column and the CASCADE, SET NULL, and SET DEFAULT actions of the ON UPDATE constraint at the same time. Do not specify the generated column and the SET NULL, and SET DEFAULT actions of the ON DELETE constraint at the same time.
  • The method of modifying and deleting generated columns is the same as that of ordinary columns. If you delete an ordinary column that a generated column depends on, the generated column is automatically deleted. The type of the column on which the generated column depends cannot be changed.
  • Data cannot be written directly to a generated column. In the INSERT or UPDATE statement, values cannot be specified for generated columns, but the keyword DEFAULT can be specified.
  • The permission control for generated columns is the same as that for ordinary columns.
• AUTO_INCREMENT

  Specifies an auto-increment column.

  For details, see •AUTO_INCREMENT.

• 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 the designated column or columns must have unique values across the entire table.

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

  Specifies that the designated column or columns must have unique values across the entire table.

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

  index_name indicates the index name.

  

  If both constraint_name and index_name are specified for the UNIQUE KEY constraint, the value of index_name will be 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.

• Column-level FOREIGN KEY constraint: REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]

  Table-level FOREIGN KEY constraint: FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]

  When the table-level and column-level REFERENCES constraints are used, no error message is displayed. However, the constraints actually do not take effect.

  The foreign key constraints require that the group consisting of one or more columns in the new table should contain and match only the referenced column values in the referenced table. If refcolumn is omitted, the primary key of reftable is used. The referenced column should be the only column or primary key in the referenced table. A foreign key constraint cannot be defined between a temporary table and a permanent table.

  There are three types of matching between a reference column and a referenced column:

  • MATCH FULL: A column with multiple foreign keys cannot be NULL unless all foreign key columns are NULL.
  • MATCH SIMPLE (default): Any unexpected foreign key column can be NULL.

  In addition, when certain operations are performed on the data in the referenced table, the operations are performed on the corresponding columns in the new table. The ON DELETE clause specifies the operations to be executed after a referenced row in the referenced table is deleted. The ON UPDATE clause specifies the operation to be performed when the referenced column data in the referenced table is updated. Possible responses to the ON DELETE and ON UPDATE clauses are as follows:

  • NO ACTION (default): When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created.
  • RESTRICT: When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created. The value is the same as that of NO ACTION.
  • CASCADE: deletes any row that references the deleted row from the new table, or update the column value of the referenced row in the new table to the new value of the referenced column.
  • SET NULL: sets the referenced columns to NULL.
  • SET DEFAULT: sets the referenced columns to their default values.
  

  • The GUC parameter foreign_key_checks controls the integrity check of foreign key constraints. foreign_key_checks can be set to on (default) or off, indicating that the integrity check of foreign key constraints is enabled or disabled, respectively.
• 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.

• FIRST

  Adds or changes the column to the first place.

• AFTER column_name

  Adds or changes the column after the column specified by column_name.

  

  The position of a column in a table that depends on rules cannot be changed (including the addition and change of the column position).

• 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

• Change column names.

  -- Create a table.
  m_db=# CREATE TABLE test_alt2(c1 INT,c2 INT);
  -- Change column names.
  m_db=# ALTER TABLE test_alt2 CHANGE COLUMN c1 id INT;
  m_db=# ALTER TABLE test_alt2 CHANGE COLUMN c2 areaid INT; 
  -- Query.
  m_db=# \d test_alt2
     Table "public.test_alt2"
   Column |  Type   | Modifiers 
  --------+---------+-----------
   id     | integer | 
   areaid | integer | 

• Add columns.

  -- 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)           |

• Add the AUTO_INCREMENT column.

  -- Create a table and add an auto-increment column.
  m_db=# CREATE TABLE test_autoinc(col1 int);
  
  -- Insert a data record.
  m_db=# INSERT INTO test_autoinc(col1) VALUES(1);
  
  -- Add a local auto-increment column, which starts from 1.
  m_db=# ALTER TABLE test_autoinc ADD COLUMN col int AUTO_INCREMENT;
  
  m_db=# SELECT col,col1 FROM test_autoinc ORDER BY 2,1;
   col | col1
  -----+------
     1 |    1
  (1 row)
  
  -- Set the next auto-increment value to 10.
  m_db=# ALTER TABLE test_autoinc AUTO_INCREMENT = 10;
  
  -- Enter NULL to trigger auto-increment. The auto-increment value is 10.
  m_db=# INSERT INTO test_autoinc(col, col1) VALUES(NULL,2);
  
  -- Enter 0 to trigger auto-increment. The auto-increment value is 11.
  m_db=# INSERT INTO test_autoinc(col, col1) VALUES(0,3);
  
  m_db=# SELECT col,col1 FROM test_autoinc ORDER BY 2,1;
   col | col1
  -----+------
     1 |    1
    10 |    2
    11 |    3
  (3 rows)

• Modify the data type of a column.

  -- Change the type of the name column in the test_alt2 table.
  m_db=# ALTER TABLE test_alt2 MODIFY name VARCHAR(50);
  -- Query.
  m_db=# \d test_alt2
            Table "public.test_alt1"
   Column |         Type          | Modifiers 
  --------+-----------------------+-----------
   c1     | integer               | 
   c2     | integer               | 
   name   | varchar(50)           | 

• 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

Examples

-- Create an index and a table.
m_db=# CREATE TABLE t01(c1 int PRIMARY KEY, c2 int UNIQUE);
m_db=# CREATE INDEX idx1 ON t01(c1);
m_db=# CREATE INDEX idx2 ON t01(c1,c2);
m_db=# \d+ t01
                         Table "public.t01"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 c1     | integer | not null  | plain   |              | 
 c2     | integer |           | plain   |              | 
Indexes:
    "t01_pkey" PRIMARY KEY, ubtree ON t01 (c1) WITH (storage_type=USTORE) TABLESPACE pg_default
    "t01_c2_key" UNIQUE CONSTRAINT, ubtree ON t01 (c2) WITH (storage_type=USTORE) TABLESPACE pg_default
    "idx1" ubtree ON t01 (c1) WITH (storage_type=USTORE) TABLESPACE pg_default
    "idx2" ubtree ON t01 (c1, c2) WITH (storage_type=USTORE) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE, collate=1537, segment=off

-- Delete an index from a table.
m_db=# ALTER TABLE t01 DROP INDEX idx1, DROP INDEX idx2;
m_db=# ALTER TABLE t01 DROP INDEX t01_pkey, DROP INDEX t01_c2_key;
m_db=# \d+ t01
                         Table "public.t01"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 c1     | integer | not null  | plain   |              | 
 c2     | integer |           | plain   |              | 
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE, collate=1537, segment=off

m_db=# DROP TABLE t01;

Helpful Links

CREATE TABLE and DROP TABLE