Thread: 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.
Attachment
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?"