QGIS DB Manager import Primary Key issue

Question

I have a number of layers that I want to import into a GeoPackage using DB Manager > Import Layer. This data will end up in a process that controls data entry and it needs to comply with corporate naming conventions - needs to be "gid". On the import screen I tick the Primary Key and entered "gid". The import tool successfully imports the data, but ignores anything I enter as the PK, it uses "fid" every time.

I have tried different options, "gid", "kid", even left this unticked - same result = "fid". None of my tables have "gid" as an existing attribute field.

Is this a bug and is there a workaround? BTW, I use QGIS 3.10

Answer 1

According to the GeoPackage standard http://www.geopackage.org/spec121/index.html#example_feature_table_sql the primary key column must be of type "integer autoincrement". The standard has an example SQL for creating a feature table and it begins

CREATE TABLE sample_feature_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

Only the INTEGER AUTOINCREMENT part is mandatory. The name of the PK field does not need to be "id" and GDAL and QGIS are using a default name "fid" for the primary key as you have noticed. The user interface of the QGIS DB Manager is lacking an option for naming the PK field into something else but you can do that with ogr2ogr.

The GeoPackage driver manual https://gdal.org/drivers/vector/gpkg.html lists a layer creation option

FID:

Column name to use for the OGR FID (primary key in the SQLite database). Default to “fid”

Use ogr2ogr like this

ogr2ogr -f gpkg -lco fid=gid gid.gpkg test.gpkg

ogrinfo gid.gpkg -al
INFO: Open of `gid.gpkg'
      using driver `GPKG' successful.

Layer name: test
Geometry: Unknown (any)
Feature Count: 2
Extent: (340.000000, 349.000000) - (428.000000, 380.000000)
Layer SRS WKT:
(unknown)
FID Column = gid
Geometry Column = geom

NOTE I am not sure what the options "Primary key" and "Geometry column" in the DB Manager UI should do but by looking at the source code at https://github.com/qgis/QGIS/blob/master/python/plugins/db_manager/dlg_import_vector.py I suppose that user can select some existing field from the source data to be used as primary key, as well as select which geometry column will be imported if source data has many of them.