PostgreSQL RENAME TABLE

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

Getting Started with the ALTER TABLE RENAME TO Statement #

In PostgreSQL, renaming a table is simple and straightforward. You can use the ALTER TABLE RENAME TO statement to change the name of an existing table.

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

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

In this syntax:

1. First, specify the name of the table you want to rename in the ALTER TABLE clause.
2. Second, provide the new table name in the RENAME TO clause.

If the table_name does not exist, you’ll encounter an error.

To change the structure of a table only if it exists, you can use the IF EXISTS option:

ALTER TABLE IF EXISTS table_name
RENAME TO new_table;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

When using the IF EXISTS statement, if the table_name does not exist, you’ll get a notice instead of an error.

If the table has dependent objects such as a view and foreign key constraints, renaming the table will automatically change the dependent objects.

For example, if a view references a table and you rename it, PostgreSQL will also change the table name in the view definition.

Renaming Table Example #

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

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

Second, create new tables called vendors and groups:

CREATE TABLE groups(   
    group_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    group_name VARCHAR(255) NOT NULL
);

CREATE TABLE vendors(
    vendor_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    vendor_name VARCHAR(255) NOT NULL,
    group_id INT NOT NULL,
    FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE 
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, rename the table groups to vendor_groups:

ALTER TABLE groups
RENAME TO vendor_groups;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, verify the change:

\d vendor_groupsCode language: plaintext (plaintext)

Output:

                               Table "public.vendor_groups"
   Column   |          Type          | Collation | Nullable |           Default
------------+------------------------+-----------+----------+------------------------------
 group_id   | integer                |           | not null | generated always as identity
 group_name | character varying(255) |           | not null |
Indexes:
    "groups_pkey" PRIMARY KEY, btree (group_id)
Referenced by:
    TABLE "vendors" CONSTRAINT "vendors_group_id_fkey" FOREIGN KEY (group_id) REFERENCES vendor_groups(group_id) ON DELETE CASCADECode language: plaintext (plaintext)

Summary #

• Use the PostgreSQL ALTER TABLE RENAME TO statement to rename a table.

Quiz #