Re: Confusing reference to MERGE use inside WITH

On Sun, Jul 16, 2023 at 5:48 AM PG Doc comments form <noreply(at)postgresql(dot)org>
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/queries-with.html
> Description:
>
> Hi.
>
> In the queries WITH documentation
> https://www.postgresql.org/docs/15/queries-with.html it is a bit confusing
> that MERGE is referenced in several places as being something you can use
> inside with, even though it can't.
>
> If you search for MERGE on the page, you will find the 2 first results is a
> reference saying MERGE can be used inside WITH,

the same as SELECT, INSERT,
> UPDATE, or DELETE.

No, they are not, see below.

The 3rd result then correctly states that MERGE cannot be
> used inside WITH.
>
>
Yes.

The first non-intro section says that the following syntax is allowed (you
can attach WITH /TO/ MERGE)

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]

https://www.postgresql.org/docs/current/sql-merge.html
The second section is saying that the "with_query" above cannot contain
MERGE, i.e., you cannot put MERGE /IN/ WITH

Namely because you cannot attach a RETURNING clause to MERGE.

The intro makes it quite clear as well:

Specifically, reading the last sentence makes it clear:

Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE,
or DELETE; and the WITH clause itself is attached to a primary statement
that can be a SELECT, INSERT, UPDATE, DELETE, or MERGE.

Here the inside is called the auxiliary while the outside is called primary.

David J.