CODEWITHSUNDEEP
postgresqlpostgresql-10
LondonRob
LondonRob

Reputation: 78723

INSERT INTO excluding ID column violates primary key uniqueness constraint

I have a Postgres 10.6 table with a serial ID column.

When I attempt to insert into it:

INSERT INTO table (col1, col2) VALUES ('foo', 'bar');

excluding the ID column from the column list, I get:

ERROR: duplicate key value violates unique constraint "customer_invoice_pkey"
  Detail: Key (id)=(1234) already exists.

Subsequent runs of the query increment the ID in the error message (1235, 1236 etc.)

How can this be happening?

Upvotes: 1

Views: 1126

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246318

Having a serial column does not prevent you from inserting rows with an explicit value for id. The sequence value is only a default value that is used when id is not specified in the INSERT statement.

So there must have been some “rogue” inserts of that kind. From PostgreSQL v11 on, you can use identity columns (GENERATED ALWAYS AS IDENTITY) to make overriding the sequence value harder.

You could use the setval function to set the sequence to a value higher than the maximum id in the table to work around the problem.

Upvotes: 1

Related Questions