How to Rename a Table in PostgreSQL

To rename a table in PostgreSQL, you use the ALTER TABLE statement followed by the RENAME TO clause.

Syntax

ALTER TABLE old_table_name RENAME TO new_table_name;

Example

Suppose you have a table named customers and you want to rename it to clients. The SQL command would be:

ALTER TABLE customers RENAME TO clients;

After running the above command, the table customers will be renamed to clients. You can verify the renaming by querying the information_schema or simply listing all tables using \d:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

Expected Output:

table_name 
--------------
clients
other_table

Renaming Tables with Dependencies

When renaming a table, it’s important to consider any existing dependencies, such as foreign keys, indexes, or triggers. PostgreSQL should handle these automatically, updating references to the renamed table.

Example with Foreign Key

Assume the orders table has a foreign key that references the customers table:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

When you rename customers to clients, PostgreSQL automatically updates the foreign key reference:

ALTER TABLE customers RENAME TO clients;

Verification

To ensure that the foreign key still works, you can run a simple INSERT statement:

INSERT INTO clients (customer_id) VALUES (1);

INSERT INTO orders (customer_id) VALUES (1);

If the foreign key reference was not updated, the second INSERT would fail. If it succeeds, it means PostgreSQL has correctly updated the reference.

Renaming a Table in a Schema

If your table is within a specific schema, you need to include the schema in the ALTER TABLE command.

Example

Consider a table sales.customers within the sales schema:

ALTER TABLE sales.customers RENAME TO clients;

The table sales.customers is now sales.clients. You can verify this by listing the tables in the sales schema:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'sales';

Expected Output

table_name
-----------
clients
other_table

Using BeeKeeper Studio

Renaming a table in Beekeeper Studio is a straightforward task:

1. Connect to your PostgreSQL database using Beekeeper Studio.
2. On the left pane, expand the schema that contains the table, and you’ll see a list of tables.
3. Right-click the table you want to rename and choose the rename table option.
4. Enter your new table name and hit Enter.

Beekeeper Studio will handle the rest, issuing the appropriate SQL command to your PostgreSQL database.

Considerations and Best Practices

• Index and Constraint Names: While PostgreSQL automatically updates dependencies like foreign keys, it does not rename indexes or constraints. If your index or constraint names include the old table name, you might need to rename them manually.

• Permissions: Renaming a table does not affect its permissions. Users who had access to the old table will have the same access to the renamed table.

• View Dependencies: If any views depend on the table, they will continue to work after renaming, but you may want to review and update them for clarity.

Conclusion

Renaming tables in PostgreSQL is a simple yet important operation. Whether working with tables in different schemas or those with dependencies, PostgreSQL handles the process smoothly, ensuring all references are updated correctly. By following best practices and understanding how the renaming process works, you can maintain the integrity and organization of your database.

Other articles you may enjoy:

• How to Rename a Column in PostgreSQL
• How to Use Stored Procedures in PostgreSQL