Bug report #21745
PostgreSQL identity column not recognized properly
|Affected QGIS version:||3.6.1||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||29560|
Having the following table definition:
CREATE TABLE test (id integer generated always as identity primary key, geom geometry(point, 4326));
features cannot be added to QGIS, because the Feature Attributes dialogue requires id column to be not null. However, when any value is filled, the saving fails with the following error:
Could not commit changes to layer test Errors: ERROR: 1 feature(s) not added. Provider errors: PostGIS error while adding features: ERROR: cannot insert into column "id" DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override.
According to https://blog.2ndquadrant.com/postgresql-10-identity-columns/, the syntax of identity columns in PostgreSQL conforms to the SQL standard and it's been around for a while.