CODEWITHSUNDEEP
postgresqlupsert
Michael Böckling
Michael Böckling

Reputation: 7872

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

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?

Upvotes: 0

Views: 622

Answers (1)

JGH
JGH

Reputation: 17846

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;

Upvotes: 4

Related Questions