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 theALTER TABLE clause.
• Second, provide the name of the column that you want to rename after theRENAME COLUMN keywords.
• Third, specify the new name for the column after theTO 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 PostgreSQLALTER TABLE...RENAME COLUMN statement to rename a column.

Last updated onJanuary 25, 2024

On this page

• Introduction to PostgreSQL RENAME COLUMN clause
• PostgreSQL RENAME COLUMN examples
• Summary