1

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.

Dave_B.
  • 173
  • 10
  • "*After single INSERTs it works well*" - I doubt that. Yes, if you overwrite the default and ignore the sequence, you always have to correct it afterwards. – Bergi Jun 22 '21 at 13:33
  • You are right, I tried it just in the moment again, and there is the same error like on bulk insert. Ok, at least consistent behavior. Thank you. – Dave_B. Jun 22 '21 at 14:11
  • Maybe my problem is coming from mysql. There I have a (really working) default sequence (without any manually doing), and full fexibility too. I can change a id and next time will nevertheless be inserted a correct new id. – Dave_B. Jun 22 '21 at 14:21

1 Answers1

3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    …and for doing it on purpose (such as a bulk import followed by correction of the sequence counter), you can still use `OVERRIDING SYSTEM VALUE`. – Bergi Jun 22 '21 at 14:09