How to Move PostgreSql Tables to a Different Schema
Posted by coreycoogan on December 22, 2010
Recently an application I’m working on had the need to move a mess of tables from the PG default “Public” schema to a new one, which we’re calling “Selection”. The more recent versions of PG (8.2 as of this writing) have a SET SCHEMA action available on the ALTER TABLE command.
ALTER TABLE name SET SCHEMA new_schema
SET SCHEMA is very nice in that it not only moves the tables, but any associated indexes, sequences and constraints.
From the 8.2 Documentation:
This form moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well.
This is all fine and good, but if you have more than a couple tables to move, this can be hassle. Here’s how to use PG’s pg_tables System Table to generate the sql for every table in your database. Run this query against the database that has the tables you wish to move. Then simply copy/paste the output into another query window and execute. Don’t forget to make the schema first!
select 'alter table "' || tablename || '" set schema "Selection";' from pg_tables where schemaname='public';