Move a table to another schema

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)

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.
(740) 587-0114
www.ravensfield.com

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

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

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.