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

PostgreSQL 1 Minute

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!

Published by pashagolub

Nerd.

Published

8 thoughts on “How to move data from one schema to another in Postgres?”

      1. 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.

        Like

        Reply

        1. 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;

          Like

          Reply

  2. 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 ..

    Like

    Reply

  3. 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

    Like

    Reply

Leave a comment