openGauss ALTER TABLE Statement

The ALTER TABLE statement modifies tables, including modifying table definitions, renaming tables, renaming specified columns in tables, renaming table constraints, setting table schemas, enabling or disabling row-level security policies, and adding or updating multiple columns.

Syntax

Add a column to an existing table.

ALTER TABLE table_name
ADD column_name data_type;
Delete a column from an existing table.

ALTER TABLE table_name DROP COLUMN column_name;
Change the column type of a table.

ALTER TABLE table_name
MODIFY column_name data_type;

Add or delete a NOT NULL constraint to or from a column of an existing table.

ALTER TABLE table_name
ALTER column_name { SET | DROP } NOT NULL;
Rename a specified column in a table.

ALTER TABLE table_name RENAME column_name TO new_column_name;
Update columns.

ALTER TABLE table_name
MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
Rename a table,which does not affect stored data.

ALTER TABLE table_name
RENAME TO new_table_name;

Parameter Description

table_name

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

column_name

Specifies the name of a new or an existing column.

data_type

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

new_table_name

Specifies the new table name.

new_column_name

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

constraint_name

Specifies the name of a constraint.