Merge into does not work
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);
MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
I am using Postgres 9.6.
Regards,
David
As evidenced by the lack of such a command in our documentation this
doesn't exist. We do offer similar functionality directly as part of the
INSERT command via its ON CONFLICT clause.
David J.
On 11/26/21 1:08 PM, Shaozhong SHI wrote:
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);
MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
I am using Postgres 9.6.
*ERROR MESSAGES! ALWAYS SHOW ERROR MESSAGES!!!
*
--
Angular momentum makes the world go 'round.
On 2021-Nov-26, Shaozhong SHI wrote:
MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
It does work for me:
55479 15devel 680346=# MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
MERGE 2
Duración: 3,879 ms
55479 15devel 680346=# select * from stock;
item_id │ balance
─────────┼─────────
20 │ 1900
10 │ 3200
30 │ 300
(3 filas)
I am using Postgres 9.6.
I am using the MERGE patch I posted here[1], on top of Postgres 15.
/messages/by-id/202111152245.byerxxac444d@alvherre.pgsql
--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)
On 11/26/21 11:44, Alvaro Herrera wrote:
On 2021-Nov-26, Shaozhong SHI wrote:
I am using the MERGE patch I posted here[1], on top of Postgres 15.
A patch that as AFAIK is not even committed to what is at best an alpha
version would in my opinion not qualify as ready much less supported. I
look forward to seeing it make it in, but I would hardly recommend it
for general use.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2021-Nov-26, Adrian Klaver wrote:
On 11/26/21 11:44, Alvaro Herrera wrote:
On 2021-Nov-26, Shaozhong SHI wrote:
I am using the MERGE patch I posted here[1], on top of Postgres 15.
A patch that as AFAIK is not even committed to what is at best an alpha
version would in my opinion not qualify as ready much less supported. I look
forward to seeing it make it in, but I would hardly recommend it for general
use.
All true. I'm recruiting testers.
--
Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/
"I love the Postgres community. It's all about doing things _properly_. :-)"
(David Garamond)
On 11/26/21 12:10, Alvaro Herrera wrote:
On 2021-Nov-26, Adrian Klaver wrote:
On 11/26/21 11:44, Alvaro Herrera wrote:
On 2021-Nov-26, Shaozhong SHI wrote:
I am using the MERGE patch I posted here[1], on top of Postgres 15.
A patch that as AFAIK is not even committed to what is at best an alpha
version would in my opinion not qualify as ready much less supported. I look
forward to seeing it make it in, but I would hardly recommend it for general
use.All true. I'm recruiting testers.
Which is great, that was not how it was presented though:
'It does work for me ...'
The issue being there are already folks that come across:
https://wiki.postgresql.org/wiki/SQL_MERGE
https://wiki.postgresql.org/wiki/SQL_MERGE_Patch_Status
and do a partial read on the pages and leap to the conclusion the MERGE
exists in Postgres.
I fully expect people to see:
"
It does work for me:
55479 15devel 680346=# MERGE INTO Stock USING Buy ON Stock.item_id =
Buy.item_id
WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.volume);
MERGE 2
Duración: 3,879 ms
55479 15devel 680346=# select * from stock;
item_id │ balance
─────────┼─────────
20 │ 1900
10 │ 3200
30 │ 300
(3 filas)
"
quit reading and then ask why MERGE does not exist in their instance?
My two cents worth.
--
Adrian Klaver
adrian.klaver@aklaver.com