From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Boxuan Zhai <bxzhai2010(at)gmail(dot)com> |
Cc: | Greg Smith <greg(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: ask for review of MERGE |
Date: | 2010-10-18 14:17:37 |
Message-ID: | AANLkTi=psFqBqhi0=Hyk8gxgAA7A__=D6nc-qvJc0bDs@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Oct 18, 2010 at 10:09 AM, Boxuan Zhai <bxzhai2010(at)gmail(dot)com> wrote:
>
>
> On Mon, Oct 18, 2010 at 9:54 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> I think that MERGE is supposed to trigger one rule for each row in the
>> source data. So:
>>
>> On Sun, Oct 17, 2010 at 8:20 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
>> > MERGE INTO Stock t
>> > USING (SELECT * FROM Stock WHERE item_id=10) AS s
>> > ON s.item_id=t.item_id
>> > WHEN MATCHED THEN UPDATE SET balance=s.balance + 1
>> > WHEN NOT MATCHED THEN INSERT VALUES (10,1)
>> > ;
>> >
>> > This works fine, and updates the matching row:
>> >
>> > item_id | balance
>> > ---------+---------
>> > 20 | 1900
>> > 10 | 2201
>>
>> Here you have one row of source data, and you got one action (the WHEN
>> MATCHED case).
>>
>> > But if I give it a key that doesn't exist instead:
>> >
>> > MERGE INTO Stock t
>> > USING (SELECT * FROM Stock WHERE item_id=30) AS s
>> > ON s.item_id=t.item_id
>> > WHEN MATCHED THEN UPDATE SET balance=s.balance + 1
>> > WHEN NOT MATCHED THEN INSERT VALUES (30,1)
>> > ;
>> >
>> > This doesn't execute the NOT MATCHED case and INSERT the way I expected
>> > it
>> > to. It just gives back "MERGE 0".
>>
>> Here you have no rows of source data (the USING (SELECT ...) doesn't
>> return anything, since no rows exist) so nothing happens.
>>
>
> Yes.
> The MERGE process is based on a left join between the source table and
> target table.
> Since here the source table is empty, no join is carried, and thus no MERGE
> action is taken.
> But, is it correct logically? I mean, should we insert some rows in the
> above example rather than do nothing?
I don't think so. I think the right way to write UPSERT is something
along the lines of:
MERGE INTO Stock t USING (VALUES (10, 1)) s(item_id, balance) ON
s.item_id = t.item_id ...
(untested)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2010-10-18 14:18:25 | Re: How to determine failed connection attempt due to invalid authorization (libpq)? |
Previous Message | Robert Haas | 2010-10-18 14:14:33 | Re: security hook on table creation |