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 到其他表。表定义如下：

 创建表stock_price_alert（
 stock_price_id整数引用删除级联上的stock_price（id）不为null， 
 fall_below_alert布尔值不为null，
rise_above_alert布尔值不为null，
主键（stock_price_id）
）; 
  

我需要：

1 ）插入记录（如果不存在）

 -查询1 
将INSERT INTO stock_price_alert（stock_price_id，fall_below_alert，rise_above_alert）
值（1，true，false）; 
  

2） UPDATE 记录是否存在

 -查询2 
更新stock_price_alert SET 
 fall_below_alert = true，
rise_above_alert = false 
 WHERE stock_price_id = 1; 
  

首先，我需要在 SELECT 上进行查询 stock_price_alert 表，以便决定执行查询（1）还是（2）。

Postgres支持插入表....在冲突时进行更新... ：

 -查询3 
插入到stock_price_alert（stock_price_id，fall_below_alert，rise_above_alert）
值（1，true，false）
冲突（stock_price_id）进行更新设置
 fall_below_alert = EXCLUDED .fall_below_alert; 
rise_above_alert = EXCLUDED.rise_above_alert; 
  

我可以始终使用查询（3）代替查询（1）或（2）吗？然后，我不需要在之前的&中执行 SELECT 查询它有助于简化代码。

但是我想知道，这是最佳实践吗？查询（3）会导致性能问题或不良副作用吗？谢谢。

解决方案

查询3是Postgres 9.5中引入的 UPSERT（= UPDATE或INSERT）的Postgres语法。 / p>

来自文档：

在冲突中进行更新保证原子 INSERT 或 UPDATE 结果；
只要没有独立错误，即使在高并发情况下，这两个结果之一也可以保证
。这也称为 UPSERT –
<quo; UPDATE 或 INSERT ”。

这是您要实现的最佳实践。

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

I need to either:

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 record if exist

-- 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 SELECT query on stock_price_alert table, in order to decide whether to perform query (1) or (2).

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;

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.

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

解决方案

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

From the documentation:

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屋！