Date: Sun, 15 Sep 2024 23:22:48 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Manual query vs trigger during data load
Message-ID: <20240915212248.mlhla3oibvuyrdhe@hjp.at>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAEzWdqfbZqtMuc5MD3g+iQDqN75WubeWcEvO6eDSBC_QmbKDvg@mail.gmail.com>
 <CAHbZ42zSrQAANMHaU78N9CBFYoK47kReRebijd8XS3+Oag8htA@mail.gmail.com>
 <f76b71b5-1f99-43bd-bdca-202999ea94be@aklaver.com>
 <67f2a664-b480-40ea-892d-2ab2147ad390@aklaver.com>
 <CAEzWdqe0VDpTi26xby+cNTMUp6goyZE1tENua08Cg-_az1mzqQ@mail.gmail.com>
 <20240914104713.jatugdwnmojwa7kf@hjp.at>
 <CAEzWdqe0J+OG8rgBmsfA1-JS-O8AVCq+NvxxCY-htpTrxRMZmg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="zglukna2do6srbey"
Content-Disposition: inline
In-Reply-To: <CAEzWdqe0J+OG8rgBmsfA1-JS-O8AVCq+NvxxCY-htpTrxRMZmg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/20240915212248.mlhla3oibvuyrdhe%40hjp.at>
Precedence: bulk


--zglukna2do6srbey
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2024-09-14 21:21:45 +0530, yudhi s wrote:
> On Sat, Sep 14, 2024 at 4:17=E2=80=AFPM Peter J. Holzer <hjp-pgsql@hjp.at=
> wrote:
>     On 2024-09-14 00:54:49 +0530, yudhi s wrote:
>     > As "thiemo" mentioned , it can be done as below method, but if
>     > we have multiple=C2=A0lookup tables to be populated for multiple
>     > columns , then , how can the INSERT query be tweaked to cater
>     > the need here?
>=20
>     Just use a join:
>     =C2=A0 =C2=A0 insert into target(val1, val2, val3, val4)
>     =C2=A0 =C2=A0 select :param1, cfgA.substA, :param3, cfgB.substB
>     =C2=A0 =C2=A0 from cfgA, cfgB
>     =C2=A0 =C2=A0 where cfgA.keyA =3D :param2 and cfgB.keyB =3D :param4
>=20
>     Or use a CTE per lookup which might be more readable:
>=20
>     =C2=A0 =C2=A0 with cA as ( select substA from cfgA where keyA =3D :pa=
ram2 ),
>     =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0cB as ( select substB from cfgB whe=
re keyB =3D :param4 )
>     =C2=A0 =C2=A0 insert into target(val1, val2, val3, val4)
>     =C2=A0 =C2=A0 select :param1, cA.substA, :param3, cB.substB
>     =C2=A0 =C2=A0 from cA, cB
>=20
>=20
>=20
> Thank you. I will try these options.=C2=A0
> Also we are trying to do something as below , which will separate the tab=
les
> based on the specific=C2=A0lookup fields for the target tables and thus i=
t will look
> simple rather than using those reference tables in the From clause which =
may
> cause some confusion in reading the code or not sure if it will cause
> cartesian. Please correct me if I'm wrong.

My examples do form a cartesian product, but as long as the keys are
unique, that's 1 * 1 * 1 ... * 1 =3D 1 rows. So that should not be a
problem in case of simple lookup tables.

That may not be immediately apparent to someone reading the code,
though. And it might fail horribly if the lookups aren't guaranteed to
return a single row.


> INSERT INTO tab_part1 (column1, column2, column3, column4, column5, part_=
date)
> VALUES ( :v_col1, (SELECT lookup_value FROM reference_tab1 WHERE lookup_k=
ey =3D
> :v_col2), :v_col3, :v_col4, :v_col5, CURRENT_DATE );=C2=A0

Your approach is safer in that it will abort with an error if the
subquery ever returns more than one value. It will also still insert a
row (with null in column2) if the subquery returns no rows, which may or
may not be what you want (and if you don't want it you can probably
prevent it with a not null constraint). Looks good to me.

        hp

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

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

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

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmbnUB8ACgkQ8g5IURL+
KF22bg/9H+F75Ae6yEtRB1hSGIGkFQ79tS6BQ7A6NFsifWfms1UDZJ79kHXQVtYi
btZox2xh3/ASVP1mEXNLqMAloz8Ii6ajEXVlroG+gl6OS1xJ2eKqQseP5rQpNUOD
0xy+NIn1HJ/U0HG2XW8/4mqGoQSF38gHkzloUDdQEaZ72dj7WBThb9ZkYIdiLn9Y
FdTn5xqLbOC0K7vCX0jycuvC9vSQSl4COEvBve7iO+CrwCCVm3hgra4Q7DuHYWtI
JGEwIkleMIT2wURi44fZgWNjg2pszAL3RgdP/mU4tL1hkgyYonriVN/86Psh9Nbe
PftvwKWlU5qLv7yg8MRm+cYOxN1qitrOVsEbcd0I+N0ytCR1t9aRV68QhRYYB7J0
brWIuZhqw0GrSvop3Tq4G8hXeBvwPLVRvqi1jZSrHA+JEyBDqvxqBKuRHsHtiDSO
ynulOdd4/aebSbTxIhZ6iqixLjs0bhY6SiVoEVkZ17rq8HUnpRaAoKJ5Oq9RoAi8
UoboyaZRI1RiMDOwZpyCj3Bx4JiUoFK+OPX4kfprL7DLLw34LShmGYzIB8YcUpK0
gcYeLu12K1edbpTcgGNPfdC2JnFNOodPlKJ/GjvBDMsbX/FYennc+fVRaUQUt6Za
iXQCzWZHx7mV968RQVBFCo+iFmahrewHxClYOo40uNggTqX/wUI=
=EIXj
-----END PGP SIGNATURE-----

--zglukna2do6srbey--