PostgreSQL – How to Move a Table from One Schema to Another
To move a table from one schema to another in PostgreSQL database, you can use the ALTER TABLE statement with the SET SCHEMA clause. Here’s the basic syntax:
ALTER TABLE old_schema.table_name SET SCHEMA new_schema;where
old_schema is the name of the current schema where the table resides.
table_name is the name of the table you want to move.
new_schema is the name of the schema where you want to move the table.
After executing the statement, the table “my_table” will be moved from “old_schema” to “new_schema.”
Please note the following considerations when moving a table between schemas:
- You must have appropriate permissions to execute the
ALTER TABLEstatement with theSET SCHEMAclause. - Moving a table between schemas does not automatically update references to the table in your SQL queries or application code. You may need to update these references manually if necessary.
- Be cautious when moving tables in a production database, as it can impact applications that rely on the table’s location. Ensure that any dependent objects, such as views or functions, are updated accordingly.
- Backup your database before performing schema changes to prevent data loss or unintended consequences.