MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Fri, 13 Sep 2024 13:01:58 +0530
Message-ID: <CAEzWdqfbZqtMuc5MD3g+iQDqN75WubeWcEvO6eDSBC_QmbKDvg@mail.gmail.com>
Subject: Manual query vs trigger during data load
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009f8d790621fb3890"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqfbZqtMuc5MD3g%2BiQDqN75WubeWcEvO6eDSBC_QmbKDvg%40mail.gmail.com>
Precedence: bulk

--0000000000009f8d790621fb3890
Content-Type: text/plain; charset="UTF-8"

Hello All,

We are having a table which is going to be inserted with 100's of millions
of rows each day. And we now want to have a requirement in which we need to
do some transformation/lookup logic built on top of a few of the input bind
values , while inserting the data. So I wanted to understand ,is it
possible to do it along with the INSERT query or is it better to have a
trigger created for the same?

For. e.g Below is the current Insert query used in the Java code. We want
to fetch the value for "column2" from a lookup table rather than directly
inserting as it's coming from the customer side. So I am thinking of a
trigger like below. But at the same time I also want to compare the
performance of a normal way of doing the lookup vs having it performed
using triggers.

So one way i am thinking is first fetching the value of the "column2" from
reference_tab1 using a separate "select query" in Java code itself,  and
then passing that to the below insert query, but i think that will increase
the response time as that will be a separate DB call.

1)So,  is there a way I can do it directly using the single INSERT query
itself without additional SELECT query? And then will try to compare that
with the trigger based approach.
2)Additionally , if this decision will impact a batch insert approach. i.e.
say , in case of trigger , will the batch insert fail because trigger will
force it to make it row by row?

INSERT INTO tab_part1 (column1, column2, column3, column4, column5,
part_date)
        VALUES (:v_col1, :v_col2, :v_col3, :v_col3, :v_col4,:v_col5,
CURRENT_DATE);

CREATE OR REPLACE FUNCTION trg_func_populate_column2() RETURNS TRIGGER AS $$
BEGIN
    -- Fetch reference value and populate column2
    NEW.column2 := (SELECT lookup_key FROM reference_tab1 WHERE lookup_key
= old.column2);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Regards
Yudhi

--0000000000009f8d790621fb3890
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello All,<div><br>We are having a table which is going to=
 be inserted with 100&#39;s of millions of rows each day. And we now want t=
o have a requirement in which we need to do some transformation/lookup logi=
c built on top of a few of the input bind values , while inserting the data=
. So I wanted to understand ,is it possible to do it along with the INSERT =
query or is it better to have a trigger created for the same?<br><br>For. e=
.g Below is the current Insert query used in the Java code. We want to fetc=
h the value for &quot;column2&quot;	from a lookup table rather than directl=
y inserting as it&#39;s coming from the customer side. So I am thinking of =
a trigger like below. But at the same time I also want to compare the perfo=
rmance of a normal way of doing the lookup vs having it performed using tri=
ggers. <br><br>So one way i am thinking is first fetching the value of the =
&quot;column2&quot; from reference_tab1 using a separate &quot;select query=
&quot; in Java code itself, =C2=A0and then passing that to the below insert=
 query, but i think that will increase the response time as that will be a =
separate DB call.</div><div><br></div><div>1)So,=C2=A0=C2=A0is there a way =
I can do it directly using the single INSERT query itself without additiona=
l SELECT query? And then will try to compare that with the trigger based ap=
proach.<br>2)Additionally , if this decision will impact a batch insert app=
roach. i.e. say , in case of trigger , will the batch insert fail because t=
rigger=C2=A0will force it to make it row by row?<br><br>INSERT INTO tab_par=
t1 (column1, column2, column3, column4, column5, part_date)<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 VALUES (:v_col1, :v_col2, :v_col3, :v_col3, :v_col4,:v_co=
l5, CURRENT_DATE);<br><br>CREATE OR REPLACE FUNCTION trg_func_populate_colu=
mn2() RETURNS TRIGGER AS $$<br>BEGIN<br>=C2=A0 =C2=A0 -- Fetch reference va=
lue and populate column2<br>=C2=A0 =C2=A0 NEW.column2 :=3D (SELECT lookup_k=
ey FROM reference_tab1 WHERE lookup_key =3D old.column2);<br>=C2=A0 =C2=A0 =
RETURN NEW;<br>END;<br>$$ LANGUAGE plpgsql;<br></div><div><br></div><div>Re=
gards</div><div>Yudhi</div></div>

--0000000000009f8d790621fb3890--