pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMove table between schemas
6+ messages / 4 participants
[nested] [flat]
* Move table between schemas
@ 2004-11-16 09:02 Markus Schaber <schabios@logi-track.com>
2004-11-16 11:05 ` Re: Move table between schemas Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Markus Schaber @ 2004-11-16 09:02 UTC (permalink / raw)
To: pgsql-sql
Hello,
Is there an easy way to move a table to another schema in PostgreSQL 7.4?
ALTER TABLE and ALTER SCHEMA don't have this options.
Thanks,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move table between schemas
2004-11-16 09:02 Move table between schemas Markus Schaber <schabios@logi-track.com>
@ 2004-11-16 11:05 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-11-16 12:30 ` Re: Move table between schemas Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-20 10:44 ` Re: Move table between schemas Markus Schaber <schabios@logi-track.com>
0 siblings, 2 replies; 6+ messages in thread
From: Andrew Sullivan @ 2004-11-16 11:05 UTC (permalink / raw)
To: pgsql-sql
On Tue, Nov 16, 2004 at 10:02:34AM +0100, Markus Schaber wrote:
> Hello,
>
> Is there an easy way to move a table to another schema in PostgreSQL 7.4?
>
> ALTER TABLE and ALTER SCHEMA don't have this options.
CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable
oughta work.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move table between schemas
2004-11-16 09:02 Move table between schemas Markus Schaber <schabios@logi-track.com>
2004-11-16 11:05 ` Re: Move table between schemas Andrew Sullivan <ajs@crankycanuck.ca>
@ 2004-11-16 12:30 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-16 22:09 ` Re: Move table between schemas Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 6+ messages in thread
From: Achilleus Mantzios @ 2004-11-16 12:30 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
O Andrew Sullivan Ýãñáøå óôéò Nov 16, 2004 :
> On Tue, Nov 16, 2004 at 10:02:34AM +0100, Markus Schaber wrote:
> > Hello,
> >
> > Is there an easy way to move a table to another schema in PostgreSQL 7.4?
> >
> > ALTER TABLE and ALTER SCHEMA don't have this options.
>
> CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable
>
> oughta work.
What about indexes, constraints, sequences,etc...???
>
> A
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move table between schemas
2004-11-16 09:02 Move table between schemas Markus Schaber <schabios@logi-track.com>
2004-11-16 11:05 ` Re: Move table between schemas Andrew Sullivan <ajs@crankycanuck.ca>
2004-11-16 12:30 ` Re: Move table between schemas Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2004-11-16 22:09 ` Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 6+ messages in thread
From: Andrew Sullivan @ 2004-11-16 22:09 UTC (permalink / raw)
To: pgsql-sql
On Tue, Nov 16, 2004 at 02:30:09PM +0200, Achilleus Mantzios wrote:
> >
> > CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable
> >
> > oughta work.
>
> What about indexes, constraints, sequences,etc...???
You'll have to create those too, I'm afraid. I don't know of a way
to move tables from one schema to another otherwise. You could do
all the dependencies with a pg_dump -t, I suspect. Not tested that,
though.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move table between schemas
2004-11-16 09:02 Move table between schemas Markus Schaber <schabios@logi-track.com>
2004-11-16 11:05 ` Re: Move table between schemas Andrew Sullivan <ajs@crankycanuck.ca>
@ 2004-11-20 10:44 ` Markus Schaber <schabios@logi-track.com>
2004-11-20 16:36 ` Re: Move table between schemas Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Markus Schaber @ 2004-11-20 10:44 UTC (permalink / raw)
To: pgsql-sql
Hi, Andrew,
On Tue, 16 Nov 2004 06:05:38 -0500
Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> > Is there an easy way to move a table to another schema in PostgreSQL 7.4?
> >
> > ALTER TABLE and ALTER SCHEMA don't have this options.
>
> CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable
> oughta work.
This has several drawbacks I can see for now:
- For large tables (some Gigs of data), this needs a long time and
produces heavy I/O load on the server.
- You need twice the disk space until you can delete the old table.
- Indices, triggers, sequences and constraints are not transferred. When
the target schema is first in the search path of the application, this
means that the application works on an incomplete table until I finished
the transition..
- It does not automatically convert views or foreign key constraints
that point to the table.
- The operation is not atomic, thus there may be inserts and updates
into the old table that get lost while the "CREATE...SELECT...;DROP
TABLE...;" runs.
Is there any (possibly ugly, fiddling with system tables) atomic way to
move a table between schemas? It should not be much more difficult
compared to e. G. renaming a table to implement this, so I couuld not
imagine this does not exist until I tried to find out how to do it.
Thanks,
markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Move table between schemas
2004-11-16 09:02 Move table between schemas Markus Schaber <schabios@logi-track.com>
2004-11-16 11:05 ` Re: Move table between schemas Andrew Sullivan <ajs@crankycanuck.ca>
2004-11-20 10:44 ` Re: Move table between schemas Markus Schaber <schabios@logi-track.com>
@ 2004-11-20 16:36 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-11-20 16:36 UTC (permalink / raw)
To: Markus Schaber <schabios@logi-track.com>; +Cc: pgsql-sql
Markus Schaber <schabios@logi-track.com> writes:
> Andrew Sullivan <ajs@crankycanuck.ca> wrote:
>> CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable
>> oughta work.
> - The operation is not atomic, thus there may be inserts and updates
> into the old table that get lost while the "CREATE...SELECT...;DROP
> TABLE...;" runs.
You'd deal with that by taking a lock on the old table. Any sort of
catalog-munging solution would have to do the same (though admittedly it
wouldn't need to hold the lock as long).
> Is there any (possibly ugly, fiddling with system tables) atomic way to
> move a table between schemas?
Offhand:
* update table's pg_class.relnamespace field
* update pg_depend entry that links table to namespace
* repeat for table's rowtype (pg_type entry)
* repeat for each index on the table
* repeat for each constraint on the table
Not sure if that's all the places or not...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-11-20 16:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-16 09:02 Move table between schemas Markus Schaber <schabios@logi-track.com>
2004-11-16 11:05 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-11-16 12:30 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-16 22:09 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-11-20 10:44 ` Markus Schaber <schabios@logi-track.com>
2004-11-20 16:36 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox