postgresql Postgres INSERT ON CONFLICT DO UPDATE vs INSERT or UPDATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48922972/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres INSERT ON CONFLICT DO UPDATE vs INSERT or UPDATE
提问by Shuwn Yuan Tee
I have stock_price_alerttable with 3 columns. stock_price_idis PRIMARY KEY& also FOREIGN KEYto other table. Table definition as below:
我有stock_price_alert3 列的表。stock_price_id是PRIMARY KEY& 也FOREIGN KEY到其他表。表定义如下：
create table stock_price_alert (
stock_price_id integer references stock_price (id) on delete cascade not null,
fall_below_alert boolean not null,
rise_above_alert boolean not null,
primary key (stock_price_id)
);
I need to either:
我需要：
1) INSERTrecord if not exist
1)INSERT如果不存在则记录
-- query 1
INSERT INTO stock_price_alert (stock_price_id, fall_below_alert, rise_above_alert)
VALUES (1, true, false);
2) UPDATErecord if exist
2）UPDATE记录是否存在
-- query 2
UPDATE stock_price_alert SET
fall_below_alert = true,
rise_above_alert = false
WHERE stock_price_id = 1;
First I need to issue SELECTquery on stock_price_alerttable, in order to decide whether to perform query (1) or (2).
首先，我需要SELECT对stock_price_alert表发出查询，以决定是执行查询 (1) 还是 (2)。
Postgres supports INSERT INTO TABLE .... ON CONFLICT DO UPDATE ...:
Postgres 支持INSERT INTO TABLE .... ON CONFLICT DO UPDATE ...：
-- query 3
INSERT INTO stock_price_alert (stock_price_id, fall_below_alert, rise_above_alert)
VALUES (1, true, false)
ON CONFLICT (stock_price_id) DO UPDATE SET
fall_below_alert = EXCLUDED.fall_below_alert,
rise_above_alert = EXCLUDED.rise_above_alert;
Instead of using query (1) or (2), can I always use query (3)? Then I don't need to issue SELECTquery in prior & it helps to simplify the code.
除了使用查询 (1) 或 (2)，我可以一直使用查询 (3) 吗？然后我不需要SELECT在之前发出查询&它有助于简化代码。
But I am wondering, which is the best practice? Will query (3) cause performance issue or unwanted side effect? Thanks.
但我想知道，哪个是最佳实践？查询 (3) 是否会导致性能问题或不需要的副作用？谢谢。
采纳答案by Yoni Rabinovitch
Query 3 is the Postgres syntax for "UPSERT" (= UPDATE or INSERT), introduced in Postgres 9.5.
查询 3 是 Postgres 9.5 中引入的“UPSERT”（= UPDATE 或 INSERT）的 Postgres 语法。
From the documentation:
从文档：
ON CONFLICT DO UPDATEguarantees an atomicINSERTorUPDATEoutcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known asUPSERT– “UPDATEorINSERT”.
ON CONFLICT DO UPDATE保证原子INSERT或UPDATE结果；只要没有独立错误，即使在高并发下，也能保证这两种结果之一。这也称为UPSERT– “UPDATE或INSERT”。
This is the best practice for what you are trying to achieve.
这是您要实现的目标的最佳实践。
回答by Jeremy Giaco
I noticed/tested that is much faster for INSERTS (have yet to test UPSERTS) to use a WHERE NOT EXISTS in addition to ON CONFLICT. Typically about 3x faster than just allowing the ON CONFLICT to handle existence checks. I think this may carry over into UPSERTS, making it likely faster to do an INSERT and then and UPDATE. Here is my test for inserts only...
我注意到/测试过，除了 ON CONFLICT 之外，INSERTS（尚未测试 UPSERTS）使用 WHERE NOT EXISTS 的速度要快得多。通常比仅允许 ON CONFLICT 处理存在检查快 3 倍。我认为这可能会延续到 UPSERTS，使得执行 INSERT 和 UPDATE 的速度可能更快。这是我仅对插入的测试...
--so i can keep rerunning
DROP TABLE if exists temp1;
DROP TABLE if exists temp2;
--create a billion rows
SELECT GENERATE_SERIES AS id INTO TEMP temp1
FROM GENERATE_SERIES(1, 10000000);
CREATE UNIQUE INDEX ux_id ON temp1(id);
ALTER TABLE temp1 CLUSTER ON ux_id;
--create a second table to insert from, with the same data
SELECT * INTO TEMP temp2
FROM temp1;
CREATE UNIQUE INDEX ux_id2 ON temp2(id);
ALTER TABLE temp2 CLUSTER ON ux_id2;
--test inserting with on conflict only
INSERT INTO temp1(id)
SELECT id
FROM temp2 ON conflict DO nothing;
--execution time: 14.71s (1million rows)
--test inserting with not exists and on conflict
INSERT INTO temp1(id)
SELECT t2.id
FROM temp2 t2
WHERE NOT EXISTS (SELECT 1 FROM temp1 t1 WHERE t2.id = t1.id)
ON conflict DO nothing;
--execution time: 5.78s (1million rows)
