Thread

1. Move a table to another schema

   Lee Kindness <lkindness@csl.co.uk> — 2003-11-17T16:05:04Z

   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.
   
   

2. Re: Move a table to another schema

   Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-11-17T17:24:28Z

   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)
   
   

3. Re: Move a table to another schema

   Andrew Rawnsley <ronz@ravensfield.com> — 2003-11-17T17:42:33Z

   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
   
   
   

4. Re: Move a table to another schema

   Julie May <julie@ccorb.com> — 2003-11-17T17:55:27Z

   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":
   
   
   

5. Re: Move a table to another schema

   Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-11-18T05:58:22Z

   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
   
   
   
   

6. Re: Move a table to another schema

   Lee Kindness <lkindness@csl.co.uk> — 2003-11-18T09:13:18Z

   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.