How to import GeoPackage with ogr2ogr to existing PostgreSQL table with integer PK?

Question

I'm encountering an error Null value in column violates not-null constraint while using the ogr2ogr command to import a GeoPackage into a PostgreSQL table named prj.viewshed_local.

The prj.viewshed_local table has two columns: id_local (integer) and geom (geometry), where id_local is one of the columns in the GeoPackage.

I have observed the following scenarios:

1. When there are no constraints on the prj.viewshed_local table, the ogr2ogr command works fine.
2. When id_local is defined as not null in the prj.viewshed_local table, the ogr2ogr command still works without any issues.
3. However, when id_local is defined as not null and there is a constraint that id_local is the primary key, I encounter an error message stating "null value in column 'id_local' of relation 'viewshed_local' violates not-null constraint."

I'm confused because in scenario 2, where the id_local column is defined as not null, the ogr2ogr command executes successfully. Furthermore, I have verified that there are no null values in the id_local column of the GeoPackage's attribute table (and data type is the same: integer).

Here is the ogr2ogr command I'm using:

ogr2ogr -f PostgreSQL PG:"host={HOST_NAME} port=5432 dbname={DB_NAME} user={USER_NAME} password={USER_PASS}" "{viewshed_dissolved}" -nln {SCHEMA}.{TABLE} -nlt PROMOTE_TO_MULTI -append --config PG_USE_COPY YES -makevalid

What could be causing this error when the primary key constraint is added? Is there any additional consideration or adjustment I should make to the ogr2ogr command to handle this constraint properly?

Answer 1

Error was caused because PK was integer. PK must be serial. See this link and this other question.