Postgres INSERT ON CONFLICT DO UPDATE 与 INSERT 或 UPDATE [英] Postgres INSERT ON CONFLICT DO UPDATE vs INSERT or UPDATE

问题描述

我有 3 列的 stock_price_alert 表.stock_price_id 是 PRIMARY KEY &也 FOREIGN KEY 到其他表.表定义如下:

I have stock_price_alert table with 3 columns. stock_price_id is PRIMARY KEY & also FOREIGN KEY to other table. Table definition as below:

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

我需要:

1) INSERT 如果不存在则记录

1) INSERT record if not exist

-- query 1
INSERT INTO stock_price_alert (stock_price_id, fall_below_alert, rise_above_alert)
VALUES (1, true, false);

2) UPDATE 记录是否存在

-- query 2
UPDATE stock_price_alert SET
    fall_below_alert = true,
    rise_above_alert = false
WHERE stock_price_id = 1;

首先我需要对 stock_price_alert 表发出 SELECT 查询，以决定是执行查询 (1) 还是 (2).

First I need to issue SELECT query on stock_price_alert table, in order to decide whether to perform query (1) or (2).

Postgres 支持 INSERT INTO TABLE .... ON CONFLICT DO UPDATE ...:

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

不使用查询 (1) 或 (2)，我是否可以始终使用查询 (3)?然后我不需要在之前发出 SELECT 查询 &它有助于简化代码.

Instead of using query (1) or (2), can I always use query (3)? Then I don't need to issue SELECT query in prior & it helps to simplify the code.

但我想知道，最佳做法是什么?查询 (3) 会导致性能问题或不必要的副作用吗?谢谢.

But I am wondering, which is the best practice? Will query (3) cause performance issue or unwanted side effect? Thanks.

推荐答案

查询 3 是 Postgres 9.5 中引入的UPSERT"(= UPDATE 或 INSERT)的 Postgres 语法.

Query 3 is the Postgres syntax for "UPSERT" (= UPDATE or INSERT), introduced in Postgres 9.5.

来自文档:

ON CONFLICT DO UPDATE 保证原子的 INSERT 或 UPDATE 结果；如果没有独立误差，这两个结果之一是保证，即使在高并发下.这也称为 UPSERT –“UPDATE 或 INSERT”.

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT – “UPDATE or INSERT”.

这是您要达到的目标的最佳实践.

This is the best practice for what you are trying to achieve.

这篇关于Postgres INSERT ON CONFLICT DO UPDATE 与 INSERT 或 UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！