From: | Reece Hart <rkh(at)gene(dot)COM> |
---|---|
To: | jim(at)nasby(dot)net |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Moving a table to a different schema |
Date: | 2003-05-30 20:28:53 |
Message-ID: | 1054326533.19317.201.camel@tallac |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | btober | 2003-05-30 21:29:40 | Re: Moving a table to a different schema |
Previous Message | Ian Harding | 2003-05-30 20:07:21 | Re: implicit abort harmful? |