Обсуждение: Merge into does not work

Поиск
Список
Период
Сортировка

Merge into does not work

От
Shaozhong SHI
Дата:
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

Re: Merge into does not work

От
"David G. Johnston"
Дата:
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.

Re: Merge into does not work

От
Ron
Дата:
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.

Re: Merge into does not work

От
Alvaro Herrera
Дата:
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)

Re: Merge into does not work

От
Adrian Klaver
Дата:
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

Re: Merge into does not work

От
Alvaro Herrera
Дата:
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)

Re: Merge into does not work

От
Adrian Klaver
Дата:
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