How to move data from one schema to another in Postgres?

I saw a question today :

I have PostpreSQL 9.1. In my database there are 2 schemas: public and test. How can I quickly move all objects and data from public to test?

Opps. I’m stuck with this issue for some time. I was thinking about dump and restore tricks, about direct changes to the pg_catalog schema etc. I even appeared on the IRC channel with this question.

There I had conversation with Jon SuckMojo Erdman, who’s first thought was dump way either. But then we have found a simple and elegant solution independently.

Assuming schema test is empty:

DROP SCHEMA test; 

ALTER SCHEMA public RENAME TO test; 

CREATE SCHEMA public; 

Booyah!

About these ads

Nerd.

Tagged with: ,
Posted in PostgreSQL
8 comments on “How to move data from one schema to another in Postgres?
  1. Now the real fun would be to copy a schema from one to another. :)

    • pashagolub says:

      Not really… :)

      Just dump schema before altering, then restore to the previous state.

      • I am talking about cloning the schema so in the end there are two copies. One with data and other other without. Like a template. I am looking into this for a mufti-tenant database. When a new client signs up create a new schema for them.

        • Mr. Muskrat says:

          I do that with databases instead of schemas. After I’ve loaded the schema, it’s simply a matter of running:

          CREATE DATABASE newtemplate TEMPLATE mydb;
          UPDATE pg_database SET datistemplate=true WHERE datname=’newtemplate’;

          Then when I need a new db, I run:

          CREATE DATABASE newdb TEMPLATE newtemplate;

  2. M. Amer Siddique says:

    This is Case when we are performing everything on same cluster. If need to clone on new cluster then what will be scenarios along with Dump/ Restore ..

  3. Anonymous says:

    Another option would have been to generate a script that does an ALTER TABLE foobar SET schema test; for all tables in the public schema

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: