Move a table to another schema

[Lee Kindness]

I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

UPDATE pg_class
SET relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'x001')
FROM pg_namespace
WHERE pg_class.relname = 'zxc' AND
pg_namespace.nspname = 'public' AND
pg_class.relnamespace = pg_namespace.oid

Has anyone else addressed this before? Recommendations?

Thanks, L.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Alvaro Herrera]

On Mon, Nov 17, 2003 at 04:05:04PM +0000, Lee Kindness wrote:

I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

You have to move all indexes, constraints, the type, etc too.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"There was no reply" (Kernel Traffic)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Andrew Rawnsley]

I would imagine the safest way would be to recreate the table in the
new schema and do a INSERT INTO ...SELECT * FROM ....
Not elegant, but perfectly safe. You mess with the pg_* catalogs at
your own risk.

On Nov 17, 2003, at 11:05 AM, Lee Kindness wrote:

I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

UPDATE pg_class
SET relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =
'x001')
FROM pg_namespace
WHERE pg_class.relname = 'zxc' AND
pg_namespace.nspname = 'public' AND
pg_class.relnamespace = pg_namespace.oid

Has anyone else addressed this before? Recommendations?

Thanks, L.

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Julie May]

When I have contemplated doing this, I figured I would just dump the
database, then alter the search patch and put in the schema name I wanted
instead of public and theoretically the tables and data should be recreated
in the new schema. I would create the new schema first.

Julie

I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Lee Kindness]

Shridhar,

Shridhar Daithankar writes:

Lee Kindness wrote:
I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

Why not just rename the schema itself? You can recreate a public
schema later..:-)

Simple, isn't it? (Unless public is a specieal schema)

Thanks Shridhar - it's good when someone has a different angle on
things!

Of course I'm using 7.3, so there is no ALTER SCHEMA - but the
catalogue magic required to rename a single schema will be a lot less
than renaming 100s of tables, indices and views!

L.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Shridhar Daithankar]

Julie May wrote:

When I have contemplated doing this, I figured I would just dump the
database, then alter the search patch and put in the schema name I wanted
instead of public and theoretically the tables and data should be recreated
in the new schema. I would create the new schema first.

Julie

I have many tables created in the "public" schema and I would like to
migrate these into an "x001" schema. Unfortunately there is no ALTER
TABLE construct for this... The following SQL would seem to move the
"zxc" table from "public.zxc" to "x001.zxc":

Why not just rename the schema itself? You can recreate a public schema later..:-)

Simple, isn't it? (Unless public is a specieal schema)

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org