MERGE examples not clear
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-merge.html
Description:
On this page: https://www.postgresql.org/docs/15/sql-merge.html
the first and second examples seems to be contrasted (by "this would be
exactly equivalent to the following statement"), however the difference does
not seem to related to the stated reason ("the MATCHED result does not
change"). It seems like the difference should involve the order of WHEN
clauses?
Of course, it might be that I don't understand the point, in which case
maybe the point could be stated more clearly?
On Tue, Feb 21, 2023 at 8:35 AM PG Doc comments form <noreply@postgresql.org>
wrote:
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-merge.html
Description:On this page: https://www.postgresql.org/docs/15/sql-merge.html
the first and second examples seems to be contrasted (by "this would be
exactly equivalent to the following statement"), however the difference
does
not seem to related to the stated reason ("the MATCHED result does not
change"). It seems like the difference should involve the order of WHEN
clauses?
Of course, it might be that I don't understand the point, in which case
maybe the point could be stated more clearly?
Yeah, that is a pretty poor pair of examples. Given that a given customer
can reasonably be assumed to have more than one recent transaction the
MERGE has a good chance of failing.
The only difference between the two is the second one uses an explicit
subquery as the source while the first simply names a table. If the
subquery had a GROUP BY customer_id that would be a good change explaining
that the second query is different because it is resilient in the face of
duplicate customer recent transactions.
While here...source_alias (...completely hides...the fact that a query was
issued). What? Probably it should read (not verified) that it is actually
required when the source is a query (maybe tweaking the syntax to match).
David J.
On Tue, Feb 21, 2023 at 08:56:50AM -0700, David G. Johnston wrote:
On Tue, Feb 21, 2023 at 8:35 AM PG Doc comments form <noreply@postgresql.org>
wrote:The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-merge.html
Description:On this page: https://www.postgresql.org/docs/15/sql-merge.html
the first and second examples seems to be contrasted (by "this would be
exactly equivalent to the following statement"), however the difference
does
not seem to related to the stated reason ("the MATCHED result does not
change"). It seems like the difference should involve the order of WHEN
clauses?
Of course, it might be that I don't understand the point, in which case
maybe the point could be stated more clearly?Yeah, that is a pretty poor pair of examples. Given that a given customer can
reasonably be assumed to have more than one recent transaction the MERGE has a
good chance of failing.The only difference between the two is the second one uses an explicit subquery
as the source while the first simply names a table. If the subquery had a
GROUP BY customer_id that would be a good change explaining that the second
query is different because it is resilient in the face of duplicate customer
recent transactions.While here...source_alias (...completely hides...the fact that a query was
issued). What? Probably it should read (not verified) that it is actually
required when the source is a query (maybe tweaking the syntax to match).
The attached patch removes the second example, which doesn't seem to add
much.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
merge.difftext/x-diff; charset=us-asciiDownload+0-17
On Wed, Sep 27, 2023 at 07:42:42PM -0400, Bruce Momjian wrote:
On Tue, Feb 21, 2023 at 08:56:50AM -0700, David G. Johnston wrote:
On Tue, Feb 21, 2023 at 8:35 AM PG Doc comments form <noreply@postgresql.org>
wrote:The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/sql-merge.html
Description:On this page: https://www.postgresql.org/docs/15/sql-merge.html
the first and second examples seems to be contrasted (by "this would be
exactly equivalent to the following statement"), however the difference
does
not seem to related to the stated reason ("the MATCHED result does not
change"). It seems like the difference should involve the order of WHEN
clauses?
Of course, it might be that I don't understand the point, in which case
maybe the point could be stated more clearly?Yeah, that is a pretty poor pair of examples. Given that a given customer can
reasonably be assumed to have more than one recent transaction the MERGE has a
good chance of failing.The only difference between the two is the second one uses an explicit subquery
as the source while the first simply names a table. If the subquery had a
GROUP BY customer_id that would be a good change explaining that the second
query is different because it is resilient in the face of duplicate customer
recent transactions.While here...source_alias (...completely hides...the fact that a query was
issued). What? Probably it should read (not verified) that it is actually
required when the source is a query (maybe tweaking the syntax to match).The attached patch removes the second example, which doesn't seem to add
much.
Patch from September 2023 applied.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
When a patient asks the doctor, "Am I going to die?", he means
"Am I going to die soon?"