postgresql – Postgres INSERT ON CONFLICT DO UPDATE vs INSERT或UPDATE

概述我有stock_price_alert表,有3列. 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, fa 我有stock_price_alert表,有3列. 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));

我需要：

1)INSERT记录(如果不存在)

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

2)UPDATE记录(如果存在)

-- query 2UPDATE stock_price_alert SET    fall_below_alert = true,rise_above_alert = falseWHERE stock_price_ID = 1;

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

Postgres支持INSERT INTO table ….关于冲突更新…：

-- query 3INSERT INTO stock_price_alert (stock_price_ID,false)ON CONFliCT (stock_price_ID) DO UPDATE SET    fall_below_alert = EXCLUDED.fall_below_alert,rise_above_alert = EXCLUDED.rise_above_alert;

而不是使用query(1)或(2),我总是可以使用query(3)？然后我不需要在先前和之前发出SELECT查询.它有助于简化代码.

但我想知道,这是最好的做法？查询(3)会导致性能问题或不必要的副作用吗？谢谢.

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

从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”.

这是您尝试实现的最佳实践.

总结

以上是内存溢出为你收集整理的postgresql – Postgres INSERT ON CONFLICT DO UPDATE vs INSERT或UPDATE全部内容，希望文章能够帮你解决postgresql – Postgres INSERT ON CONFLICT DO UPDATE vs INSERT或UPDATE所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。