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;