OGR integer type to PostgreSQL BIGINT?

Question

I have a problem with integer types when importing a shapefile created in OGR into PostGIS.

I started off with a field created as ogr.OFTInteger64 in OGR/pyqgis as follows:

field_defn = ogr.FieldDefn( fld, ogr.OFTInteger64 ) 
lyr.CreateField ( field_defn )

The field type shows as type: qlonglong, and type name: Integer64 in QGIS.

But after importing the shapefile into PostGIS with shp2pgsql,

shp2pgsql -Id -s 4326 my_lyr.shp my_lyr | psql

the field ended up being a double precision field in \d+, and I had to convert values in this field back to BIGINT before use.

According to PostgreSQL documentation, both bigint and double precision are types 8 byte (64 bit). How can I avoid the confusion and get the intended BIGINT field in PostGIS?

• user30184: What happens if you use ogr2ogr for the conversion?
• Rob Skelly: I guess if worst comes to worst, you can pipe the output from shp2pgsql through sed to fix the column definitions...
• Evan Carroll: Can you provide a test shapefile?
• Evan Carroll: What version of gdal are you using?

Answer 1

A quick look into this, CreateField is a wrapper around C++'s Layer_CreateField. You can see that bFID64 is supported in the pgdatasource. Perhaps you should check out ogr2ogr -f "PostgreSQL" instead of using shp2pgsql.

PostGIS provides shp2pgsql (not GDAL): it seems to not support 64 bit ints yet, and instead defaults to FTDouble. You can see all the types supported here or here. You can see the function that sets the types here.

Update, I opened a bug report

These two code bases seem to have a lot of overlap. Because gdal is used by PostGIS, it seems that PostGIS should work to get their changes to the ogr data source merged upstream and maintained there.

• tinlyx: FYI, ogr2ogr -f "PostgreSQL" generated a different error: gis.stackexchange.com/questions/254671 @user30184