Thread: MERGE output doubt

Hi all,
I'm experimenting with the new MERGE command, but I'm not getting the
output total count. From the docs
<https://www.postgresql.org/docs/15/sql-merge.html>: "The total_count
is the total number of rows changed ".
This is my simple setup:

testdb=> table scores;
pk |   name   | score
----+----------+-------
 1 | luca     |    10
 2 | luca     |    20
 3 | luca     |    50
 4 | emanuela |    50
 5 | emanuela |   150
 6 | luca     |   122
(6 rows)

testdb=> truncate average_scores ;
TRUNCATE TABLE
testdb=> MERGE INTO average_scores a
USING ( SELECT name, avg( score ) AS avg_score
       FROM scores GROUP BY name ) s
ON s.name = a.name
WHEN MATCHED  THEN
   do nothing
WHEN NOT MATCHED THEN
   INSERT (name, avg_score) VALUES( s.name, s.avg_score );
MERGE 2

So far so good, two cumulative entries have been inserted into
average_scores. Now, if I use  a do nothing merge:

estdb=> MERGE INTO average_scores a
USING ( SELECT name, avg( score ) AS avg_score
       FROM scores GROUP BY name ) s
ON s.name = a.name
WHEN MATCHED  THEN
   do nothing
WHEN NOT MATCHED THEN
   do nothing;
MERGE 2

I was expecting an output tag like "MERGE 0" since both branches have
"do nothing", so no tuples should be updated at all on the target
table.
Moreover, if I truncate the target table and execute again the merge
query, I got the result of 0:

testdb=> truncate average_scores ;
TRUNCATE TABLE
testdb=> MERGE INTO average_scores a
USING ( SELECT name, avg( score ) AS avg_score
       FROM scores GROUP BY name ) s
ON s.name = a.name
WHEN MATCHED  THEN   -- caso di match
   do nothing
WHEN NOT MATCHED THEN
   do nothing;
MERGE 0

What am I missing here?

On 2022-Oct-20, Luca Ferrari wrote:

> Now, if I use  a do nothing merge:
> 
> estdb=> MERGE INTO average_scores a
> USING ( SELECT name, avg( score ) AS avg_score
>        FROM scores GROUP BY name ) s
> ON s.name = a.name
> WHEN MATCHED  THEN
>    do nothing
> WHEN NOT MATCHED THEN
>    do nothing;
> MERGE 2
> 
> I was expecting an output tag like "MERGE 0" since both branches have
> "do nothing", so no tuples should be updated at all on the target
> table.

Hmm, yeah, it should report 0 tuples, according to the documentation.
AFAICS this patch fixes it, will push shortly.

> Moreover, if I truncate the target table and execute again the merge
> query, I got the result of 0:
> 
> testdb=> truncate average_scores ;
> TRUNCATE TABLE
> testdb=> MERGE INTO average_scores a
> USING ( SELECT name, avg( score ) AS avg_score
>        FROM scores GROUP BY name ) s
> ON s.name = a.name
> WHEN MATCHED  THEN   -- caso di match
>    do nothing
> WHEN NOT MATCHED THEN
>    do nothing;
> MERGE 0
> 
> What am I missing here?

Hmm, is this not what you were expecting?  Or are you saying that it
should have been affected by the same bug?

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

On 2022-Nov-17, Alvaro Herrera wrote:

> On 2022-Oct-20, Luca Ferrari wrote:
> 
> > I was expecting an output tag like "MERGE 0" since both branches have
> > "do nothing", so no tuples should be updated at all on the target
> > table.
> 
> Hmm, yeah, it should report 0 tuples, according to the documentation.
> AFAICS this patch fixes it, will push shortly.

Pushed, thanks for reporting.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Someone said that it is at least an order of magnitude more work to do
production software than a prototype. I think he is wrong by at least
an order of magnitude."                              (Brian Kernighan)