From: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | MERGE output doubt |
Date: | 2022-10-20 13:58:16 |
Message-ID: | CAKoxK+42MmACUh6s8XzASQKizbzrtOGA6G1UjzCP75NcXHsiNw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2022-10-20 15:02:37 | Re: pg_restore 12 "permission denied for schema" errors |
Previous Message | Ron | 2022-10-20 13:20:09 | Re: pg_restore 12 "permission denied for schema" errors |