PostgreSQL RENAME COLUMN (original) (raw)

Last Updated : 15 Jul, 2025

**Renaming columns in **PostgreSQL is a common task for **developers and **database administrators. When aligning with **naming conventions, fixing typos, or restructuring database schemas. Using the **PostgreSQL ALTER TABLE RENAME COLUMN statement, we can efficiently **rename one or more columns without losing data.

In this article, we will explain the **syntax, **examples, and important considerations to help us rename columns efficiently while ensuring our **database operates smoothly.

What is PostgreSQL RENAME COLUMN?

The PostgreSQL **ALTER TABLE RENAME COLUMN clause allows us to rename a column in an existing table. We can rename **single or multiple columns in different tables to improve **readability and **consistency in database **design. PostgreSQL ensures that this operation is **transactional andif anything goes wrong, changes are rolled back to keep the **database consistent.

**Syntax:

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;

**key terms

• **Specify the Table: The table contains the column we want to rename.
• **Column Name: The name of the column we want to rename.
• **New Column Name: The new name for the column.

Examples of Using PostgreSQL RENAME COLUMN Clause

Let’s take some practical examples to demonstrate how to rename columns, handle foreign key constraints, and work with **indexed columns. These examples will guide us through **real-world scenarios where renaming columns becomes necessary for better **schema organization and **readability.

Example 1: Renaming a Single Column in PostgreSQL

Let’s create a **customers table and **rename the 'email' column to '**contact_email'. This example demonstrates a basic column **rename operation, showing how to make **schema changes while preserving **data and **structure.

Step 1: Create Table

CREATE TABLE customers (
id serial PRIMARY KEY,
name VARCHAR NOT NULL,
phone VARCHAR NOT NULL,
email VARCHAR,
group_id INT,
);

Step 2: Insert Data

INSERT INTO customers (name, phone, email, group_id)
VALUES
('John Doe', '1234567890', 'john@mail.com', 101),
('Jane Smith', '0987654321', 'jane@mail.com', 102);

Step 3: Verify Data Before Renaming

SELECT * FROM customers;

**Output Before Renaming:

Step4: Rename Column

ALTER TABLE customers
RENAME COLUMN email TO contact_email;

Step 5: Verify Data After Renaming

SELECT * FROM customers;

**Output

**Explanation:

In this example, the email column was successfully renamed to contact_email. The data in the table remains unchanged, and we can now see the new column name in the output while the existing data, such as **email addresses, is preserved.

**Example 2: Renaming Multiple Columns

If we need to **rename multiple columns in PostgreSQL, we must execute multiple ALTER TABLE statements. These statements rename two columns '**name' and '**phone' of the '**customers' table to '**customer_name' and '**contact_phone' respectively.

Step 1: Rename Columns

ALTER TABLE customers
RENAME COLUMN name TO customer_name;

ALTER TABLE customers
RENAME COLUMN phone TO contact_phone;

Step 2: Verify Changes

SELECT * FROM customers;

**Output

**Explanation:

In this example, we renamed two columns, **name**to **customer_name**and **phone**to **contact_phone **using separate ALTER TABLE statements. After verifying the changes, the output reflects the **updated column names while keeping the data intact.

Example 3: Renaming Columns with Foreign Keys

In this example, we will create an **orders**table that **references the **customers**table through a **foreign key on the **customer_id**column. After renaming the **customer_id**column, we will re-establish the **foreign key relationship.

Step 1: Create the orders Table

CREATE TABLE orders (
order_id serial PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);

Step 2: Insert Data into the orders Table

INSERT INTO orders (customer_id, order_date)
VALUES
(1, '2024-01-15'),
(2, '2024-02-20');

Step 3: Drop the Foreign Key Constraint

ALTER TABLE orders
DROP CONSTRAINT orders_customer_id_fkey;

Step 4: Rename Column

ALTER TABLE orders
RENAME COLUMN customer_id TO new_customer_id;

Step 5: Add Foreign Key Constraint Again

ALTER TABLE orders
ADD CONSTRAINT orders_new_customer_id_fkey
FOREIGN KEY (new_customer_id) REFERENCES customers (id);

Step 6: Verify Data

SELECT * FROM orders;

**Output After Renaming:

**Explanation:

In this example, the column **customer_id**in the orders table was renamed to new_customer_id. After renaming, we re-established the **foreign key relationship between the **orders**and **customers**tables. The data remains **intact, and the foreign key reference is properly updated to reflect the new column name.

Important Points About RENAME COLUMN in PostgreSQL

• PostgreSQL treats **unquoted identifiers as **lowercase. If the column names are **mixed case or contain special characters, **use double quotes.|
• Renaming a column does not automatically update references to that column in **views, triggers, **functions, or application code.
• The RENAME COLUMN operation is **transactional. If something goes wrong, the **database will roll back to its previous state.
• Renaming a column does not affect the names of associated **constraints or **indexes.

**Conclusion

Knowing **how to rename a column in PostgreSQL ensures better organization and consistency in our database. Using the **PostgreSQL ALTER TABLE RENAME COLUMN statement, we can easily rename both **single and multiple columns. If a column is indexed, the index reference will automatically update.

However, keep in mind that renaming columns affects dependent objects like **views, triggers, and foreign keys, which need to be updated manually. By following **best practices such as **transactional updates and **stakeholder notifications, we can ensure a **smooth renaming process without affecting database performance.