/Rename Table

PostgreSQL Rename Table: A Step-by-Step Guide

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

Introduction to PostgreSQL RENAME TABLE statement

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

ALTER TABLE table_nameRENAME TO new_table_name;

In this statement:

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

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

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

ALTER TABLE IF EXISTS table_nameRENAMETO new_table_name;

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

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

PostgreSQL rename table examples

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

1) Basic PostgreSQL rename table statement example

First,create a new table calledvendors for the demonstration purpose:

CREATE TABLE vendors (    idserial PRIMARY KEY,    name VARCHAR NOT NULL);

Second, describe thevendors table using the\d command inpsql:

\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)

Third, change the name of thevendors table tosuppliers using theALTER TABLE...RENAME TO statement:

ALTER TABLE vendorsRENAME TO suppliers;

Finally, describe thesuppliers 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)

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

1) Renaming a table that has dependent objects

First, create new tables calledcustomers andgroups:

CREATE TABLEcustomer_groups(    id SERIAL PRIMARY KEY,    name VARCHAR NOTNULL);CREATE TABLEcustomers(    id SERIAL PRIMARY KEY,    name VARCHAR(255) NOTNULL,    group_idINT NOTNULL,    FOREIGN KEY(group_id) REFERENCES customer_groups(id)       ON DELETE CASCADE       ON UPDATE CASCADE);

Second,create a view based on thecustomers andcustomer_groups tables:

CREATE VIEW customer_dataAS SELECT    c.id,    c.name,    g.name customer_groupFROM    customers cINNER JOIN customer_groups gON g.id= c.group_id;

When you rename a table, PostgreSQL will automatically update its dependent objects such asforeign key constraints,views, andindexes.

Third, rename thecustomer_groups table togroups:

ALTER TABLE customer_groupsRENAMETO groups;

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

\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 CASCADE

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

Fifth, show thecustomer_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;

The output shows that thecustomer_groups table in theSELECT statement of the view was also updated togroups table.

Summary

Use theALTER TABLE ... RENAME TO statement to rename a table.

PreviousPostgreSQL ALTER TABLE NextPostgreSQL ADD COLUMN: Add One or More Columns to a Table

Last updated onFebruary 19, 2024

Was this page helpful?

Thank you for your feedback!

Movatterモバイル変換

PostgreSQL Rename Table: A Step-by-Step Guide

Introduction to PostgreSQL RENAME TABLE statement

PostgreSQL rename table examples

1) Basic PostgreSQL rename table statement example

1) Renaming a table that has dependent objects

Summary

On this page