On this page

Show all

Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-2.x)/ SQL Reference/ SQL Syntax/ ALTER TABLE

ALTER TABLE

Updated on 2025-02-27 GMT+08:00

View PDF

Description

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

Precautions

The owner of a table, users granted with the ALTER permission on the table, or users granted with 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 a 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.

Auto-increment columns cannot be added, or a column whose DEFAULT value contains the nextval() expression cannot be added.
Row-level security cannot be enabled for foreign tables and temporary tables.
When you delete a PRIMARY KEY constraint by constraint name, the NOT NULL constraint is not deleted. If necessary, manually delete the NOT NULL constraint.
When JDBC is used, the DEFAULT value can be set through PrepareStatement.
If you add a column using ADD COLUMN, all existing rows in the table are initialized to the column's default value (NULL if no DEFAULT value is specified).
If no DEFAULT value is specified for the new column, NULL is used, and no full table update is triggered.

If the new column has a DEFAULT value, the column must meet all the following requirements. Otherwise, the entire table is updated, leading to additional overheads and affecting online services.

1. The data type is BOOL, BYTEA, SMALLINT, BIGINT, SMALLINT, INTEGER, NUMERIC, FLOAT, DOUBLE PRECISION, CHAR, VARCHAR, TEXT, TIMESTAMPTZ, TIMESTAMP, DATE, TIME, TIMETZ, or INTERVAL.

2. The length of the DEFAULT value of the added column cannot exceed 128 bytes.

3. The DEFAULT value of the added column does not contain the volatile function.

4. The DEFAULT value is required and cannot be NULL.

If you are not sure whether condition 3 is met, check whether the provolatile attribute of the function in the PG_RPOC system catalog is 'v'.

Syntax

Modify the definition of a table.
```
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    action [, ... ];
```
- There are several clauses of column_clause:
```
ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]    
| MODIFY column_name data_type    
| MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
| MODIFY column_name [ CONSTRAINT constraint_name ] NULL
| 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 }
```
  NOTE:
  ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
  Adds a column to a table. If you add a column using ADD COLUMN, all existing rows in the table are initialized to the column's default value (NULL if no DEFAULT value is specified).
  
  ADD ( { column_name data_type } [, ...] )
  Adds columns in the 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 the 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.
  
  DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
  Drops a column from a table. Indexes and constraints 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 a view.
  
  The DROP COLUMN statement does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent INSERT and UPDATE operations on the table will store a NULL value for the column. Therefore, dropping a column takes a short period of time but does not immediately release the tablespace on the disks, because the space occupied by the dropped column is not recycled. The space will be recycled when VACUUM is executed.
  
  ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
  Alters the type of a column in a table. Indexes and simple table constraints on the column will automatically use the new data type by reparsing the originally supplied expression.
  
  If the original data type of a column and the modified data type are binary compatible, you do not need to rewrite the entire table when running this statement. In other scenarios, the entire table is rewritten. You can check whether the original type and target type are binary compatible in the PG_CAST system catalog. If castmethod is 'b', they are binary compatible. For example, if the data type of the source table is text and is converted to int, table rewriting is triggered. If it is converted to clob, table rewriting is not triggered. If table rewriting is triggered, the deleted space on the table is recycled immediately.
  
  Running this command will clear the statistics of this column. You are advised to collect the statistics of this column again after the modification.
  
  ALTER [ COLUMN ] column_name { SET DEFAULT expression | DROP DEFAULT }
  Sets or drops 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.
  
  ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
  Alters 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 non-NULL values.
  
  ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer
  Specifies the per-column statistics-collection target for subsequent ANALYZE operations. The target can be set in the range from 0 to 10000. Set it to –1 to revert to using the default system statistics target.
  
  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 a table, while n_distinct_inherited affects the statistics of the 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 clause specifies whether this column is held inline or in a secondary TOAST table, and whether the data should be compressed. Only row-store tables can be set. SET STORAGE itself does not change anything in the table. It sets the strategy to be pursued during future table updates.
  column_constraint is as follows:
```
[ CONSTRAINT constraint_name ]
    { NOT NULL |
      NULL |
      CHECK ( expression ) |
      DEFAULT default_expr  |
      GENERATED ALWAYS AS ( generation_expr ) STORED |
      UNIQUE index_parameters |
      PRIMARY KEY index_parameters |
      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 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 |
      PARTIAL CLUSTER KEY ( column_name [, ... ]  }
      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 ]
```
  index_parameters is as follows:
