Thread: Move table between schemas
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
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
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
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
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
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'srowtype (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