[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] copying tables from one schema to another
From:       Mathieu Basille <basille () ase-research ! org>
Date:       2011-09-22 12:31:57
Message-ID: 4E7B2ABD.8000402 () ase-research ! org
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]