PostgreSQL identity column not recognized properly

[qgib]

Author Name: Michal Zimmermann (Michal Zimmermann)
Original Redmine Issue: 21745
Affected QGIS version: 3.6.1
Redmine category:data_provider/postgis

---

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.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Applied in changeset 59dd851.

---

• done_ratio was changed from 0 to 100
• status_id was changed from Open to Closed

[qgib]

Author Name: Michal Zimmermann (Michal Zimmermann)

---

Jürgen Fischer wrote:

Applied in changeset 59dd851.

How do I tell which QGIS version will include this fix?

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Michal Zimmermann wrote:

Jürgen Fischer wrote:

Applied in changeset 59dd851.

How do I tell which QGIS version will include this fix?

It's in master and will be in 3.8.

[zimmicz]

Writing to identity column works in v 3.8.0, yet it seems a bit hackish to me. Even though the database column is defined as id integer generated always as identity, QGIS issues the following query:

INSERT INTO "table"("id",...) OVERRIDING SYSTEM VALUE VALUES...

which overrides the value generated by the database itself. I'd say the proper solution to this would be to respect the GENERATED ALWAYS | BY DEFAULT column definition. Yet I'm not sure if that's not out of QGIS scope. What's your take on that, @jef-n?

[andywicht]

In QGIS 3.10.2 it is still not possible to create a new feature in a table with a PK column defined like that. The field is marked as NOT NULL, therefore QGIS expects a value, but it is impossible to add a value because one can not type in that field.

[gioman]

In QGIS 3.10.2 it is still not possible to create a new feature in a table with a PK column defined like that. The field is marked as NOT NULL, therefore QGIS expects a value, but it is impossible to add a value because one can not type in that field.

@andywicht can you elaborate? I'm on 3.10.2 and I can add feature to a table that as PK like the following:

The gid has the NOT NULL constraint, but also is SERIAL so it gets autofilled. If you don't have the serial property is up to you to add a proper value for the pk column.

[andywicht]

@gioman I have to correct my statement.
If a table is defined as follows:

CREATE SCHEMA test;

CREATE TABLE test.test (
    gid int8 NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    geom geometry(polygon)
);

INSERT INTO test.test (geom) 
VALUES ('POLYGON EMPTY'::geometry);

SELECT * 
FROM test.test;

gid|geom         
---|-------------
  1|POLYGON EMPTY

Then QGIS does not understand that the DB will autogenerate a value and one would see the window as it can be seen in the previous screenshot.

You can actually put in an ID manually, which defeats the purpose of an autogenerated ID.
There is a sequence present when the table is defined like that. But QGIS does not acknowledge that.

When defining the primary key as "SERIAL" (default value incrementing the sequence) everything works as expected.

Ignore the fact that I wrote you cannot input anything (I think I tried to add characters but the datatype forbids that. So that's fine).

[glw]

I am still seeing this as an issue. On QGIS 3.22.2

After:
alter table sometable alter column fpd_uid add generated always as identity;

[posikifi]

Works on 3.22.4. Have you also define that fpd_uid is primary key?

[peoute]

I ran in to an issue by setting both a bigint as identity and having a second primary key of a uuid. QGIS wouldn't get the bigint primary key from the DB. When removed the uuid as a primary key and only had the bigint as primary key, QGIS would allow a feature to be created and get the nextval from the DB.

[JKBWP]

I'm still (or again?) having the same problem on QGIS 3.22.10 with the column "id int8 NOT NULL GENERATED ALWAYS AS IDENTITY" defined as primary key.

[elpaso]

Wierd, it's tested: https://github.com/qgis/QGIS/pull/34017/files#diff-9d72c55672446858ae068c676202964429e249c9b1db2657c32ec34418d904b9

[JKBWP]

I think I figured it out now: the problem only exists when the option 'Evaluate default values on provider side' (Project Properties / Data Sources Properties) is activated. Without it, everything works just fine...