Re: Moving a table to a different schema

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(at)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(at)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(at)csb-dev=# update pg_class set relnamespace=2200 where
relname='locus';
UPDATE 1

To verify that it's in the right schema:

admin(at)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(at)gene(dot)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(at)in-machina(dot)com, GPG: 0x25EC91A0