[postgis-users] error loading data using shp2pgsql

[Dylan Keon]

Steffen Macke wrote:
>> Hi, I'm just getting started with PostGIS.  I tried loading two 
>> shapefiles for practice...cities (point) and rivers (line).  The first 
>> went just fine, the second is giving me errors.
> 
> could you try the CVS version of shp2pgsql? It contains some fixes
> regarding NULL values and proper string escaping.
> A CVS snapshot should be available on the download page.

The CVS snapshot is installed and seems to be working fine (you were 
right that I forgot to edit the Makefile this morning...dumb mistake on 
my part :-)

I was still getting a parsing error, but found the problem after some 
digging.  One column in the rivers shapefile contains null values only. 
  shp2pgsql assigned that column an int4 datatype, and inserted an 
asterisk into each row of that column in the rivers.sql output. 
Postgres choked when trying to load the first asterisk it encountered. 
Changing that column from int4 to varchar allowed the shapefile to load 
completely, with no errors.  I wouldn't want to keep it that way, but it 
worked for testing.  The question is, why did shp2pgsql output '*' 
instead of '' for that column?  Here is the error I was originally getting:

========================================================================
psql:rivers.sql:4: ERROR:  pg_atoi: error in "*": can't parse "*"
========================================================================

OK, on to the main error...I'm wondering if this error (the duplicate 
key insertion error) is related to dropping tables.  For example, when I 
'drop table cities', I am not able to then reload that same table.  I 
receive the following error:

========================================================================
psql:cities.sql:2: NOTICE:  Error occurred while executing PL/pgSQL 
function addgeometrycolumn
psql:cities.sql:2: NOTICE:  line 39 at execute statement
psql:cities.sql:2: ERROR:  Cannot insert a duplicate key into unique 
index gc_pk
========================================================================

This error is reproducible...it doesn't matter which shapefiles I 
load/drop/reload.  Am I dropping tables incorrectly?  Does the gc_pk 
index need to be rebuilt before reloading a shapefile that was 
previously loaded?

Thanks for any insights,
Dylan