```
[ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ USING INDEX TABLESPACE tablespace_name ]
```

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

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;
```
NOTE:
- The schema setting 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 the CREATE permission 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 the CREATE permission on the table's schema. These restrictions enforce that the user can only rebuild and delete the table. However, a system administrator can alter the ownership of any table anyway.
- 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 statement. This is useful with large tables, since only one pass over the tables need be made.
- Adding a CHECK or NOT NULL constraint will scan the table to validate that existing rows meet the constraint.
- Adding a column with a non-null default or changing the type of an existing column will rewrite the entire table. Rewriting a large table may take much time and temporarily needs doubled disk space.

Add columns.

ALTER TABLE [ IF EXISTS ] table_name
    ADD ( { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );

Update columns.

ALTER TABLE [ IF EXISTS ] table_name 
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );

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 that you need to modify.

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.
constraint_name
Specifies the name of an existing constraint to drop.
index_name
Specifies the name of an index.
storage_parameter
Specifies the name of a storage parameter.

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 this function 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.
The following option is added to fix optimizer statistics:
- min_tuples (float8 type)
  Default value: 0
  
  The optimizer selects the larger value of the estimated statistics and the parameter to calculate the data volume based on the estimation table of statistics.
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.
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.
USING expression
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 the old to new type.

NOTE:

USING in ALTER TYPE can specify any expression involving the old values of the row; that is, it can refer to any columns other than the one being cast. This allows general casting 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 ALTER TYPE, and then use SET DEFAULT to add a suitable new default. Similar considerations apply to indexes and constraints involving the column.
NOT NULL | NULL
Sets whether the column allows null values.
ENABLE
Specifies that the constraint is enabled. By default, the constraint is enabled.
integer
Specifies the constant value of a signed integer. When using PERCENT, the range of integer is from 0 to 100.
attribute_option
Specifies an attribute option.
PLAIN | EXTERNAL | EXTENDED | MAIN
Specifies a column-store mode.
- PLAIN must be used for fixed-length values (such as integers). It must be inline and 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.
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.

A CHECK constraint specified as a column constraint should reference only the column's value, while an expression 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 to a column.

The data type of the default expression must match that 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.
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.
NOTE:
- The generation expression cannot refer to 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, aggregate function, or window function can be used. The function called by the generation expression can only be an immutable function.
- 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. Delete the ordinary column that the generated column depends on. The generated column is automatically deleted. The type of the column on which the generated column depends cannot be changed.
- The generated column cannot be directly written. 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.
- Columns cannot be generated for column-store tables and memory-optimized tables (MOTs). In foreign tables, only postgres_fdw supports generated columns.
UNIQUE index_parameters
UNIQUE ( column_name [, ... ] ) index_parameters

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

Specifies that a column or columns of a table can contain only unique (non-duplicate) and non-NULL values.
REFERENCES reftable [ ( refcolum ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)

The foreign key constraint requires 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 refcolum 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.
- MATCH PARTIAL: This option is not supported currently.
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 reported. If the constraint is deferrable and there are still any referenced rows, this error will occur when the constraint is checked.
- RESTRICT: When a foreign key is deleted or updated, an error indicating that the foreign key constraint is violated is created. It is the same as NO ACTION except that the constraint is not deferrable.
- 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.
DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE
Sets whether the constraint can be deferrable.
- DEFERRABLE: deferrable to the end of the transaction and checked using SET CONSTRAINTS.
- 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.
  NOTE:
  
  Ustore tables do not support the DEFERRABLE and INITIALLY DEFERRED constraints.
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.

Examples of Altering a Table

Rename a table.

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

Alter the schema of a table.

-- Create test_schema.
openGauss=# CREATE SCHEMA test_schema;
-- Change the schema of the test_alt1 table to test_schema.
openGauss=# ALTER TABLE test_alt1 SET SCHEMA test_schema;
-- Query table information.
openGauss=# 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 test_user.
openGauss=# CREATE USER test_user PASSWORD 'XXXXXXXXXX';
-- Change the owner of the test_alt1 table to test_user.
openGauss=# ALTER TABLE IF EXISTS test_schema.test_alt1 OWNER TO test_user;
-- Query.
openGauss=# SELECT tablename, schemaname, tableowner FROM pg_tables WHERE tablename = 'test_alt1';
 tablename | schemaname  | tableowner 
-----------+-------------+------------
 test_alt1 | test_schema | test_user
(1 row)

Alter the tablespace of a table.

-- Create the tbs_data1 tablespace.
openGauss=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';
-- Change the tablespace of the test_alt1 table to tbs_data1.
openGauss=# ALTER TABLE test_schema.test_alt1 SET TABLESPACE tbs_data1;
-- Query.
openGauss=# SELECT tablename, tablespace FROM pg_tables WHERE tablename = 'test_alt1';
 tablename | tablespace 
-----------+------------
 test_alt1 | tbs_data1
(1 row)

-- Drop.
openGauss=# DROP TABLE test_schema.test_alt1; 
openGauss=# DROP TABLESPACE tbs_data1; 
openGauss=# DROP SCHEMA test_schema; 
openGauss=# DROP USER test_user;

Examples of Changing a Column

Change column names.

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

Add a column.

-- Add a column to the test_alt1 table.
openGauss=# ALTER TABLE IF EXISTS test_alt2 ADD COLUMN name VARCHAR(20);
-- Query.
openGauss=# \d test_alt2
           Table "public.test_alt1"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 id       | integer               | 
 areacode | integer               | 
 name     | character varying(20) |

Alter the data type of a column.

-- Change the type of the name column in the test_alt1 table.
openGauss=# ALTER TABLE test_alt1 MODIFY name VARCHAR(50);
-- Query.
openGauss=# \d test_alt1
          Table "public.test_alt2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 areaid | integer               | 
 name   | character varying(50) |

-- Change the type of the name column in the test_alt1 table.
openGauss=# ALTER TABLE test_alt2 ALTER COLUMN name TYPE VARCHAR(25);
-- Query.
openGauss=# \d test_alt2
          Table "public.test_alt2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 areaid | integer               | 
 name   | character varying(25) |

Drop a column.

-- Drop the areaid column from test_alt1.
openGauss=# ALTER TABLE test_alt2 DROP COLUMN areaid;
-- Query.
openGauss=# \d test_alt2
          Table "public.test_alt2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(25) |

Modify the column-store mode.

-- View table details.
openGauss=# \d+ test_alt2
                              Table "public.test_alt2"
 Column |         Type          | Modifiers | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+--------------+-------------
 id     | integer               |           | plain    |              | 
 name   | character varying(25) |           | extended |              | 
Has OIDs: no
Options: orientation=row, storage_type=USTORE

-- Change the storage mode of the name column in the test_alt2 table.
openGauss=# ALTER TABLE test_alt2 ALTER COLUMN name SET STORAGE PLAIN;

-- Query.
openGauss=# \d+ test_alt2
                             Table "public.test_alt2"
 Column |         Type          | Modifiers | Storage | Stats target | Description 
--------+-----------------------+-----------+---------+--------------+-------------
 id     | integer               |           | plain   |              | 
 name   | character varying(25) |           | plain   |              | 
Has OIDs: no
Options: orientation=row, storage_type=USTORE

-- Drop.
openGauss=# DROP TABLE test_alt2;

Examples of Changing a Constraint

Add a NOT NULL constraint to a column.

-- Create a table.
openGauss=# CREATE TABLE test_alt3(pid INT, areaid CHAR(5), name VARCHAR(20));
-- Add a NOT NULL constraint to pid.
openGauss=# ALTER TABLE test_alt3 MODIFY pid NOT NULL;
-- Query.
openGauss=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | not null
 areaid | character(5)          | 
 name   | character varying(20) |

Cancel the NOT NULL constraint on a column.

openGauss=# ALTER TABLE test_alt3 MODIFY pid NULL;
-- Query.
openGauss=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | 
 areaid | character(5)          | 
 name   | character varying(20) |

Alter the default value of a column.

-- Alter the default value of id in the test_alt1 table.
openGauss=# ALTER TABLE test_alt3 ALTER COLUMN areaid SET DEFAULT '00000';
-- Query.
openGauss=# \d test_alt3
                 Table "public.test_alt3"
 Column |         Type          |        Modifiers        
--------+-----------------------+-------------------------
 pid    | integer               | 
 areaid | character(5)          | default '00000'::bpchar
 name   | character varying(20) |

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

Add a table-level constraint.

Directly add a constraint.

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

Create an index and then add a constraint.

-- Create a table.
openGauss=# CREATE TABLE test_alt4(c1 INT, c2 INT);
-- Create an index.
openGauss=# CREATE UNIQUE INDEX pk_test4_c1 ON test_alt4(c1);
-- Associate the created index when adding a constraint.
openGauss=# ALTER TABLE test_alt4 ADD CONSTRAINT pk_test4_c1 PRIMARY KEY USING INDEX pk_test4_c1;
-- Query.
openGauss=# \d test_alt4
   Table "public.test_alt4"
 Column |  Type   | Modifiers 
--------+---------+-----------
 c1     | integer | not null
 c2     | integer | 
Indexes:
    "pk_test4_c1" PRIMARY KEY, btree (c1) TABLESPACE pg_default
-- Drop.
openGauss=# DROP TABLE test_alt4;

Drop a table-level constraint.

-- Drop a constraint.
openGauss=# ALTER TABLE test_alt3 DROP CONSTRAINT IF EXISTS pk_test3_pid;
-- Query.
openGauss=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | not null
 areaid | integer               | 
 name   | character varying(20) | 

-- Drop.
openGauss=# DROP TABLE test_alt3;