PostgreSQL RENAME COLUMN: Renaming a column
Summary: in this tutorial, you will learn how to use the PostgreSQL
RENAME COLUMN
clause in theALTER 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 theALTER TABLE
statement withRENAME COLUMN
clause as follows:
ALTER TABLE table_nameRENAME COLUMN column_name TO new_column_name;
In this statement:
- First, specify the name of the table that contains the column which you want to rename after the
ALTER TABLE
clause. - Second, provide the name of the column that you want to rename after the
RENAME COLUMN
keywords. - Third, specify the new name for the column after the
TO
keyword.
TheCOLUMN
keyword in the statement is optional therefore you can omit it like this:
ALTER TABLE table_nameRENAME column_nameTO new_column_name;
For some reason, if you try to rename a column that does not exist, PostgreSQL will issue an error. Unfortunately, PostgreSQL does not support the IF EXISTS
option for theRENAME
clause.
To rename multiple columns, you execute theALTER TABLE RENAME COLUMN
statement multiple times, one column at a time:
ALTER TABLE table_nameRENAME column_name1TO new_column_name1;ALTER TABLE table_nameRENAME column_name2TO new_column_name2;
If you rename a column referenced by other database objects such asviews,foreign key constraints,triggers, and stored procedures, PostgreSQL will automatically change the column name in the dependent objects.
PostgreSQL RENAME COLUMN examples
Let’s take some examples of using theALTER TABLE RENAME COLUMN
statement to rename a column.
Setting up sample tables
First,create two new tablescustomers
andcustomer_groups
.
CREATE TABLE customer_groups ( idserial PRIMARY KEY, name VARCHAR NOT NULL);CREATE TABLE customers ( idserial PRIMARY KEY, name VARCHAR NOT NULL, phoneVARCHAR NOT NULL, emailVARCHAR, group_idINT, FOREIGN KEY (group_id)REFERENCES customer_groups (id));
Then,create a new view namedcustomer_data
based on thecustomers
andcustomer_groups
tables.
CREATE VIEW customer_data ASSELECT c.id, c.name, g.name customer_groupFROM customers c INNER JOIN customer_groups gON g.id= c.group_id;
1) Renaming one column example
The following statement uses theALTER TABLE RENAME COLUMN
statement to rename theemail
column of thecustomers
table tocontact_email
:
ALTER TABLE customersRENAME COLUMN emailTO contact_email;
2) Renaming a column that has dependent objects example
This example uses theALTER TABLE RENAME COLUMN
statement to change thename
column of thecustomer_groups
table togroup_name
:
ALTER TABLE customer_groupsRENAME COLUMNname TO group_name;
Note that the name column is used in thecustomer_data
view.
Now, you can check whether the change in thename
column was cascaded to thecustomer_data
view:
\d+ customer_data
Output:
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;
The output indicates that thename
column has been changed togroup_name
.
3) Using multiple RENAME COLUMN statements to rename multiple columns example
These statements rename two columnsname
andphone
of thecustomers
table tocustomer_name
andcontact_phone
respectively:
ALTER TABLE customersRENAME COLUMN name TO customer_name;ALTER TABLE customersRENAME COLUMN phone TO contact_phone;
Summary
- Use the PostgreSQL
ALTER TABLE...RENAME COLUMN
statement to rename a column.
Last updated on