CODEWITHSUNDEEP
postgresqlsql-merge
mp75
mp75

Reputation: 21

Merge Into Statement Erroring out at "Merge"

I get the following error when trying to run a SQL "Merge Into" statement in Postgres:

"ERROR: syntax error at or near "MERGE".

We are on Postgres 12.

Here is the SQL you can use to test.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);
;

Upvotes: 0

Views: 846

Answers (1)

user330315
user330315

Reputation:

As documented in the manual Postgres has no merge statement.

You need to use INSERT ON CONFLICT instead

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;

Upvotes: 2

Related Questions