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

Re: How to "COPY schema1.table TO schema2.table" ?

From: Tim Goodaire <tgoodaire(at)linux(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to "COPY schema1.table TO schema2.table" ?
Date: 2005-02-02 19:58:35
Message-ID: 420130EB.6050006@linux.ca (view raw or flat)
Thread:
Lists: pgsql-novice
You can copy a table from one schema to another by doing something like 
this:

SET search_path TO schema1,schema2;

CREATE TABLE schema2.mytable AS SELECT * FROM schema1.mytable;

Tim

Mike G. wrote:

>Someone once posted to a list a sql script that could be run to move / rename a table from one schema to another.  
>
>Don't remember if it was the General or Hacker list.  Sorry.
>
>
>On Fri, Jan 21, 2005 at 10:26:45AM -0700, Michael Fuhr wrote:
>  
>
>>On Fri, Jan 21, 2005 at 05:18:49PM +0100, Andreas Neumann wrote:
>>
>>    
>>
>>>Is there a convenient way to move a table (or a bunch of tables) to a
>>>different schema?
>>>      
>>>
>>It might be possible by fiddling with the system catalogs, although
>>I haven't tried it myself.
>>
>>I'm just brainstorming now, but you could update the table's
>>relnamespace column in pg_class.  That alone probably wouldn't
>>suffice, however -- I'd also look for dependent objects in pg_depend,
>>pg_index, etc., and see if they needed to have columns updated as
>>well.  Maybe somebody else can think of a complete list.
>>
>>I see that the TODO list has the following item under ALTER:
>>"Allow objects to be moved to different schemas."
>>
>>-- 
>>Michael Fuhr
>>http://www.fuhr.org/~mfuhr/
>>    
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>  
>


In response to

Responses

pgsql-novice by date

Next:From: Rodolfo J. PaizDate: 2005-02-02 20:10:11
Subject: Re: some help
Previous:From: Bruce MomjianDate: 2005-02-02 17:26:32
Subject: Re: [NOVICE] Last ID Problem

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