ALTER TABLE Statement
Tags: opengauss
Published: 2023-03-07 03:47:46

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yobvt0ne6xrjrih4u2qw.png)

**·** Delete a column from an existing table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nruv20l2ss4409niaqom.png)

**·** Change the column type of a table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c2dhjkfid46n2e9wx7a5.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d2ya59it4luu5y8m2hey.png)

**·** Rename a specified column in a table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ueqbv60jnwr6bn750bgp.png)

**·** 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yabn8kcrssqu2wc9q8ln.png)

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

**Examples**

The data in the customer_t1 table is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cvjkf9wdm5n3fw197r4t.png)

**·** Add a column.

Add a new column to the preceding table.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gbclws5jdq3ot9h8jjsu.png)

The following shows the structure of the customer_t1 table. The date column is added successfully.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r26q4rt3obbuzdu1t2b4.png)

**·** Change the data type of a column.

Change the data type of the c column from character(8) to character(12).

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y8e30812lew4gxra3vs3.png)

Query the structure of the customer_t1 table. The data type of the c_last_name column is changed successfully.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ripk7md5qpe2j14cazh6.png)

**·** Add a column constraint.

Delete the rows where the c_customer_sk column is empty.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1oahlo746xjdjryngi1j.png)

Add a not-null constraint to the c_customer_sk column.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hqb4ketxtzckl4qrvxvw.png)

Query the structure of the customer_t1 table. The constraint is successfully added to the c_customer_sk column.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a5cyjgii30r13abbs9v9.png)

**·** Change a column name.

Change the column name from date to purchase date.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7aavqiaito4fkdderf2z.png)

Query the structure of the customer_t1 table. The name of the date column is changed successfully.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yx1n0soce7qkvxqja39x.png)

**·** Delete a column.

Delete the purchase_date column.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/twgx4lu3gzpduqvlxfy3.png)

After deletion, the data in the customer_t1 table is as follows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uxkykxgvk8lcx3lbx5t0.png)