Postgres的INSERT ON CONFLICT DO NOTHING与SELECT + INSERT查询

Question

我有一个如下所示的表 stock_price_code。其中，value 列具有 UNIQUE 约束。

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

如果value没有匹配的记录，我想要在表格中进行INSERT操作。 我有以下两个查询语句：

-- 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;

在Postgres 9.5之前，我使用的是查询1。然后Postgres 9.5开始引入了INSERT ... ON CONFLICT ...功能。如果我用查询2替换查询1，是否存在已知的副作用或性能问题？谢谢。

- Shuwn Yuan Tee

---

“插入冲突”在并发事务中是安全的，而另一个查询则不是。如果有副作用，那么新代码将更可靠地工作 - 您不再需要检查唯一键冲突。 - user330315

关于并发事务的问题已经注意到了。然而，在进行了一些实验后，我决定在我的用例中坚持使用“查询2”。我在下面的评论中解释了原因。感谢您的帮助，谢谢！ - Shuwn Yuan Tee

Answer 1

查询 2 只要在 SELECT 'MCD' WHERE NOT EXISTS (SELECT * FROM stock_price_code WHERE value = 'MCD') 和 INSERT INTO stock_price_code (value) 之间插入一行，就能够正常工作。而 查询 1 则会因为重复而失败。

我认为 ON CONFLICT 的开销比 WHERE NOT EXISTS 小，但不确定。

- Vao Tsun

---

我刚意识到使用INSERT ... ON CONFLICT DO NOTHING有一个缺点，即自增的SERIAL每次都会递增，无论有没有INSERT。虽然在SERIAL中存在间隙并不是什么大问题，但这个查询经常执行，大多数情况下都会执行DO NOTHING。为了避免这种情况，我认为在我的用例中应该坚持使用“查询1”。感谢您的解释，非常感激！ - Shuwn Yuan Tee