Обсуждение: Batch Insert tables

    I'm experimenting with postgres with postgis for use with
mapserver.  I have loaded approximately 650 shapefiles into postgres,
each as a separate table.  The name of each table is a unique identifier
for as species.  Each represents a species distribution and may consist
of one or more polygons.  What I would like to do is combine the 650
tables into a single table (Preferably without reloading them into
postgres with shp2pgsql).  The complication is that I need to add a
column to each of the tables that incorporates the name of the table as
a unique identifier before merging the tables.  Is their an easy way to
do this in postgres( 7.4) ???  Maybe I don't need to do this, but it
seems like the easiest way to set the database up (for the schema I have
in mind).

On Feb 2, 2005, at 19:53, Jeremiah Knoche wrote:

>    I'm experimenting with postgres with postgis for use with
> mapserver.  I have loaded approximately 650 shapefiles into postgres,
> each as a separate table.  The name of each table is a unique
> identifier for as species.  Each represents a species distribution and
> may consist of one or more polygons.  What I would like to do is
> combine the 650 tables into a single table (Preferably without
> reloading them into postgres with shp2pgsql).  The complication is
> that I need to add a column to each of the tables that incorporates
> the name of the table as a unique identifier before merging the
> tables.  Is their an easy way to do this in postgres( 7.4) ???  Maybe
> I don't need to do this, but it seems like the easiest way to set the
> database up (for the schema I have in mind).

INSERT INTO comb_table (species_id, other_col1, other_col2, ... )
SELECT 'foo' as species_id, other_col1, other_col2, ...
from foo_species_table;

Note this is all one expression. (See the INSERT SQL command
reference[1] for more details.) In this incarnation, you would repeat
this 650 times, or perhaps write a script that would do it for you.

Hope this helps.

Michael Glaesemann
grzm myrealbox com

PS Your revised schema sounds much better than the existing "1 table
per species" schema. Good luck :)

[1] http://www.postgresql.org/docs/7.4/interactive/sql-insert.html