Обсуждение: How to "COPY schema1.table TO schema2.table" ?
Hi, how can I copy a table (including sequences etc.) to a different schema? Kind regards, Andreas
What are you using to connect to the database? Pgadmin? psql? On Wed, Jan 19, 2005 at 03:34:58PM +0100, Andreas Neumann wrote: > Hi, > > how can I copy a table (including sequences etc.) to a different schema? > > Kind regards, > > Andreas > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
Normally I use psql. Sometimes for a fast Browse phpPgAdmin. On Wed, 19 Jan 2005, Mike G. wrote: > What are you using to connect to the database? Pgadmin? psql? > > On Wed, Jan 19, 2005 at 03:34:58PM +0100, Andreas Neumann wrote: > > Hi, > > > > how can I copy a table (including sequences etc.) to a different schema? > > > > Kind regards, > > > > Andreas
I really don't use either of those. I use PgAdmin which with a few clicks displays the sql code used to create the tablesand functions. Copy and paste the coe into a new window, change the schema name, and execute. Then you can clearthe window and do a Insert into B (select * from A); I am sure the same can be done with psql but I don't know how. Sorry. On Thu, Jan 20, 2005 at 01:29:28PM +0100, Andreas Neumann wrote: > Normally I use psql. Sometimes for a fast Browse phpPgAdmin. > > On Wed, 19 Jan 2005, Mike G. wrote: > > What are you using to connect to the database? Pgadmin? psql? > > > > On Wed, Jan 19, 2005 at 03:34:58PM +0100, Andreas Neumann wrote: > > > Hi, > > > > > > how can I copy a table (including sequences etc.) to a different schema? > > > > > > Kind regards, > > > > > > Andreas > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Ok, I see. To construct the table new from scratch is of course possible independent from the used frontend. I was hoping for something more convenient, since I will do that probably more often. But thanks so far, it is definitely a solution. Is there a convenient way to move a table (or a bunch of tables) to a different schema? (Rebuilding and filling the table and then deleting the old one doesn't seem very efficient to me.) On Thu, 20 Jan 2005, Mike G. wrote: > I really don't use either of those. I use PgAdmin which with a few clicks displays the sql code used to create the tablesand functions. Copy and paste the coe into a new window, change the schema name, and execute. Then you can clearthe window and do a Insert into B (select * from A); > > I am sure the same can be done with psql but I don't know how. Sorry. > > On Thu, Jan 20, 2005 at 01:29:28PM +0100, Andreas Neumann wrote: > > Normally I use psql. Sometimes for a fast Browse phpPgAdmin. > > > > On Wed, 19 Jan 2005, Mike G. wrote: > > > What are you using to connect to the database? Pgadmin? psql? > > > > > > On Wed, Jan 19, 2005 at 03:34:58PM +0100, Andreas Neumann wrote: > > > > Hi, > > > > > > > > how can I copy a table (including sequences etc.) to a different schema? > > > > > > > > Kind regards, > > > > > > > > Andreas > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > -- ------------------------------------------------------------------- Dipl.-Math.techn. Andreas Neumann, M.Sc. Information Services and Electronic Markets Institute for Information Engineering and Management Department of Economics and Business Engineering Universität Karlsruhe (TH) 76128 Karlsruhe, Germany Informationsdienste und elektronische Märkte Institut für Informationswirtschaft und -management Fakultät für Wirtschaftswissenschaften Gebäude 20.20, Raum 151 Universität Karlsruhe (TH) 76128 Karlsruhe, Germany Phone: +49 721 608-8404 Fax: +49 721 608-8403 Email: andreas.neumann@em.uni-karlsruhe.de WWW: http://www.em.uni-karlsruhe.de/mitarbeiter/ane/index_eng.php -------------------------------------------------------------------
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/
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/
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 > > >
On Wed, Feb 02, 2005 at 02:58:35PM -0500, Tim Goodaire wrote: > You can copy a table from one schema to another by doing something like > this: > > SET search_path TO schema1,schema2; This isn't necessary for the CREATE TABLE statement below since you're already using schema-qualified table names. There might be other reasons for doing so, however. > CREATE TABLE schema2.mytable AS SELECT * FROM schema1.mytable; Note that this copies only data -- you won't get constraints, default values, indexes, triggers, rules, etc. -- Michael Fuhr http://www.fuhr.org/~mfuhr/