The following bug has been logged on the website:
Bug reference: 18043
Logged by: Clark Pearson
Email address: clark.pearson@mail.com
PostgreSQL version: 15.2
Operating system: Windows
Description:
I have checked the release notes for 5.3, FAQ & TO DO, and can find no
mention of this as a known bug, or as work in progress. I am currently on
15.2.
This test case demonstrates failure to insert a row into an empty table when
source/target are the same table (I have not explored whether it applies in
a wider context, only when source/target are the same table):
postgres=> create table t (id integer, val integer);
CREATE TABLE
-- Table is empty, merge id=1 row :: does not insert
postgres=> merge into t n using t o on( n.id = 1 ) when matched then update
set val = o.val+1 when not matched then insert(id,val) values(1,1);
MERGE 0
-- Table is still empty, change join condition alias to 'o' (old) rather
than 'n' (new) :: still does not insert
postgres=> merge into t n using t o on( o.id = 1 ) when matched then update
set val = o.val+1 when not matched then insert(id,val) values(1,1);
MERGE 0
-- Insert a row, id=1
postgres=> insert into t values(1,1);
INSERT 0 1
-- Rerun original merge statement, id=1 row is found and merge-updated
(t.val column is set up by 1 to 2)
postgres=> merge into t n using t o on( n.id = 1 ) when matched then update
set val = o.val+1 when not matched then insert(id,val) values(1,1);
MERGE 1
-- Now run merge on row id=2 (new row), merge correctly inserts a row
postgres=> merge into t n using t o on( n.id = 2 ) when matched then update
set val = o.val+1 when not matched then insert(id,val) values(2,1);
MERGE 1