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.

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)
  • Add a column.

    Add a new column to the preceding table.

    openGauss=# ALTER TABLE customer_t1 ADD date time;
    

    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.

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

    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.

    Delete the rows where the c_customer_sk column is empty.

    openGauss=# DELETE FROM customer_t1 WHERE c_customer_sk is NULL;
    

    Add a not-null constraint to the c_customer_sk column.

    openGauss=# ALTER TABLE customer_t1 ALTER c_customer_sk SET NOT NULL;
    

    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 |
    
  • Change a column name.

    Change the column name from date to purchase date.

    openGauss=# ALTER TABLE customer_t1  RENAME  date TO purchase_date;
    

    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 |
    
  • Delete a column.

    Delete the purchase_date column.

    openGauss=# ALTER TABLE customer_t1 DROP purchase_date;
    

    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)
    
Feedback
编组 3备份
    openGauss 2024-04-21 00:47:23
    cancel