PostgreSQL duplicate key value violates unique constraint while using on conflict do nothing on insert

Question

Table def:

CREATE SEQUENCE IF NOT EXISTS lazy_product_stock_id_seq;
CREATE TABLE "public"."lazy_product_stock" (
    "id" int4 NOT NULL DEFAULT nextval('lazy_product_stock_id_seq'::regclass),
    "product_id" int4,
    "hold" int4 DEFAULT 0,
    "quantity" int4 DEFAULT 0,
    "warehouse_id" int4,
    PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX lazy_product_stock_pkey ON public.lazy_product_stock USING btree (id)
CREATE INDEX lazy_product_stock_product_id_idx ON public.lazy_product_stock USING btree (product_id)
CREATE INDEX lazy_product_stock_warehouse_id_idx ON public.lazy_product_stock USING btree (warehouse_id)
CREATE UNIQUE INDEX CONCURRENTLY "lazy_product_stock_comb_idx2" ON "public"."lazy_product_stock" USING BTREE ("product_id","warehouse_id");

I have a function that inserts new rows into the database:

CREATE OR REPLACE FUNCTION sp_lazystock_i_f(_product_id int4, site_id int4) RETURNS VOID AS $$
declare
warehouse record;
BEGIN
FOR warehouse IN select id from warehouse where siteid = site_id LOOP
    insert into lazy_product_stock (product_id, warehouse_id) VALUES (_product_id, warehouse.id) ON CONFLICT (product_id,warehouse_id) DO NOTHING;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

It fails with

duplicate key value violates unique constraint "lazy_product_stock_comb_idx2"

the index

CREATE UNIQUE INDEX CONCURRENTLY "lazy_product_stock_comb_idx2" ON "public"."lazy_product_stock" USING BTREE ("product_id","warehouse_id");

Although running the relevant insert on its own with duplicate values it has no problem with it.

insert into lazy_product_stock (product_id, warehouse_id) VALUES (123, 1234) ON CONFLICT (product_id,warehouse_id) DO NOTHING;

Query 1 OK: INSERT 0 0, 0 rows affected

I can't understand what seems to be the the difference between the function and the single statement?

This also works without a problem:

do $$
declare
warehouse record;
begin
    FOR warehouse IN select id from warehouse where siteid = 123 LOOP
    insert into lazy_product_stock (product_id, warehouse_id) VALUES (12345, warehouse.id) ON CONFLICT (product_id,warehouse_id) DO NOTHING;
END LOOP;
end; $$ LANGUAGE plpgsql;

same error with

insert into lazy_product_stock (product_id, warehouse_id)
    select _product_id, warehouse.id
    from warehouse where siteid = site_id
    on conflict (product_id, warehouse_id) do nothing;

I am using Postgresql 12.3

Edit your question and show the definition of the table. I suspect you have other unique constraints on it. — Gordon Linoff
– Gordon Linoff, Commented Oct 7, 2020 at 16:15
Unrelated, but: the loop is completely unnecessary and will only make things a lot slower — user330315
– user330315, Commented Oct 15, 2020 at 20:06

Gordon Linoff · Accepted Answer · 2020-10-07 13:35:57Z

2

+250

Why use a loop for this? How about:

insert into lazy_product_stock (product_id, warehouse_id)
    select _product_id, w.id
    from warehouse where siteid = site_id
    on conflict (product_id, warehouse_id) do nothing;

answered Oct 7, 2020 at 13:35

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

display name Over a year ago

same error when using your insert duplicate key value violates unique constraint "lazy_product_stock_comb_idx2"

GMB · Accepted Answer · 2020-10-15 19:55:51Z

1

Does it work if you don't specify a target?

insert into lazy_product_stock (product_id, warehouse_id)
select _product_id, warehouse.id
from warehouse 
where siteid = site_id
on conflict do nothing;

do nothing is the one and only conflict action that does not require specifying a target, which makes this workaround possible here.

answered Oct 15, 2020 at 19:55

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

display name Over a year ago

no, it fails with the same constraint error error: duplicate key value violates unique constraint "lazy_product_stock_comb_idx2"

display name · Accepted Answer · 2020-10-17 11:36:22Z

0

Seems like I had 2 such functions trigerring on the same logic which caused the problem. Thank you all for help.

answered Oct 17, 2020 at 11:36

display name

6737 silver badges13 bronze badges

Collectives™ on Stack Overflow

PostgreSQL duplicate key value violates unique constraint while using on conflict do nothing on insert

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related