Postgresql: why no automatic updates of a id sequence after bulk insert

Question

Assuming, I have this table:

CREATE TABLE IF NOT EXISTS public.test
(
    "Id" smallint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "Value" character varying(10)
);

and I insert some rows:

INSERT INTO public.test ("Id", "Value") VALUES 
(1, 'Val1'),
(2, 'Val2'),
(3, 'Val3');

Everything is fine. But now I want to insert another row

INSERT INTO public.test ("Value") VALUES ('Val9');

and I get the error: duplicate key violates unique constraint

That is, how I learned, because the pk-sequence is out of sync. But why? Is there any senseful reason why pg do not update the sequence automatically? After single INSERTs it works well, but after BULK not? Is there any way to avoid these manually updates? Or is it standard to correct every pk-sequence of every table after every little bulk insert (if there is a serial id)?

For futher information, I use GENERATED BY DEFAULT because I want to migrate a database from mysql and I want to preserve the IDs. And I would like the idea of having a lot of flexibility with the keys (similar to mysql).

But what do I not understand here?

Is it possible to correct the sequence automatically without knowing it's concrete name?

Sorry, more questions than I wanted to ask. But ... I don't understand this concept. Would appreciate some explanation.

Answer 1

The problem is the BY DEFAULT in GENERATED BY DEFAULT AS IDENTITY. That means that you can override the automatically generated keys, like your INSERT statements do.

When that happens, the sequence that implements the identity column is not modified. So when you insert a row without specifying "Id", it will start counting at 1, which causes the conflict.

Never override the default. To make sure this doesn't happen by accident, use GENERATED ALWAYS AS IDENTITY.