I'm having trouble when trying to upload a shape file in postgresql/PostGIS. There's a field with type "date", but actually there'r two elements with content : '20012060' and it returns ERROR when I start shp2pgsql. Is there any way to change all fields from dbf to character varying, using shp2pgsql or any other extension from PostGIS?

enter image description here

  • What does SHOW DATESTYLE; in Postgres tell you? My guess is that you will need to set your datestyle on the input table to YMD, ie, SET datestyle = "ISO, YMD"; – John Powell Jun 5 '19 at 10:37
  • Actually, looking more closely, 20012060 is clearly not a date in any format. So, to answer your original question, one of the switches to shp2pgsql is -a, which means, append. So, you can create the table with the problem columns defined as varchar and then run shp2pgsql -a ..... – John Powell Jun 5 '19 at 10:45

Is using ogr2ogr an option? If so you should be able to use COLUMN_TYPES option, see the help section for PostgreSQL driver.

Example of importing shape to postgis with the integer field "id" imported as text

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres dbname=testdb password=somepassword" "/home/bera/GIS/data/test/polys.shp" -lco COLUMN_TYPES="id=text"

enter image description here

Data type of columns not specified seem to be set automatically, for example the "sometext" column.

  • This doesn't exactly answer the original question, as valid an approach as it may be. – John Powell Jun 5 '19 at 10:46

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.