Re: [HACKERS] SQL MERGE patches for PostgreSQL Versions

On Thu, Jun 22, 2017 at 7:52 PM, Jan de Visser <jan@de-visser.net> wrote:
> On Thursday, June 22, 2017 12:32:14 PM EDT Kang Yuzhe wrote:
>> Here is a sample what I did after applying the patch.
>>
>> testdb=# BEGIN;
>> BEGIN
>> testdb=#
>> testdb=# MERGE INTO Stock USING Buy ON Stock.item_id = Buy.item_id
>> testdb-#  WHEN MATCHED THEN UPDATE SET balance = balance + Buy.volume
>> testdb-#  WHEN NOT MATCHED THEN DO NOTHING;
>> MERGE 1
>> testdb=# SELECT * FROM Stock;
>>  item_id | balance
>> ---------+---------
>>       20 |    1900
>>       10 |    3200
>> (2 rows)
>>
>> testdb=# ROLLBACK;
>> ROLLBACK
>
> I am not quite sure what you're trying to achieve, but are you aware that
> pgsql 9.6 introduced the ON CONFLICT clause, which allows you to do the same
> with a different syntax?
>
> https://www.postgresql.org/docs/9.6/static/sql-insert.html
>
> Look for ON CONFLICT.

Yes, I am aware of ON CONFLICT.
DO NOTHING in SQL Merge is one type of scenario which is like ON CONFLICT.
My goal is to understand how SQL MERGE works which is in SQL ANSI/ISO
standard. And I would implement as a patch in the latest PG if I could
do that.

Regards,
Zeray.