{"id":832,"date":"2024-12-11T11:59:30","date_gmt":"2024-12-11T04:59:30","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=832"},"modified":"2025-01-03T14:50:31","modified_gmt":"2025-01-03T07:50:31","slug":"postgresql-rename-table","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-rename-table\/","title":{"rendered":"PostgreSQL RENAME TABLE"},"content":{"rendered":"\n

Summary:<\/strong> In this tutorial, you will learn how to rename a table in PostgreSQL using the ALTER TABLE RENAME TO<\/code> statement.<\/p>\n\n\n\n

Getting Started with the ALTER TABLE RENAME TO Statement #<\/a><\/h2>\n\n\n\n

In PostgreSQL, renaming a table is simple and straightforward. You can use the ALTER TABLE RENAME TO<\/code> statement to change the name of an existing table.<\/p>\n\n\n\n

Here’s the syntax of the ALTER TABLE RENAME TO<\/code> statement:<\/p>\n\n\n

ALTER<\/span> TABLE<\/span> table_name<\/span>\nRENAME<\/span> TO<\/span> new_table;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
    \n
  1. First, specify the name of the table you want to rename in the ALTER TABLE<\/code> clause.<\/li>\n\n\n\n
  2. Second, provide the new table name in the RENAME TO<\/code> clause.<\/li>\n<\/ol>\n\n\n\n
    If the table_name<\/code> does not exist, you’ll encounter an error.<\/p>\n\n\n\n
    To change the structure of a table only if it exists, you can use the IF EXISTS<\/code> option:<\/p>\n\n\n
    ALTER<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> table_name<\/span>\nRENAME<\/span> TO<\/span> new_table;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    When using the IF EXISTS<\/code> statement, if the table_name<\/code> does not exist, you’ll get a notice instead of an error.<\/p>\n\n\n\n
    For example, if a view references a table and you rename it, PostgreSQL will also change the table name in the view definition.<\/p>\n\n\n\n
    Renaming Table Example #<\/a><\/h2>\n\n\n\n
    First, open your terminal and connect to the PostgreSQL server:<\/p>\n\n\n
    psql -U postgres<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    CREATE<\/span> TABLE<\/span> groups<\/span>(   \n    group_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    group_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> vendors(\n    vendor_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    vendor_name VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    group_id INT<\/span> NOT<\/span> NULL<\/span>,\n    FOREIGN KEY<\/span>(group_id) REFERENCES<\/span> groups<\/span>(group_id) ON<\/span> DELETE<\/span> CASCADE<\/span> \n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, rename the table groups<\/code> to vendor_groups<\/code>:<\/p>\n\n\n
    ALTER<\/span> TABLE<\/span> groups<\/span>\nRENAME<\/span> TO<\/span> vendor_groups;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Finally, verify the change:<\/p>\n\n\n
    \\d vendor_groups<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
                                   Table \"public.vendor_groups\"\n   Column   |          Type          | Collation | Nullable |           Default\n------------+------------------------+-----------+----------+------------------------------\n group_id   | integer                |           | not null | generated always as identity\n group_name | character varying(255) |           | not null |\nIndexes:\n    \"groups_pkey\" PRIMARY KEY, btree (group_id)\nReferenced by:\n    TABLE \"vendors\" CONSTRAINT \"vendors_group_id_fkey\" FOREIGN KEY (group_id) REFERENCES vendor_groups(group_id) ON DELETE CASCADE<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary #<\/a><\/h2>\n\n\n\n
    Second, create new tables<\/a> called vendors<\/code> and groups<\/code>:<\/p>\n\n\n
    If the table has dependent objects such as a view<\/a> and foreign key constraints<\/a>, renaming the table will automatically change the dependent objects.<\/p>\n\n\n\n