PostgreSQL RENAME COLUMN Statement

PostgreSQL RENAME COLUMN Statement

The PostgreSQL RENAME COLUMN statement is used to rename an existing column in a table. This statement is essential for modifying the table structure to reflect changes in data requirements or to improve column naming clarity.

---

Syntax

ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

The RENAME COLUMN statement has the following components:

• table_name: The name of the table containing the column to be renamed.
• old_column_name: The current name of the column to be renamed.
• new_column_name: The new name for the column.

---

Example PostgreSQL RENAME COLUMN Statement Queries

Let's look at some examples of PostgreSQL RENAME COLUMN statement queries:

1. Basic RENAME COLUMN Example

ALTER TABLE employees
RENAME COLUMN email TO email_address;

This query renames the email column to email_address in the employees table.

2. RENAME COLUMN in Another Table

ALTER TABLE departments
RENAME COLUMN dept_name TO department_name;

This query renames the dept_name column to department_name in the departments table.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and then renaming a column.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

In this example, we create a table named employees with columns for id, first_name, last_name, and email.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane.smith@example.com');

Here, we insert data into the employees table, including values for the email column.

Step 3: Renaming the Column

This step involves renaming the email column to email_address in the employees table.

ALTER TABLE employees
RENAME COLUMN email TO email_address;

Here, we rename the email column to email_address in the employees table.

Conclusion

The PostgreSQL RENAME COLUMN statement is a fundamental tool for modifying the structure of an existing table to improve clarity and reflect changes in data requirements. Understanding how to use the RENAME COLUMN statement and its syntax is essential for effective database schema management and modification in PostgreSQL.