PostgreSQL Rename Columns

In this article, you will learn how to rename one or more columns using PostgreSQL ALTER TABLE ... RENAME COLUMN statements.

PostgreSQL allows you to use the ALTER TABLE ... RENAME COLUMN statement to rename an existing column.

PostgreSQL RENAME COLUMN syntax

To rename a column in a table, use the ALTER TABLE ... RENAME COLUMN statement:

ALTER TABLE table_name
RENAME [COLUMN] column_name to new_column_name;

Explanation:

  • The table_name is the table in which to rename the column.
  • The RENAME [COLUMN] ... TO ... clause to rename a column. The COLUMN keyword can be omitted.
  • The column_name is the name of the column to be renamed. new_column_name is the new name of the column.

When you want to rename a column, if other database objects (such as foreign keys, views, triggers, stored procedures, etc.) reference the column, PostgreSQL will automatically change the column name in those dependent objects.

If you enter a column name that does not exist, PostgreSQL will give an error: ERROR: column “x” does not exist.

A ALTER TABLE ... RENAME COLUMN statement can rename only one column. If you want to rename multiple columns, use multiple statements.

PostgreSQL RENAME COLUMN example

This example demonstrates how to rename a column in a table in PostgreSQL.

We will create two tables users and user_hobbies in the testdb database. Among them, the users table is used to store the user’s name, gender, age and other information, and the user_hobbies table is used to store the user’s hobbies.

The following statement creates a table named users:

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  user_id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(45) NOT NULL,
  age INTEGER,
  locked BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMP NOT NULL
);

The following statement creates the user_hobbies table:

DROP TABLE IF EXISTS user_hobbies;

CREATE TABLE user_hobbies (
  hobby_id SERIAL NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (hobby_id),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT);

Use the \d command to display the definition of the users table:

\d users

                          Table "public.users"
  Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
user_id    | integer                     |           | not null |
name       | character varying(45)       |           | not null |
age        | integer                     |           |          |
locked     | boolean                     |           | not null | false
created_at | timestamp without time zone |           | not null |
Indexes:
   "users_pkey" PRIMARY KEY, btree (user_id)
Referenced by:
   TABLE "user_hobbies" CONSTRAINT "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

Use the \d command to display user_hobbies the definition of the table:

\d user_hobbies

                                          Table "public.user_hobbies"
  Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
user_id    | integer                     |           | not null |
hobby      | character varying(45)       |           | not null |
created_at | timestamp without time zone |           | not null |
Indexes:
   "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
   "fk_user" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE CASCADE

We see that the foreign key fk_user in the user_hobbies table refers users to the user_id column.

The following statement is used to rename user_id column to id in the users table:

ALTER TABLE users
RENAME COLUMN user_id TO id;

Since the foreign key fk_user in the user_hobbies table refers the user_id column in the users table, PostgreSQL automatically updates the column name that the foreign key depends on.

We can verify that the foreign key in the user_hobbies table has been updated by viewing the table definition with the \d command, as follows:

\d user_hobbies

                                          Table "public.user_hobbies"
  Column   |            Type             | Collation | Nullable |                    Default
------------+-----------------------------+-----------+----------+------------------------------------------------
hobby_id   | integer                     |           | not null | nextval('user_hobbies_hobby_id_seq'::regclass)
user_id    | integer                     |           | not null |
hobby      | character varying(45)       |           | not null |
created_at | timestamp without time zone |           | not null |
Indexes:
   "user_hobbies_pkey" PRIMARY KEY, btree (hobby_id)
Foreign-key constraints:
   "fk_user" FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE CASCADE

Look at users(id) in the last line, we can see that the dependent column names in the foreign key are automatically updated.

Conclusion

PostgreSQL ALTER TABLE ... RENAME COLUMN statements are used to rename an existing column. You can also rename tables, add columns, drop columns, modify column properties, and more.