[postgis-users] How to create table new_table as select * from old_table
Darren Houston
dhouston at beyondcompliance.ca
Thu Jan 8 08:41:51 PST 2004
Hello everyone.
Here is the method I use if I want a permanent copy of a spatial table that
retains constraints and has an entry in geometry_columns;
--only copy over the table as a shell
CREATE TABLE spatial_table_2 AS SELECT * FROM spatial_table LIMIT 0;
--drop the spatial column
ALTER TABLE spatial_table_2 DROP COLUMN the_geom;
--create a spatial column, constraints and enter info into geometry_columns
-- -1 is the srid value, MULTIPOLYGON is the geometry type and 2 is the dimension
SELECT addgeometrycolumn('database', 'spatial_table_2', 'the_geom', -1,
'MULTIPOLYGON', 2 );
--copy data from table 1 to table 2
INSERT INTO spatial_table_2 SELECT * FROM spatial_table;
It would be easy to write a function to perform all of this with one easy SELECT
query.
Hope this helps. Anyone have a faster method?
--
Darren Houston
More information about the postgis-users
mailing list