How can a Postgres UPSERT fail due to a duplicate SERIAL PRIMARY KEY value?

Question

Given the table definition:

CREATE TABLE devices
(
  id            SERIAL PRIMARY KEY,
  device_id     TEXT   NOT NULL,
  device_name   TEXT   NOT NULL
);

CREATE UNIQUE INDEX devices_by_device_id_unique ON devices (device_id);

And this upsert:

INSERT INTO devices (device_id, device_name)
VALUES (:device_id, :device_name)
ON CONFLICT (device_id) DO UPDATE
SET device_name = :device_name

How is it possible to get the following error:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "devices_pkey"
  Detail: Key (id)=(253) already exists.

Since the id column is auto-generated, it shouldn't be possible to attempt to insert the same value twice?

JGH · Accepted Answer · 2020-07-15 13:37:28Z

4

If you specify the ID value, the sequence won't be updated automatically:

insert into devices (id, device_id,device_name) values (1,1,'one');
INSERT 0 1
insert into devices (device_id,device_name) values (2,'two');
ERROR:  duplicate key value violates unique constraint "devices_pkey"
DETAIL:  Key (id)=(1) already exists.

You can reset the sequence to the current max value

SELECT setval(
          pg_get_serial_sequence('public.devices','id'), 
          max(id)) 
FROM public.devices;

answered Jul 15, 2020 at 13:37

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Böckling Over a year ago

> "If you specify the ID value" But the ID is never specified.

Michael Böckling Over a year ago

But maybe someone did a manual insert, for testing purposes 🤔

Michael Böckling Over a year ago

Concluded that thats what probably happened, thanks!

Collectives™ on Stack Overflow

How can a Postgres UPSERT fail due to a duplicate SERIAL PRIMARY KEY value?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related