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.


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 :)


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.

  • 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 Jan 9 '14 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 Jan 9 '14 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 Jan 10 '14 at 0:48
  • The date in the bad record is clearly bad, it's only 7 characters long '2130803'. Should be '20130803'. – Paul Ramsey Jan 10 '14 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 Jan 10 '14 at 3:20

I used the Spatial Data Manager, a Windows graphical tool to load shapefiles and file geodatabases in many spatial db.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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