[postgis-users] copying tables from one schema to another

[Mathieu Basille]

You can use something along that line:

CREATE SCHEMA new_schema;
ALTER TABLE table_name SET SCHEMA new_schema;

(either with pgdamin or with psql directly)

I personally find it easier to set the schema during the importation 
step with shp2psql/raster2psql, e.g. using:

raster2pgsql -r your_raster.tif -t data.your_raster -l 1 -k 64x64 -s 
26919 -I -M -o your_raster.sql

which will directly import 'your_raster' into the schema 'data'. I ran 
into some problems with indexes by altering schemas*, and I now tend to 
favour the direct import into the intended schema (and it's also quicker 
as it involves less code).

Mathieu.


* After moving a given table in a different schema, I couldn't import 
another table with the same name in the public schema because of the 
associated index, and I add to merely drop the table. There is probably 
a solution to this problem, but I couldn't find it and the drop was a 
quick and easy solution for me...


Le 22/09/2011 08:16, Mr. Puneet Kishor a écrit :
>
> On Sep 22, 2011, at 6:29 AM, Robert Buckley wrote:
>
>> Hi,
>>
>> As a follow-up to my earlier post (Re: [postgis-users] schemas and postgis data) I have another question.
>>
>> I had originally put all my data into the public schema. I want to move my geodata to the schema "data", so I just used to following command in the pgadmin3 sql editor
>>
>> create table data.new_table as (select * from public.old_table);
>
>
> Don't recreate the tables. Just alter the schema. Check the Pg docs for changing the schema.
>
>
>>
>> although this works, the schema is not copied..ie the primary key and other contraints are missing.
>>
>> So i have done this
>>
>> # first create new table and copy schema
>> create table data.table_2(like table_1 including defaults including constraints including indexes);
>>
>> #then copy data into table_2
>> insert into data.table2(select * from public.table_1);
>>
>> Is there a better/quicker/safer way of doing this? possibly with pgadmin3?_______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 

~$ whoami
Mathieu Basille, Post-Doc

~$ locate
Laboratoire d'Écologie Comportementale et de Conservation de la Faune
+ Centre d'Étude de la Forêt
Département de Biologie
Université Laval, Québec

~$ info
http://ase-research.org/basille

~$ fortune
``If you can't win by reason, go for volume.''
Calvin, by Bill Watterson.