On Friday 30 Jul 2004 3:56 pm, m.e.bruche@lse.ac.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