Postgres INSERT ON CONFLICT DO NOTHING vs SELECT + INSERT query

Question

I have table stock_price_code as below. value column has a UNIQUE constraint.

create table stock_price_code (
    id serial primary key,
    value text not null,
    unique (value)
);

I want to INSERT into table, if no record found for value. I have these 2 queries:

-- query 1
INSERT INTO stock_price_code (value)
    SELECT 'MCD'
    WHERE NOT EXISTS (SELECT * FROM stock_price_code WHERE value = 'MCD')
    RETURNING id;


-- query 2
INSERT INTO stock_price_code (value) VALUES ('MCD')
    ON CONFLICT (value) DO NOTHING
    RETURNING id;

I was using query 1 before Postgres 9.5. Then Postgres 9.5 started introducing INSERT ... ON CONFLICT ... feature. If I substitude query 1 with query 2, is there any known side effect or perfomance issue? Thanks.

`insert on conflict` is safe for concurrent transactions while the other query is not. If there is a side effect, then that the new code will work more reliably - you don't need to check for a unique key violation any more — , Feb 22 '18 at 09:17
Noted on the concurrent transaction thing. However, after some experiment, I decided to stick to `query 2` for my use case. I explain why in the comment for answer below. Appreciate your help, thanks! — Shuwn Yuan Tee, Feb 23 '18 at 04:06

score 5 · Accepted Answer · answered Feb 22 '18 at 09:18

5

query 2 will work if a row gets inserted between SELECT 'MCD' WHERE NOT EXISTS (SELECT * FROM stock_price_code WHERE value = 'MCD') and INSERT INTO stock_price_code (value), while query 1 will fail with duplicate.

The overhead of ON CONFLICT I believe is smaller then WHERE NOT EXISTS, not sure though

answered Feb 22 '18 at 09:18

Vao Tsun

47,234
13
100
132

5

I just realize with `INSERT ... ON CONFLICT DO NOTHING`, there is 1 drawback where the auto increment `SERIAL` get incremented each time, no matter there is `INSERT` or not. Although there is no big deal with gaps in `SERIAL`, but this query is run very often & most of the time it ends up `DO NOTHING`. To avoid this, I think I will stick to `query 1` for my use case. Thanks for your explanation, appreciate that! – Shuwn Yuan Tee Feb 23 '18 at 04:02

Postgres INSERT ON CONFLICT DO NOTHING vs SELECT + INSERT query

1 Answers1