Moving tables between schemas

[m.e.bruche]

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

?

Thanks.

Max
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Shridhar Daithankar]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[John Sidney-Woollett]

You may be able to play around with the system catalogs, but I don't
know anything about that...

An easy method of moving the tables are

create table mynewschema1.table1 as select * from public.table1;
drop public.table1;

create table mynewschema2.table2as select * from public.table2;
drop public.table2;

You may need to recreate any indexes that the tables used in the public
schema, and you may need to grant rights in order to allow your users to
access the tables...

Hope that helps

John Sidney-Woollett

create

m.********@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

?

Thanks.

Max
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[m.e.bruche]

Thanks. I hadn't thought of that - that's probably easiest.

On Fri, 2004-07-30 at 10:19, John Sidney-Woollett wrote:

You may be able to play around with the system catalogs, but I don't
know anything about that...

An easy method of moving the tables are

create table mynewschema1.table1 as select * from public.table1;
drop public.table1;

create table mynewschema2.table2as select * from public.table2;
drop public.table2;

You may need to recreate any indexes that the tables used in the public
schema, and you may need to grant rights in order to allow your users to
access the tables...

Hope that helps

John Sidney-Woollett

create

m.********@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

?

Thanks.

Max
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[John Sidney-Woollett]

Be careful with any objects (functions, triggers) to make sure that are
updated to reference the new tables in their correct schemas too.

JOhn Sidney-Woollett

m.********@lse.ac.uk wrote:

Thanks. I hadn't thought of that - that's probably easiest.

On Fri, 2004-07-30 at 10:19, John Sidney-Woollett wrote:

You may be able to play around with the system catalogs, but I don't
know anything about that...

An easy method of moving the tables are

create table mynewschema1.table1 as select * from public.table1;
drop public.table1;

create table mynewschema2.table2as select * from public.table2;
drop public.table2;

You may need to recreate any indexes that the tables used in the public
schema, and you may need to grant rights in order to allow your users to
access the tables...

Hope that helps

John Sidney-Woollett

create

m.********@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

?

Thanks.

Max
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Shridhar Daithankar]

On Friday 30 Jul 2004 3:49 pm, John Sidney-Woollett wrote:

You may be able to play around with the system catalogs, but I don't
know anything about that...

An easy method of moving the tables are

create table mynewschema1.table1 as select * from public.table1;
drop public.table1;

create table mynewschema2.table2as select * from public.table2;
drop public.table2;

One can also create a table before hand using 'create table like' and then
inserting rows. This has advantage of preserving any constraints in original
table.

But this does not take care of indexes AFAIK.

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match