Date: Sat, 21 Sep 2024 14:48:09 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Clarify this MERGE warning? "Only columns from the target table
 that attempt to match data_source rows should appear in join_condition."
Message-ID: <20240921124809.mc6tdjx3vswpo2pu@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CALB5dS9y7W395PNn_b90ex7jyYkirKnS2OdcDcjq=eT=J32NSA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="dbsdx5dejfejvbdm"
Content-Disposition: inline
In-Reply-To: <CALB5dS9y7W395PNn_b90ex7jyYkirKnS2OdcDcjq=eT=J32NSA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240921124809.mc6tdjx3vswpo2pu%40hjp.at>
Precedence: bulk


--dbsdx5dejfejvbdm
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-09 14:02:50 +0100, Philip Hazelden wrote:
> The MERGE docs[1] give this warning:
>=20
> > Only columns from the target table that attempt to match
> > `data_source` rows should appear in `join_condition`.
> > `join_condition` subexpressions that only reference the target
> > table's columns can affect which action is taken, often in
> > surprising ways.
>=20
> (The docs for upcoming v17 have the same line.)
>=20
> But when I tested this, it seems to work fine. For example, consider a
> two-level primary key, where the source table implicitly has a fixed
> value for one level:
[...]

The warning is a bit misleading, IMHO. I think what this is trying to
say is that this is effectively data_source LEFT JOIN target ON
condition, and any row from data_source not matched by condition will
end up in the "NOT MATCHED" set. So you might insert rows from
data_source which you thought you had excluded in the condition.

So it's important to get the match right, and then decide what to do in
the WHEN clauses.


>     merge into t1 using t2
>       on t1.k2 =3D t2.k2 and t1.k1 =3D 1
>       when matched then update set v =3D t2.v
>       when not matched then insert values (1, t2.k2, t2.v);

I think that's ok. The t1.k1 =3D 1 is used to match rows from the target
to the data source (for each row in the data source, find the rows in
the target which have the same k2 and k1 =3D 1).

But "columns from the target table that attempt to match data_source`
rows" for me sort of sounds like those columns have to have a counterpart
in the data_source, which k1 hasn't. Also maybe the order is the wrong
way around? "Match rows in the target to rows in the data_source" would
fit my mental model better.

        hp
--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--dbsdx5dejfejvbdm
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmbuwIAACgkQ8g5IURL+
KF2VYg/+Pyv7XLXVu0DkLqZLLeKlWLniiq77QjENlmznsWI2c9gPLq6hIfFAkscG
uFX2e0PGWNlk4hLjvLQEQrKq09oHXKXIiOTAKTsFjDFLtO0+h7gPnUrkWMu9P4jA
b1HhiDTlgqI4c48E5Zv9XP5Mu7thXC1GJcFwxdhMVzZiAWylvDdrLWj8dWfpMuUp
D2BcMuMuHx8XoCWcFjthy8K1JWYPL/i3/hPXIPpTQ6j1DF4rjekdXum06knZIxs1
h+gNdGIX/KdMrSlakUonl5rA3encASp9upsQ+MSHt9L09ZD9P18zAlLst4isXM2P
FP3D8LuX90zCepoejAIZaOtOXXiOh2Co5ukzS3rFfvw6Rx+aXN60gSKJG09vfheL
9dN3iBw/jivmiIUWGzBUxnJCMA+bXEaYoVnOwlY13Blet6oMd/I//vWMy1OE55v7
MhXBT3pD/noW3gkIZfdlWhAoXZtkCr8l45PImtSAyC4p4lhQd4vd8P/2MJ+8n9N/
Zp+UtSw5OI1yiTLlbyH22kZ8G6vLyTma6VLhXM3dIJgQgt9tD/Eqfocv0pV78W6y
7gqufWYmxs5Cml6+S9GXjIuQE5Oeu6kDHV8UKVfElsZzziEnvUrVsajxYCNBoiIp
vcrIm4q/lGe2SihSMGA6hoK83aP+AsxITC7mLGxdhCL04bzjg/I=
=ebbE
-----END PGP SIGNATURE-----

--dbsdx5dejfejvbdm--