Manual query vs trigger during data load

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