upsert multiple entries, "duplicate key value violates unique constraint"

[tconroy]

I'm running into the above issue when trying to do a bulk-update (upsert) of multiple existing rows.

I have the following table and data:

-- Table Definition ----------------------------------------------

CREATE TABLE board_cards (
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    owner_id uuid NOT NULL DEFAULT auth.uid() REFERENCES users(id),
    text text,
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    list_id uuid NOT NULL REFERENCES board_lists(id),
    preceded_by uuid REFERENCES board_cards(id) UNIQUE
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX board_cards_pkey ON board_cards(id uuid_ops);
CREATE UNIQUE INDEX board_cards_preceded_by_key ON board_cards(preceded_by uuid_ops);

-- Data -------------------------------------------------------
-- note: contrived example for demo purposes.
INSERT INTO board_cards (owner_id, text, id, list_id, preceded_by) VALUES
('...', 'A1', 'a1-id', 'some-list-id', null),
('...', 'A2', 'a2-id', 'some-list-id', 'a1-id'),
('...', 'A3', 'a3-id', 'some-list-id', 'a2-id'),

The above represents a "card" in a singly-linked list (think something like Trello lists where cards are sequentially ordered). the preceded_by field references the board_card.id that precedes the current card.

I am calling this in supabase:

// the user has swapped the position of "A1" and "A2"
await supabase.from('board_cards').upsert([
  { id: 'a2-id', preceded_by: null  },
  { id: 'a1-id',  preceded_by: 'a2-id' },
  { id: 'a3-id',  preceded_by: 'a1-id' },
], {
  onConflict: 'preceded_by',
})

However this produces the following error message:

{"hint":null,"message":"duplicate key value violates unique constraint \"board_cards_pkey\"","code":"23505","details":null}

I've tried setting:

{ onConflict: 'id,preceded_by'  }

However that doesn't work either:

{"hint":null,"message":"there is no unique or exclusion constraint matching the ON CONFLICT specification","code":"42P10","details":null}

any input would be much appreciated! I'm sure it's something simple I am missing.

[steve-chavez]

Are you trying to update a unique key(the primary key) based on a conflict on another unique key? 🤯

I don't think you can satisfy both constraints at the same time on a single ON CONFLICT statement. That would also fail with pure SQL:

CREATE TABLE board_cards (
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    txt text,
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    preceded_by uuid REFERENCES board_cards(id) UNIQUE
);

INSERT INTO board_cards (txt, id, preceded_by) VALUES
('A1', '862dcfa0-0c07-44c6-b8d0-8f2b5c4365e7', null),
('A2', 'bf3f21a0-71ac-4f94-988f-f63a354979f7', '862dcfa0-0c07-44c6-b8d0-8f2b5c4365e7'),
('A3', '6ed862aa-076e-44ae-8db1-67a3386efe8d', 'bf3f21a0-71ac-4f94-988f-f63a354979f7');

-- this is essentially what happens when doing "upsert" on supabase-js
INSERT INTO board_cards (id, preceded_by) VALUES
('bf3f21a0-71ac-4f94-988f-f63a354979f7', null),
('862dcfa0-0c07-44c6-b8d0-8f2b5c4365e7', 'bf3f21a0-71ac-4f94-988f-f63a354979f7'),
('6ed862aa-076e-44ae-8db1-67a3386efe8d', '862dcfa0-0c07-44c6-b8d0-8f2b5c4365e7')
ON CONFLICT(preceded_by) DO UPDATE SET id = EXCLUDED.id;

ERROR:  23505: duplicate key value violates unique constraint "board_cards_pkey"
DETAIL:  Key (id)=(bf3f21a0-71ac-4f94-988f-f63a354979f7) already exists.
SCHEMA NAME:  test
TABLE NAME:  board_cards
CONSTRAINT NAME:  board_cards_pkey

Sorry, I must say I'm confused by the database model. At this point I'd suggest doing the custom logic on a function + rpc, or perhaps rework the model if possible, so you can do the ON CONFLICT on the primary key.

[JustGabo]

I think i'm kinda late, but imma try. I had the same problem, and the only way that worked for me to fix the problem, was to remove the foreing key relation and setting the id-colunm as a not primary key, hope this helps other people with the same problem.

pd: If you're gonna insert more than just one row with the same id, you're gonna have this problem, use the primary key just if you're gonna insert one row using the foreing key relation or the normal id, otherwise, you're gonna receive the message in the comments above.