PostgreSQL Rename Column

Summary: in this tutorial, you’ll learn how to use the PostgreSQL ALTER TABLE RENAME COLUMN statement to rename a column of a table.

Getting Started with the PostgreSQL ALTER TABLE RENAME COLUMN Statement #

The ALTER TABLE RENAME COLUMN statement allows you to rename a table column.

Here’s the syntax of the ALTER TABLE RENAME COLUMN statement:

ALTER TABLE table_name
RENAME COLUMN column_name 
TO new_column;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, provide the name of the table in the ALTER TABLE clause.
• Second, specify the column you want to change in the RENAME COLUMN clause.
• Third, specify the new column name for the column in the TO clause.

To make it shorter, you can omit the COLUMN keyword in the RENAME COLUMN like this:

ALTER TABLE table_name
RENAME column_name 
TO new_column;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the column you change has references such as views, foreign key constraints, triggers, user-defined functions, and stored procedures, PostgreSQL automatically changes the column names in these objects.

When you rename columns, updating your application code to reflect these changes is crucial. If your application tries to access old columns, it may cause unexpected behavior or errors.

To streamline the renaming column process, you can use a migration library. A migration can help manage schema changes efficiently. Typically, a migration library allows you to synchronize the database version and application code during deployment.

Renaming Column Example #

First, open your terminal and use psql to connect to the PostgreSQL server:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a table called sales_returns to store the sales returns from customers:

CREATE TABLE sales_returns (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  date DATE NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  order_id INT,
  reason VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, change the name of the order_id column to sales_order_id:

ALTER TABLE sales_returns 
RENAME order_id 
TO sales_order_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, verify the changes:

\d sales_returnsCode language: plaintext (plaintext)

Renaming a Column Referenced by a View #

First, create a view sales_return_reports based on the sales_returns table:

CREATE VIEW sales_return_reports AS
SELECT
  product_name,
  date,
  SUM(quantity)
FROM
  sales_returns
GROUP BY
  product_name,
  date;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, rename the date column to return_date:

ALTER TABLE sales_returns 
RENAME date 
TO return_date;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, check the sales_return_reports view:

\d+ sales_return_reportsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

                               View "public.sales_return_reports"
    Column    |          Type          | Collation | Nullable | Default | Storage  | Description
--------------+------------------------+-----------+----------+---------+----------+-------------
 product_name | character varying(255) |           |          |         | extended |
 date         | date                   |           |          |         | plain    |
 sum          | bigint                 |           |          |         | plain    |
View definition:
 SELECT product_name,
    return_date AS date,
    sum(quantity) AS sum
   FROM sales_returns
  GROUP BY product_name, return_date;Code language: plaintext (plaintext)

The output indicates that the date column changed to return_date in the view’s definition. However, the view still uses date as the column alias.

Renaming a Column If Exists #

PostgreSQL does not support the syntax for the ALTER TABLE RENAME COLUMN IF EXISTS statement. You’ll encounter an error if you rename a column that does not exist.

If you want to rename a column if it exists, you can define a user-defined function:

CREATE OR REPLACE FUNCTION fn_rename_column(
    from_table VARCHAR, 
    old_column VARCHAR, 
    new_column VARCHAR
)
RETURNS VOID
AS
$$
BEGIN
    IF EXISTS (SELECT *
        FROM information_schema.columns
        WHERE table_name = from_table AND column_name = old_column)
    THEN
        ALTER TABLE from_table 
        RENAME COLUMN old_column TO new_column;
    END IF;
END $$; 
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The fn_rename_column function checks if the column exists before executing the ALTER TABLE statement.

Summary #

• Use the ALTER TABLE RENAME COLUMN statement to rename a column.

Quiz #