Thread: Moving tables between schemas
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
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
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.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 > > ? > > Thanks. > > Max > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
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.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 > > > > ? > > > > Thanks. > > > > Max > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
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.e.bruche@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.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 >>> >>>? >>> >>>Thanks. >>> >>>Max >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >>
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