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

Re: Moving tables between schemas

From: John Sidney-Woollett <johnsw(at)wardbrook(dot)com>
To: "m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk" <m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk>
Cc: postgres general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Moving tables between schemas
Date: 2004-07-30 10:42:58
Message-ID: 410A2632.3000408@wardbrook.com (view raw or flat)
Thread:
Lists: pgsql-general
Be careful with any objects (functions, triggers) to make sure that are 
updated to reference the new tables in their correct schemas too.

JOhn Sidney-Woollett

m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk wrote:

> Thanks. I hadn't thought of that - that's probably easiest.
> 
> On Fri, 2004-07-30 at 10:19, John Sidney-Woollett wrote:
> 
>>You may be able to play around with the system catalogs, but I don't 
>>know anything about that...
>>
>>An easy method of moving the tables are
>>
>>create table mynewschema1.table1 as select * from public.table1;
>>drop public.table1;
>>
>>create table mynewschema2.table2as select * from public.table2;
>>drop public.table2;
>>
>>You may need to recreate any indexes that the tables used in the public 
>>schema, and you may need to grant rights in order to allow your users to 
>>access the tables...
>>
>>Hope that helps
>>
>>John Sidney-Woollett
>>
>>create
>>
>>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
>>>
>>>?
>>>
>>>Thanks.
>>>
>>>Max
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>

In response to

pgsql-general by date

Next:From: Shridhar DaithankarDate: 2004-07-30 10:48:13
Subject: Re: Moving tables between schemas
Previous:From: m.e.bruche@lse.ac.ukDate: 2004-07-30 10:26:58
Subject: Moving tables between schemas

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