Moving a table to a different schema

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:

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:

admin@csb-dev=# select relname,relnamespace from pg_class where
relname='locus';
relname | relnamespace
---------+--------------
locus | 531465

relnamespace is the oid of the schema (aka namespace) in pg_namespace.
So:

admin@csb-dev=# select oid,* from pg_namespace;
oid | nspname | nspowner | nspacl
--------+------------+----------+----------------
11 | pg_catalog | 1 | {=U}
99 | pg_toast | 1 | {=}
2200 | public | 1 | {=UC}
531465 | unison | 1 | {=U,admin=UC}

And if I wanted to make locus public, I could do this:

admin@csb-dev=# update pg_class set relnamespace=2200 where
relname='locus';
UPDATE 1

To verify that it's in the right schema:

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