PostgreSQL – RENAME COLUMN
In PostgreSQL, the RENAME COLUMN clause is used with the ALTER TABLE statement to rename one or more columns in a table.
Syntax: ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
Let’s analyze the above syntax:
- First, specify the table, which contains the column you want to rename, after the ALTER TABLE clause.
- Second, provide the column name after the RENAME COLUMN clause.
- Third, give the new column name after the TO keyword.
Let’s take some examples of using the ALTER TABLE RENAME COLUMN to get a better understanding. Example 1: First, let’s create two new tables namely customers and customer_groups using the below statement:
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) );
Then we create a new view named customer_data based on the customers and customer_groups tables as follows:
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;
Now we will use the ALTER TABLE RENAME COLUMN statement to rename the email column of the customers table to contact_email:
ALTER TABLE customers RENAME COLUMN email TO contact_email;
Now verify the changes made using the below statement:
SELECT * FROM customers;
Output: Example 2: These statements rename two columns name and phone of the customers table to customer_name and contact_phone respectively:
ALTER TABLE customers RENAME COLUMN name TO customer_name; ALTER TABLE customers RENAME COLUMN phone TO contact_phone;
Now verify the changes made using the below statement:
SELECT * FROM customers;
Output: