Skip site navigation (1) Skip section navigation (2)

Re: Moving tables between schemas

From: Shridhar Daithankar <shridhar(at)frodo(dot)hserus(dot)net>
To: "m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk" <m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Moving tables between schemas
Date: 2004-07-30 10:11:48
Message-ID: 200407301541.48304.shridhar@frodo.hserus.net (view raw or flat)
Thread:
Lists: pgsql-general
On Friday 30 Jul 2004 3:56 pm, m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk wrote:
> Hi,
>
> In my database, I created a lot of tables before I found out about
> schemas. It's a mess!
>
> Suppose I want to get organised, and create a couple of schemas. How do
> I move existing tables into my newly created schemas?
>
> e.g.
>
> public.table1 -> mynewschema1.table1
> public.table2 -> mynewschema2.table2

You can dump the tables, edit the dump to alter the create table statement so 
that the table is created in proper schema.

This could be very rudimentary depending upon size of each table and number of 
tables. The local documentation of 7.4.3 indicates that either alter table or 
alter schema does not support moving tables between schemas.

You could resort to catalog hacking and change the schema manually but I am 
not sure how advisable and predictable it would be.

HTH

 Shridhar

In response to

pgsql-general by date

Next:From: John Sidney-WoollettDate: 2004-07-30 10:19:55
Subject: Re: Moving tables between schemas
Previous:From: Nilabhra BanerjeeDate: 2004-07-30 09:37:51
Subject: Postgresql and OS cluster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group