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
|