From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: clark.pearson@mail.com
Subject: BUG #18043: Merge fails to insert when source and target are same table and table is empty (contains no rows)
Date: Sun, 30 Jul 2023 16:17:50 +0000
Message-ID: <18043-d89bc5e22cb0f2e4@postgresql.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, clark.pearson@mail.com
Subject: Re: BUG #18043: Merge fails to insert when source and target are same table and table is empty (contains no rows)
In-Reply-To: <18043-d89bc5e22cb0f2e4@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox