postgresql - 合并到“合并”时出错的语句

Question

尝试在 Postgres 中运行 SQL“Merge Into”语句时出现以下错误：

“错误：“合并”处或附近的语法错误。

我们在 Postgres 12 上。

这是您可以用来测试的 SQL。SET AUTOCOMMIT = ON;

/* Drop tables when done testing... */
-- DROP TABLE Stock;
-- DROP TABLE Buy;
-- DROP TABLE Sale;

/* Build Tables */

CREATE TABLE STOCK(ITEM_ID INT UNIQUE, BALANCE INT);
INSERT INTO STOCK VALUES (10, 2200);
INSERT INTO STOCK VALUES (20, 1900);


CREATE TABLE BUY(ITEM_ID INT, VOLUME INT);
INSERT INTO BUY VALUES(10, 1000);
INSERT INTO BUY VALUES(30, 300);


CREATE TABLE SALE(ITEM_ID INT, VOLUME INT);
INSERT INTO SALE VALUES (10, 2200);
INSERT INTO SALE VALUES (20, 1000);

/* Test out Merge Statement */

MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
WHEN MATCHED THEN UPDATE SET Stock.balance = Stock.balance + Buy.volume
WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
;

标签： postgresqlsql-merge

Answer 1

如手册中所述， Postgres 没有合并语句。

您需要INSERT ON CONFLICT改用

insert into stock (item_id, balance)
select item_id, volume
from Buy 
on conflict (item_id) do
  update SET stock.balance = stock.balance + excluded.volume;