PostgreSQL Rename Table: A Step-by-Step Guide

Summary: in this tutorial, you will show how to rename a table using the PostgreSQL ALTER TABLE RENAME TO statement.

Introduction to PostgreSQL RENAME TABLE statement

To change the name of an existing table, you use the ALTER TABLE... RENAME TO statement as follows:

ALTER TABLE table_name
RENAME TO new_table_name;Code language: SQL (Structured Query Language) (sql)

In this statement:

  • First, specify the name of the table which you want to rename after the ALTER TABLE clause.
  • Second, assign the new table name after the RENAME TO clause.

If you rename a table that does not exist, PostgreSQL will issue an error.

To avoid the error, you can use the the IF EXISTS option:

ALTER TABLE IF EXISTS table_name
RENAME TO new_table_name;Code language: SQL (Structured Query Language) (sql)

In this case, if the table_name does not exist, PostgreSQL will issue a notice instead.

To rename multiple tables, you have to execute multiple ALTER TABLE ... RENAME TO statements. It’s not possible to rename multiple tables using a single ALTER TABLE statement.

PostgreSQL rename table examples

Let’s take some examples of using the ALTER TABLE ... RENAME TO statement.

1) Basic PostgreSQL rename table statement example

First, create a new table called vendors for the demonstration purpose:

CREATE TABLE vendors (
    id serial PRIMARY KEY,
    name VARCHAR NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, describe the vendors table using the \d command in psql:

\d vendors

Output:

                                 Table "public.vendors"
 Column |       Type        | Collation | Nullable |               Default
--------+-------------------+-----------+----------+-------------------------------------
 id     | integer           |           | not null | nextval('vendors_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "vendors_pkey" PRIMARY KEY, btree (id)Code language: PHP (php)

Third, change the name of the vendors table to suppliers using the ALTER TABLE...RENAME TO statement:

ALTER TABLE vendors 
RENAME TO suppliers;Code language: SQL (Structured Query Language) (sql)

Finally, describe the suppliers table:

                                Table "public.suppliers"
 Column |       Type        | Collation | Nullable |               Default
--------+-------------------+-----------+----------+-------------------------------------
 id     | integer           |           | not null | nextval('vendors_id_seq'::regclass)
 name   | character varying |           | not null |
Indexes:
    "vendors_pkey" PRIMARY KEY, btree (id)Code language: PHP (php)

Notice that the name of the table changed but the sequence (vendors_id_seq) remains intact.

1) Renaming a table that has dependent objects

First, create new tables called customers and groups:

CREATE TABLE customer_groups(
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE customers(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    group_id INT NOT NULL,
    FOREIGN KEY (group_id) REFERENCES customer_groups(id) 
       ON DELETE CASCADE 
       ON UPDATE CASCADE
);Code language: SQL (Structured Query Language) (sql)

Second, create a view based on the customers and customer_groups tables:

CREATE VIEW customer_data
AS SELECT
    c.id,
    c.name,
    g.name customer_group
FROM
    customers c
INNER JOIN customer_groups g ON g.id = c.group_id;Code language: SQL (Structured Query Language) (sql)

When you rename a table, PostgreSQL will automatically update its dependent objects such as foreign key constraints, views, and indexes.

Third, rename the customer_groups table to groups:

ALTER TABLE customer_groups
RENAME TO groups;Code language: SQL (Structured Query Language) (sql)

Fourth, verify the foreign key constraint in the customers table by showing the table via \d command in psql:

\d customers

Output:

                                     Table "public.customers"
  Column  |          Type          | Collation | Nullable |                Default
----------+------------------------+-----------+----------+---------------------------------------
 id       | integer                |           | not null | nextval('customers_id_seq'::regclass)
 name     | character varying(255) |           | not null |
 group_id | integer                |           | not null |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "customers_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(id) ON UPDATE CASCADE ON DELETE CASCADECode language: PHP (php)

The output indicates that the foreign key constraint was updated and referenced the groups table instead of the customer_groups table.

Fifth, show the customer_data view in psql:

\d+ customer_data

Output:

                                    View "public.customer_data"
     Column     |          Type          | Collation | Nullable | Default | Storage  | Description
----------------+------------------------+-----------+----------+---------+----------+-------------
 id             | integer                |           |          |         | plain    |
 name           | character varying(255) |           |          |         | extended |
 customer_group | character varying      |           |          |         | extended |
View definition:
 SELECT c.id,
    c.name,
    g.name AS customer_group
   FROM customers c
     JOIN groups g ON g.id = c.group_id;Code language: PHP (php)

The output shows that the supplier_groups table in the SELECT statement of the view was also updated to groups table.

Summary

  • Use the ALTER TABLE ... RENAME TO statement to rename a table.
Was this tutorial helpful ?