[postgis-users] How to create table new_table as select * from old_table

[Darren Houston]

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