Thread: 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:
ERROR MESSAGES! ALWAYS SHOW ERROR MESSAGES!!!
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.
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. https://postgr.es/m/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. > > https://postgr.es/m/202111152245.byerxxac444d@alvherre.pgsql > 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. > > > > https://postgr.es/m/202111152245.byerxxac444d@alvherre.pgsql > > 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. >>> >>> https://postgr.es/m/202111152245.byerxxac444d@alvherre.pgsql >> >> 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