Moving objects between schemas

Once you've created schemas for administration purposes, you'll want to move existing objects to keep things tidy.

How to do it…

To move one table from its current schema to a new schema, use the following:

ALTER TABLE cust
SET SCHEMA anotherschema;

If you want to move all objects, you can consider renaming the schema itself, using the following query:

ALTER SCHEMA existingschema RENAME TO anotherschema;

This only works if another schema with that name does not exist. Otherwise, you'll need to run ALTER TABLE for each table you want to move. You can use the earlier recipe Performing actions on many tables to achieve that.

Views, sequences, functions, aggregates, and domains can also be moved by ALTER commands with SET SCHEMA options.

How it works…

When you move tables to a new schema, all the indexes, triggers, and rules defined on those tables will also be moved to the new schema. If you've used a SERIAL data type and an implicit sequence has been created, then that...