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

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