PostgreSQL Rename Column

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 TABLE statement. This allows us to specify in which table the column we wish to rename is located.
• The second part is the RENAME COLUMN clause. Similarly, this clauses allows us to specify the target column.
• Finally is the TO keyword. 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.

Practical Example

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:

\c sample_database

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 current_location.

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.

Conclusion

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!!

If you enjoy our content, please consider buying us a coffee to support our work: