On Mon, Oct 18, 2010 at 10:09 AM, Boxuan Zhai <bxzhai2...@gmail.com> wrote: > > > On Mon, Oct 18, 2010 at 9:54 PM, Robert Haas <robertmh...@gmail.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 <g...@2ndquadrant.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
