I have data stored in an AWS RDS PostgreSQL database with the PostGIS extension enabled. When I connect to the data from within QGIS (2.18.2) the data types are not preserved. For example a column of type 'bigint' (in PostgreSQL)

enter image description here

is converted to qlonglong int8 (in QGIS): enter image description here

The problem arises when I export the data to another file format (e.g. GPKG). integer columns are saved as doubles. The data type should be integer 64 bit / 8 byte. How can I convert a postgis table with bigint to geopackage without converting the bigint to double? (preferably without extra steps)

PostgreSQL version:

PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

PostGIS version:

POSTGIS="2.3.4 r16009" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.9.1" LIBJSON="0.12" TOPOLOGY RASTER
  • 1
    were there any intermediate steps included? did you edit the layer? – ThingumaBob Nov 28 '18 at 13:29
  • Yes, I joined a csv layer to the table. It seems like this caused the problem. – RutgerH Nov 28 '18 at 14:01
  • I don't think QGIS is not changing the data types, it just reads them differently. For example, we have smallint in the PGIS database, but QGIS reads them as int2 - for all intents and purposes, the data is the same. – DPSSpatial Nov 28 '18 at 16:19

I can't seem to reproduce your issue, neither with QGIS 2.18 nor 3.4 (on Ubuntu 18.04, PostgreSQL 10/PostGIS 2.5); while QGIS uses qlonglong internally, the exported GPKG DB layer reports integer64 (note that I haven't altered the layer in any way).

Anyway, ogr2ogr should convert src type to the appropriate dst type (i.e. SQLite's unique value typing system, INTEGERs (integer64) in this case):

ogr2ogr -f GPKG path/to/dst.gpkg PG:"host=<host> port=<port> dbname=<dbname> user=<user> password=<password>" schema.table -append
  • ...not sure if directly drag'n'drop the PostGIS layer into the GPKG in the Browser can help avoiding type conversions? – ThingumaBob Nov 28 '18 at 13:27
  • 1
    It seems like an intermediate Join caused the errors. – RutgerH Nov 28 '18 at 14:02

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.