QGis postgis sequence is ignored

Question

I have a problem adding new features to a Postgis database. My table has a primary key defined as

gid integer NOT NULL DEFAULT nextval('bo_gid_seq'::regclass)

When I try to add a new polygon in QGis to this layer, i can draw it, but I'm unable to save it to the database. I allways get an error:

null value in column "gid" violates not-null constraint ...

Before saving, the form shows me the nextval('bo_gid_seq'::regclass), but finally it seems to be ignored by the database. The errors stays exactly the same, even when I enter a valid gid-number.

I used this feature with the same database before QGis 2.8, but with all the actual releases (2.14) I can't get it to work.

After all I wonder that there are so few threads about that problem in the net?

Answer 1

You need to check the value of the sequence.
QGis use the next sequence number but you have no guaranty that value isn't already used in the table.

If the sequence is being used for unique ids in a table, you can simply do this:

get the max value of the field:

select max(gid) from mytable;

Update your sequence:

SELECT setval('bo_gid_seq', the_max_value , true);

Answer 2

I had a very similar problem as you discribed in your question. Turned out that the value of the proposed sequence was way below the number of records in my table. I had to restart the sequence related to the column.

I could solve it with this line of code:

alter sequence [schema.table_column_seq] restart with 855;

See https://www.postgresql.org/docs/10/sql-altersequence.html for reference.