2

I'm attempting to import a shapefile into postgis via shp2pgsql.

Among the fields in the shapefile's .dbf file is a date field with format yyyymmdd, which is a valid date format for postgresql.

The date field as seen in QGIS

However, the sql generated by shp2pgsql outputs the date field with quotes:

enter image description here

And that means when I run the command, I get an 'invalid input syntax' error:

enter image description here

What do I need to do to get shp2pgsql to format the value correctly so it can be loaded into my database? I need to automate this loading process, so anything that involves opening up the SQL and editing the file should be a last resort.

Thanks

EDIT: Turns out it was a bad record causing the error. Of course an insert statement uses quotes around the values. I get stupid after midnight :)

Improve this question

2 Answers 2

Reset to default
1

Check your data table, and in particular the row for "Whiskey Complex". The problem is not "quoting the date", since clearly previous records that receive exactly the same treatment are being successfully inserted. I suspect you might just have a bad record in your data.

Improve this answer
5
  • You were right about the bad data record. (And no, quoting the date had nothing to do with it...it was 3 am and I was getting punchy).
    – aidian holder
    Commented Jan 9, 2014 at 22:29
  • Two Questions: 1.How can I inspect the file for valid data. The SHP appeared OK in QGIS, the DBF looked good in OpenOffice - even when I went back and specifically looked at that bad record. Anything else you can think of to inspect a .shp or .dbf file for validity (preferably programatically)? 2. Can you see anything invalid about the date syntax referenced in that error message? Looked good to me -- identical to other records that worked fine -- but apparently not to postgresql.
    – aidian holder
    Commented Jan 9, 2014 at 22:52
  • @aidianholder Those are good questions. I recommend you see if this site has those answered already. If not, post them, especially the one about checking for validity.
    – SaultDon
    Commented Jan 10, 2014 at 0:48
  • The date in the bad record is clearly bad, it's only 7 characters long '2130803'. Should be '20130803'.
    – Paul Ramsey
    Commented Jan 10, 2014 at 2:12
  • 2 for 2. I must have stared at that for an hour trying to figure it out. Thanks for bailing me out.
    – aidian holder
    Commented Jan 10, 2014 at 3:20
0

I used the Spatial Data Manager, a Windows graphical tool to load shapefiles and file geodatabases in many spatial db.
http://www.mapet.altervista.org

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.