Re: [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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@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.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

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

[Mr. Puneet Kishor]

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@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

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

[Robert Buckley]

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);

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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users