O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 might consider just 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 already exist.

Otherwise, you'll need to run ALTER TABLE for each table you would like to move. You can use the recipe for performing the same action on many tables to achieve that.

Views, sequences, functions, aggregates, and domains can also be moved ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required