1

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
3
  • 1
    were there any intermediate steps included? did you edit the layer?
    – geozelot
    Nov 28, 2018 at 13:29
  • Yes, I joined a csv layer to the table. It seems like this caused the problem.
    – RutgerH
    Nov 28, 2018 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. Nov 28, 2018 at 16:19

1 Answer 1

1

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
2
  • ...not sure if directly drag'n'drop the PostGIS layer into the GPKG in the Browser can help avoiding type conversions?
    – geozelot
    Nov 28, 2018 at 13:27
  • 1
    It seems like an intermediate Join caused the errors.
    – RutgerH
    Nov 28, 2018 at 14:02

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.