TutorialsArena

PostgreSQL: Renaming Columns with ALTER TABLE

Rename columns in PostgreSQL tables using ALTER TABLE RENAME COLUMN. This guide explains the syntax and usage of this command.

Renaming Columns in PostgreSQL Using `ALTER TABLE RENAME COLUMN`

In PostgreSQL, you rename a table column using the `ALTER TABLE` command with the `RENAME COLUMN` clause. This is a fundamental database operation for modifying table structures. It's important to understand the implications of renaming columns, especially when those columns are referenced by other database objects.

`ALTER TABLE RENAME COLUMN` Syntax


ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

This command renames a column within a table. The `COLUMN` keyword is optional. You must have the necessary privileges to alter the table.

  • table_name: The name of the table containing the column.
  • column_name: The current name of the column.
  • new_column_name: The new name for the column. Cannot start with `pg_` (reserved for system objects).

Renaming Multiple Columns

To rename multiple columns, you must execute the `ALTER TABLE RENAME COLUMN` command separately for each column.


ALTER TABLE my_table RENAME COLUMN col1 TO new_col1;
ALTER TABLE my_table RENAME COLUMN col2 TO new_col2;

Important Considerations

  • Non-Existent Columns: Renaming a non-existent column results in an error.
  • No `IF EXISTS` Clause: PostgreSQL does not support an `IF EXISTS` clause with `RENAME COLUMN`.
  • Dependent Objects: Renaming a column also updates references in dependent objects (views, foreign key constraints, etc.).

Example: Creating Tables and a View

(Note: The original content includes several screenshots showing the `psql` output after creating tables and a view. Since images cannot be displayed here directly, please refer to the original document for visual confirmation. The descriptions below aim to convey the information present in those screenshots.)

This section demonstrates creating the `Students` and `Student_groups` tables and a view called `Student_data` that joins them. The `Student_data` view depends on columns from both tables.

Example 1: Renaming a Single Column

This example renames the `St_email` column in the `Students` table to `email`:


ALTER TABLE Students RENAME COLUMN St_email TO email;

(Screenshot illustrating the successful execution of the command would be included here in a real HTML file.)

Example 2: Renaming a Column with Dependencies

This example renames the `Stu_name` column in `Student_groups` (used in the `Student_data` view):


ALTER TABLE Student_groups RENAME COLUMN Stu_name TO name;

(Screenshot illustrating the successful execution of the command and the updated view definition would be included here in a real HTML file.)

Example 3: Renaming Multiple Columns

This example shows how to rename multiple columns in the `Students` table:


ALTER TABLE Students RENAME COLUMN St_name TO Student_name;
ALTER TABLE Students RENAME COLUMN St_phone_no TO Student_phone_no;

(Screenshot illustrating the successful execution and a `SELECT` query showing the renamed columns would be included here in a real HTML file.)