8

I have a Multipolygon-Layer stored in a PostgreSQL/PostGIS database. The primary key field 'gid' is SERIAL. When trying to add a new polygon in QGIS (LYON 2.12.1 on OSX El Capitan), the attributes form shows me:

nextval('test_gid_seq'::regclass) for the gid field (which is the correct sequence to autoincrement the field).

When I try to save the edits, I always get an error:

ERROR:  duplicate key value violates unique constraint "test_pkey"
    DETAIL:  Key (gid)=(1) already exists.

This is strange, since the actual value in the sequence is 114. Why does QGIS tries to use 1? Is this a bug in QGIS? Or does anybody know how to avoid these problems?

4
  • 1
    Sounds like it isn't sending the full field list. It is easy to simulate this error: create table test (id serial primary key, val int); insert into test values (1);insert into test values (1); where the second insert fails with ERROR: duplicate key value violates unique constraint "test_pkey" DETAIL: Key (id)=(1) already exists. This, of course, is because there is no explicit field list. If the insert was instead insert into test (val) values (1); then it works. I'm sure you know this, but I can't see any other way that this error could arise. Feb 9, 2016 at 6:39
  • Are you sure that the actual value is 114? You can check by running SELECT nextval('test_gid_seq'::regclass) directly in postgres.
    – tilt
    Feb 9, 2016 at 12:04
  • Yes, the value is correct.
    – geom
    Mar 14, 2016 at 20:34
  • gid integer NOT NULL DEFAULT nextval ('test_gid_seq'::regclass), Jan 30, 2019 at 20:12

1 Answer 1

-1

Run this:

SELECT setval('test_gid_seq', (SELECT MAX(gid) FROM your_table)+1);
1
  • 3
    No. Dont do that. Bad answer. It fails if you have multiple accesses, (For instance two transactions trying to write at the same time). Postgres has a SERIAL type that can take care of this by generating a new series key when its asked for, which means its 'thread safe' so to speak. Its an old MYSQL technique and they stopped using that over there a long time ago for good reasons.
    – Shayne
    Jul 1, 2022 at 1:18

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.