In this short tutorial, we will discuss how you can rename an existing table column. This will allow you to avoid conflicting names when migrating database or adding new columns to the table.
Let's get started.
PostgreSQL Rename Statement
In PostgreSQL, we can rename a column using the
ALTER TABLE statement. The syntax is as shown below:
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name
Let us break down the command syntax above.
- The first step is the
ALTER TABLEstatement. This allows us to specify in which table the column we wish to rename is located.
- The second part is the
RENAME COLUMNclause. Similarly, this clauses allows us to specify the target column.
- Finally is the
TOkeyword. This allows us to specify the new name for the column.
Keep in mind that the specified column should exist on the target table. Attempting to rename a column that does not exist will raise an error. In PostgreSQL, you do not have the
IF EXISTS clause.
PostgreSQL will also rename all other references that refer to the renamed column. For example, if the renamed column is referenced by other constraints such as foreign keys, PostgreSQL will point the child columns to the renamed column.
The best way to illustrate how to rename a column in PostgreSQL is using an example.
Let us start by creating a sample database as shown:
CREATE DATABASE "sample_database";
The query above should create a new database called
sample_database. Feel free to rename the database to any name you wish.
We can then swtich to the database by running:
This should connect to the new database and allow us to create new schemas.
Let's start by adding a table as shown:
CREATE TABLE department ( id SERIAL PRIMARY KEY, department_name VARCHAR NOT NULL ); CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, country VARCHAR NOT NULL, department_id INT, FOREIGN KEY (department_id) REFERENCES department (id) );
In the example code above, we have two tables. One holding the department information and the other holding employee information.
Notice the employees table has a reference the id column in the deparment table.
PostgreSQL Rename Column
We can use the
ALTER TABLE clause to rename the country column to current_location. An example code is as shown below:
ALTER TABLE employees RENAME COLUMN country TO current_location;
The command above should rename the country column to current_location.
We can verify by running the command:
SELECT * FROM employees;
The statement above should return the columns as shown:
We can see the new column name is set to
PostgreSQL Rename Column with Dependencies
As mentioned, renaming a column with dependencies such as foreign keys will force PostgreSQL to update the references. We can verify this by renaming the column
department_name in the department's table to dpt_name.
The command statement is as provided below:
ALTER TABLE department RENAME COLUMN department_name TO dpt_name;
The statement above should rename the and the references to the column.
In this short article, you discovered how you can rename a column in PostgreSQL using the
ALTER TABLE and
RENAME TABLE statements.
The source code used in this tutorial is provided below:
CREATE TABLE department ( id SERIAL PRIMARY KEY, department_name VARCHAR NOT NULL ); CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, country VARCHAR NOT NULL, department_id INT, FOREIGN KEY (department_id) REFERENCES department (id) ); ALTER TABLE employees RENAME COLUMN country TO current_location; SELECT * FROM employees; ALTER TABLE department RENAME COLUMN department_name TO dpt_name; SELECT * FROM employees;
Thanks for reading and See you in the next one!!