Re: Moving tables between schemas

From: Shridhar Daithankar <shridhar(at)frodo(dot)hserus(dot)net>
To: "m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk" <m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Moving tables between schemas
Date: 2004-07-30 10:11:48
Message-ID: 200407301541.48304.shridhar@frodo.hserus.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Friday 30 Jul 2004 3:56 pm, m(dot)e(dot)bruche(at)lse(dot)ac(dot)uk wrote:
> Hi,
>
> In my database, I created a lot of tables before I found out about
> schemas. It's a mess!
>
> Suppose I want to get organised, and create a couple of schemas. How do
> I move existing tables into my newly created schemas?
>
> e.g.
>
> public.table1 -> mynewschema1.table1
> public.table2 -> mynewschema2.table2

You can dump the tables, edit the dump to alter the create table statement so
that the table is created in proper schema.

This could be very rudimentary depending upon size of each table and number of
tables. The local documentation of 7.4.3 indicates that either alter table or
alter schema does not support moving tables between schemas.

You could resort to catalog hacking and change the schema manually but I am
not sure how advisable and predictable it would be.

HTH

Shridhar

In response to

Browse pgsql-general by date

  From Date Subject
Next Message John Sidney-Woollett 2004-07-30 10:19:55 Re: Moving tables between schemas
Previous Message Nilabhra Banerjee 2004-07-30 09:37:51 Postgresql and OS cluster