agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedMERGE examples not clear
4+ messages / 3 participants
[nested] [flat]
* MERGE examples not clear
@ 2023-02-21 15:14 PG Doc comments form <noreply@postgresql.org>
2023-02-21 15:56 ` Re: MERGE examples not clear David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Doc comments form @ 2023-02-21 15:14 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: dwayne.towell@gmail.com
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MERGE examples not clear
2023-02-21 15:14 MERGE examples not clear PG Doc comments form <noreply@postgresql.org>
@ 2023-02-21 15:56 ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-27 23:42 ` Re: MERGE examples not clear Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2023-02-21 15:56 UTC (permalink / raw)
To: dwayne.towell@gmail.com; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MERGE examples not clear
2023-02-21 15:14 MERGE examples not clear PG Doc comments form <noreply@postgresql.org>
2023-02-21 15:56 ` Re: MERGE examples not clear David G. Johnston <david.g.johnston@gmail.com>
@ 2023-09-27 23:42 ` Bruce Momjian <bruce@momjian.us>
2024-11-01 20:21 ` Re: MERGE examples not clear Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2023-09-27 23:42 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: dwayne.towell@gmail.com; pgsql-docs@lists.postgresql.org
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:
[text/x-diff] merge.diff (956B, ../../ZRS98kJEt9AZAtpM@momjian.us/2-merge.diff)
download | inline diff:
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml
index 0995fe0c04..4544ce92b3 100644
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -582,23 +582,6 @@ WHEN NOT MATCHED THEN
</programlisting>
</para>
- <para>
- Notice that this would be exactly equivalent to the following
- statement because the <literal>MATCHED</literal> result does not change
- during execution.
-
-<programlisting>
-MERGE INTO customer_account ca
-USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
-ON t.customer_id = ca.customer_id
-WHEN MATCHED THEN
- UPDATE SET balance = balance + transaction_value
-WHEN NOT MATCHED THEN
- INSERT (customer_id, balance)
- VALUES (t.customer_id, t.transaction_value);
-</programlisting>
- </para>
-
<para>
Attempt to insert a new stock item along with the quantity of stock. If
the item already exists, instead update the stock count of the existing
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MERGE examples not clear
2023-02-21 15:14 MERGE examples not clear PG Doc comments form <noreply@postgresql.org>
2023-02-21 15:56 ` Re: MERGE examples not clear David G. Johnston <david.g.johnston@gmail.com>
2023-09-27 23:42 ` Re: MERGE examples not clear Bruce Momjian <bruce@momjian.us>
@ 2024-11-01 20:21 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2024-11-01 20:21 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: dwayne.towell@gmail.com; pgsql-docs@lists.postgresql.org
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?"
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-11-01 20:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-21 15:14 MERGE examples not clear PG Doc comments form <noreply@postgresql.org>
2023-02-21 15:56 ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-27 23:42 ` Bruce Momjian <bruce@momjian.us>
2024-11-01 20:21 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox