Each SERIAL field in the database table is associated with sequence which tracks generation of new values. Sequence is basically a counter that calculates the next value of the SERIAL field.

If values in the table are inserted by omitting the SERIAL filed, PostgreSQL will automatically insert appropriate value in the field and increment sequence value. In the example below table Events has event_id as serial primary key:

insert into public.events (event_type, name, description, action)
    values (0, 'De-energize line', 'Opis 20', 'Bla bla bla');

It is not necessary to have field event_id specified in Insert statement; field will be correctly populated with next sequence value and sequence will be incremented. However, if the event_id ​field is explicitly specified, sequence will not be changed no matter what value is inserted – this is obviously by design.

This can lead to a problems. Consider thethe situation where next sequence value is 3 and the statement below is issued:

insert into public.events (event_id, event_type, name, description, action)
    values (3, 0, 'De-energize line', 'Opis 20', 'Bla bla bla');

Sequence will remain on the value 3 and thus be out of sync with the table SERIAL field. Next insert without event_id specified will result in “Duplicate key error”, becuase PostgreSQL will try to use next value found in sequence.

This error can be remediated by issuing statement below:

SELECT setval('events_event_id_seq', (SELECT MAX(event_id) FROM events)+1);

Sequence names and values can be found in pgAdmin, under “Sequences” key in schema tree.

It is recommended not to mix automatic generation of SERIAL values and explicit setting of SERIAL field. One exception to this rule is bulk imports, which must be followed by syncing sequences.