Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://neon.com/postgresql/postgresql-tutorial/postgresql-rename-table below:

PostgreSQL Rename Table

Summary: in this tutorial, you will show how to rename a table using the PostgreSQL ALTER TABLE RENAME TO 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;

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 IF EXISTS option:

ALTER TABLE IF EXISTS table_name
RENAME TO new_table_name;

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.

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

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

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

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)

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

ALTER TABLE vendors
RENAME TO suppliers;

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)

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

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

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;

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;

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 CASCADE

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;

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

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4