Thread: Moving a table to a different schema
Is there a command to move an existing table to a different schema? -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Thu, May 29, 2003 at 11:14:22AM -0500, Jim C. Nasby wrote:
> Is there a command to move an existing table to a different schema?
CREATE TABLE targetschem.tablename AS
SELECT * FROM sourceschem.tablename
would work.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Well, that would *copy* the table... I just want to *move* it. :) On Thu, May 29, 2003 at 02:31:04PM -0400, Andrew Sullivan wrote: > On Thu, May 29, 2003 at 11:14:22AM -0500, Jim C. Nasby wrote: > > Is there a command to move an existing table to a different schema? > > CREATE TABLE targetschem.tablename AS > SELECT * FROM sourceschem.tablename > > would work. > > A > > -- > ---- > Andrew Sullivan 204-4141 Yonge Street > Liberty RMS Toronto, Ontario Canada > <andrew@libertyrms.info> M2P 2A8 > +1 416 646 3304 x110 > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On 5/29/03 5:23 PM, "Jim C. Nasby" <jim@nasby.net> wrote: > Well, that would *copy* the table... I just want to *move* it. :) > > On Thu, May 29, 2003 at 02:31:04PM -0400, Andrew Sullivan wrote: >> On Thu, May 29, 2003 at 11:14:22AM -0500, Jim C. Nasby wrote: >>> Is there a command to move an existing table to a different schema? >> >> CREATE TABLE targetschem.tablename AS >> SELECT * FROM sourceschem.tablename So perhaps finish with DROP TABLE sourceschem.tablename ? :> -- sgl ======================================================= Steve Lane Vice President The Moyer Group 14 North Peoria St Suite 2H Chicago, IL 60607 Voice: (312) 433-2421 Email: slane@moyergroup.com Fax: (312) 850-3930 Web: http://www.moyergroup.com =======================================================
On Thu, 2003-05-29 at 09:14, Jim C. Nasby wrote:
Imagine moving unison.locus to public.locus. First:
I've used this without problems, but you must satisfy yourself.
Good luck,
Reece
WARNING: The following works for me, but there might be other ramifications that I'm not aware of.Is there a command to move an existing table to a different schema?
Imagine moving unison.locus to public.locus. First:
admin@csb-dev=# select relname,relnamespace from pg_class where relname='locus';relnamespace is the oid of the schema (aka namespace) in pg_namespace. So:
relname | relnamespace
---------+--------------
locus | 531465
admin@csb-dev=# select oid,* from pg_namespace;And if I wanted to make locus public, I could do this:
oid | nspname | nspowner | nspacl
--------+------------+----------+----------------
11 | pg_catalog | 1 | {=U}
99 | pg_toast | 1 | {=}
2200 | public | 1 | {=UC}
531465 | unison | 1 | {=U,admin=UC}
admin@csb-dev=# update pg_class set relnamespace=2200 where relname='locus';To verify that it's in the right schema:
UPDATE 1
admin@csb-dev=# \dt public.locus
List of relations
Schema | Name | Type | Owner
--------+-------+-------+-------
public | locus | table | admin
I've used this without problems, but you must satisfy yourself.
Good luck,
Reece
-- Reece Hart, Ph.D. rkh@gene.com, http://www.gene.com/ Genentech, Inc. 650/225-6133 (voice), -5389 (fax) Bioinformatics and Protein Engineering 1 DNA Way, MS-93 http://www.in-machina.com/~reece/ South San Francisco, CA 94080-4990 reece@in-machina.com, GPG: 0x25EC91A0 |
> On Thu, 2003-05-29 at 09:14, Jim C. Nasby wrote: > >> Is there a command to move an existing table to a different schema? > > WARNING: The following works for me, but there might be other > ramifications that I'm not aware of. > > Imagine moving unison.locus to public.locus. First: > Somebody earlier suggested using pg_dump. Up to this point I've only used pg_dump to dump the entire schema and data for backup and occasionally modified the output for changes to table definitions (during database design/development, not in a production environment, however). What I recently "discovered" was using pg_dump to dump only a single table. It produces output the lets you recreate the table, reload data, and recreate constraints and triggers, not just the table definition. You could easily modifiy that output and change any occurances of the old schema name to the new schema name. I at first thought I had a problem with that because the views and associated rules would be lost when you dropped the old table prior to reload from the modified script. But then I tried doing a pg_dump for a single table, but instead of a table I specified a view defined on the subject table. The ouput of pg_dump for a view includes any rules defined on the view as well as its definition, so you don't lose those. The only thing you might lose is foreign key constraints on OTHER tables that reference the subject table. ~Berend Tober
> On Thu, 2003-05-29 at 09:14, Jim C. Nasby wrote: > >> Is there a command to move an existing table to a different schema? > > WARNING: The following works for me, but there might be other > ramifications that I'm not aware of. > > Imagine moving unison.locus to public.locus. First: > Somebody earlier suggested using pg_dump. Up to this point I've only used pg_dump to dump the entire schema and data for backup and occasionally modified the output for changes to table definitions (during database design/development, not in a production environment, however). What I recently "discovered" was using pg_dump to dump only a single table. It produces output the lets you recreate the table, reload data, and recreate constraints and triggers, not just the table definition. You could easily modifiy that output and change any occurances of the old schema name to the new schema name. I at first thought I had a problem with that because the views and associated rules would be lost when you dropped the old table prior to reload from the modified script. But then I tried doing a pg_dump for a single table, but instead of a table I specified a view defined on the subject table. The ouput of pg_dump for a view includes any rules defined on the view as well as its definition, so you don't lose those. The only thing you might lose is foreign key constraints on OTHER tables that reference the subject table. ~Berend Tober
Reece Hart <rkh@gene.COM> writes:
>> Is there a command to move an existing table to a different schema?
> WARNING: The following works for me, but there might be other
> ramifications that I'm not aware of.
> admin@csb-dev=# update pg_class set relnamespace=2200 where
> relname='locus';
You would also need to update the namespace links for the associated
rowtype and for any indexes and constraints on the table. At least if
you wanted things to be clean. I am not sure whether anything critical
depends on these objects being in the same namespace as their parent
table, but certainly the system is not designed to cope with them not
being there.
regards, tom lane