[postgis-users] ": can't parse "ne 1, pg_atoi: error in " & empty strings in shp2pgsql output...

[Mike Leahy]

Hello all,

 

I’ve just installed Cygwin (on WinXP) and compiled the latest available 
versions of PostgreSQL/PostGIS.

When I run a COPY query to import data from a tab-separated DOS-text file I 
get the following message: 

": can't parse "ne 1, pg_atoi: error in "

Sometimes it’s followed by a number.  I know this is caused because my data 
files are windows text files - when I convert the files to UNIX text files 
using UltraEdit32, it works fine (though I now have to specify that empty 
strings are null values).  I know that this problem is specifically to do with 
the PostgreSQL - but I'm just curious if anyone on this list knows of an easy 
fix that doesn’t require converting all my files...perhaps a setting in Cygwin 
or something (I’ve already tried installing it with the DOS file setting, and 
I’ve tried compiling PostgreSQL/PostGIS with all the fopen or popen commands 
with the t argument for text).
 
My other problem, more relevant to PostGIS, is that in some cases when I 
import a spatial table from an SQL file which I’ve created using the shp2pgsql 
command, if there are null values in numeric columns it writes two single 
quotes in the insert statement (i.e., an empty string rather than a NULL 
value).  I know this is a problem for the newer version of PostgreSQL (i.e., 
in the newer versions, an empty string can’t be used as a null value for a 
numeric column).  I looked at the code in shp2pgsql.c, but I really can’t see 
where it’s deciding whether to put NULL or ‘’ for an empty record in a numeric 
column.  It doesn’t seem to have any problem identifying null strings (i.e., 
there are NULL values in the file, but only in the string columns).  Perhaps 
the actual problem is that the NULL/empty strings have been reversed, where 
empty strings are getting NULLs, and empty numbers are getting ‘’s ...  This 
is a bigger pain to fix, because I have to replace all ‘’ with NULL in my sql 
file - meaning that any string columns that had empty strings now have null 
values instead.  I don’t think that will affect me for my work, but it’s 
probably better to keep empty strings when possible.

I don’t suppose anyone would have any updated code for shp2pgsql that reflects 
this issue?

Kind regards,
Mike


----------------------------------------
This mail sent through www.mywaterloo.ca