DEV Community

wanglei
wanglei

Posted on

ALTER TABLE Statement

#opengauss

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;
Enter fullscreen mode Exit fullscreen mode

Delete a column from an existing table.

ALTER TABLE table_name DROP COLUMN column_name;
Enter fullscreen mode Exit fullscreen mode

Change the column type of a table.

ALTER TABLE  table_name
 MODIFY column_name data_type;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Rename a specified column in a table.

ALTER TABLE table_name RENAME  column_name TO new_column_name;
Update columns.
Enter fullscreen mode Exit fullscreen mode 
ALTER TABLE  table_name 
    MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
Enter fullscreen mode Exit fullscreen mode

Rename a table,which does not affect stored data.

A

LTER TABLE table_name 
    RENAME TO new_table_name;
Enter fullscreen mode Exit fullscreen mode

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:

openGauss=# SELECT * FROM customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
          3869 | hello         | Grace        |             |   1000
          3869 | hello         | Grace        |             |   1000
          3869 |               | Grace        |             |
          3869 | hello         |              |             |
          3869 | hello         |              |             |
               |               |              |             |
          6985 | maps          | Joes         |             |   2200
          9976 | world         | James        |             |   5000
          4421 | Admin         | Local        |             |   3000
          6881 | maps          | Lily         |             |   1000
          4320 | tpcds         | Lily         |             |   2000
(11 rows)
Enter fullscreen mode Exit fullscreen mode

Add a column.

Add a new column to the preceding table.

openGauss=# ALTER TABLE customer_t1 ADD date time;
Enter fullscreen mode Exit fullscreen mode

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

openGauss=# \d customer_t1
             Table "public.customer_t1"
    Column     |          Type          | Modifiers
---------------+------------------------+-----------
 c_customer_sk | integer                |
 c_customer_id | character(5)           |
 c_first_name  | character(6)           |
 c_last_name   | character(8)           |
 amount        | integer                |
 date          | time without time zone |
Change the data type of a column.
Enter fullscreen mode Exit fullscreen mode

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

openGauss=# ALTER TABLE customer_t1 MODIFY c_last_name character(12);
Enter fullscreen mode Exit fullscreen mode

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

openGauss=# \d customer_t1
             Table "public.customer_t1"
    Column     |          Type          | Modifiers
---------------+------------------------+-----------
 c_customer_sk | integer                |
 c_customer_id | character(5)           |
 c_first_name  | character(6)           |
 c_last_name   | character(12)          |
 amount        | integer                |
 date          | time without time zone |
Add a column constraint.
Enter fullscreen mode Exit fullscreen mode

Delete the rows where the c_customer_sk column is empty.

openGauss=# DELETE FROM customer_t1 WHERE c_customer_sk is NULL;
Enter fullscreen mode Exit fullscreen mode

Add a not-null constraint to the c_customer_sk column.

openGauss=# ALTER TABLE customer_t1 ALTER c_customer_sk SET NOT NULL;
Enter fullscreen mode Exit fullscreen mode

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

openGauss=# \d customer_t1
             Table "public.customer_t1"
    Column     |          Type          | Modifiers
---------------+------------------------+-----------
 c_customer_sk | integer                | not null
 c_customer_id | character(5)           |
 c_first_name  | character(6)           |
 c_last_name   | character(12)          |
 amount        | integer                |
 date          | time without time zone |
Enter fullscreen mode Exit fullscreen mode

Change a column name.
Change the column name from date to purchase date.

openGauss=# ALTER TABLE customer_t1  RENAME  date TO purchase_date;
Enter fullscreen mode Exit fullscreen mode

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

openGauss=# \d customer_t1
             Table "public.customer_t1"
    Column     |          Type          | Modifiers
---------------+------------------------+-----------
 c_customer_sk | integer                | not null
 c_customer_id | character(5)           |
 c_first_name  | character(6)           |
 c_last_name   | character(12)          |
 amount        | integer                |
 purchase_date | time without time zone |
Enter fullscreen mode Exit fullscreen mode

Delete a column.

Delete the purchase_date column.

openGauss=# ALTER TABLE customer_t1 DROP purchase_date;
Enter fullscreen mode Exit fullscreen mode

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

openGauss=# SELECT * FROM customer_t1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
          3869 | hello         | Grace        |             |   1000
          3869 | hello         | Grace        |             |   1000
          3869 |               | Grace        |             |
          3869 | hello         |              |             |
          3869 | hello         |              |             |
          6985 | maps          | Joes         |             |   2200
          9976 | world         | James        |             |   5000
          4421 | Admin         | Local        |             |   3000
          6881 | maps          | Lily         |             |   1000
          4320 | tpcds         | Lily         |             |   2000
(10 rows)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

dwayne_mcdaniel profile image

PHP[TEK] 2024: Pioneering the Future of Web Development in Chicago

Dwayne McDaniel -

jetthoughts_61 profile image

Tips to attract readers to read your post

JT Dev -

abdullah-dev0 profile image

Understanding Suspense and Suspended Components in React

Abdullah -

alexreis profile image

O básico de Threads no Java

Alex Reis -