Postgres Pro
Downloads
Home   >   mailing lists

Thread: Move a table to another schema

Move a table to another schema

From
Lee Kindness
Date:
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.

Re: Move a table to another schema

From
Alvaro Herrera
Date:
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)

Re: Move a table to another schema

From
Andrew Rawnsley
Date:
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

Re: Move a table to another schema

From
"Julie May"
Date:
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":

Re: Move a table to another schema

From
Lee Kindness
Date:
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.

Re: Move a table to another schema

From
Shridhar Daithankar
Date:
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