Summary: in this tutorial, you will learn how to use the PostgreSQL RENAME COLUMN clause in the ALTER TABLE statement to rename one or more columns of a table.
Introduction to PostgreSQL RENAME COLUMN clause
To rename a column of a table, you use the ALTER TABLE statement with RENAME COLUMN clause as follows:
1 2 | ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name; |
In this statement:
- First, specify the table, which contains the column you want to rename, after the
ALTER TABLEclause. - Second, provide the column name after the
RENAME COLUMNclause. - Third, give the new column name after the
TOkeyword.
The COLUMN keyword in the statement in optional therefore you can omit it as shown in the following statement:
1 2 | ALTER TABLE table_name RENAME column_name TO new_column_name; |
For some reasons, if you try to rename a non-existing column, PostgreSQL will issue an error. Unfortunately that PostgreSQL does not provide the IF EXISTS option for the RENAME CLAUSE.
To rename multiple columns, you add each RENAME clause for each column, each clause is separated by a comma (,):
1 2 3 4 | ALTER TABLE table_name RENAME column_name_1 TO new_column_name_1, RENAME column_name_2 TO new_column_name_2, ...; |
If you rename a column that references by other database objects such as views, foreign key constraints, triggers, stored procedures, etc., PostgreSQL will also change all of its dependent objects.
PostgreSQL RENAME COLUMN examples
We will create two new tables customers and customer_groups for demonstration.
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE customer_groups ( id serial PRIMARY KEY, name VARCHAR NOT NULL ); CREATE TABLE customers ( id serial PRIMARY KEY, name VARCHAR NOT NULL, phone VARCHAR NOT NULL, email VARCHAR, group_id INT, FOREIGN KEY (group_id) REFERENCES customer_groups (id) ); |
In addition, we create a new view named customer_data based on the customers and customer_groups tables.
1 2 3 4 5 6 7 8 | CREATE VIEW customer_data AS SELECT c.id, c.name, g.name customer_group FROM customers c INNER JOIN customer_groups g ON g.id = c.group_id; |
The following statement renames the email column of the customers table to contact_email:
1 2 | ALTER TABLE customers RENAME COLUMN email TO contact_email; |
Let’s try to rename a column that has dependent objects such as the name column of the customer_groups table:
1 2 | ALTER TABLE customer_groups RENAME COLUMN name TO group_name; |
Now, you can check whether the change of the name column was cascaded to the customer_data view:
1 2 3 4 5 6 7 8 9 10 11 12 13 | test=# \d+ customer_data; View "public.customer_data" Column | Type | Modifiers | Storage | Description ----------------+-------------------+-----------+----------+------------- id | integer | | plain | name | character varying | | extended | customer_group | character varying | | extended | View definition: SELECT c.id, c.name, g.group_name AS customer_group FROM customers c JOIN customer_groups g ON g.id = c.group_id; |
As you can see in the view definition, the name column has been changed to group_name.
The following illustrates how to rename two columns name and phone of the customers table to customer_name and contact_phone respectively in a single statement:
1 2 3 | ALTER TABLE customers RENAME COLUMN name TO customer_name, RENAME COLUMN phone TO contact_phone. |
In this tutorial, you have learned how to use the PostgreSQL RENAME COLUMN clause in the ALTER TABLE statement to change the name of one or more columns.