public inbox for [email protected]help / color / mirror / Atom feed
Re: Manual query vs trigger during data load 7+ messages / 4 participants [nested] [flat]
* Re: Manual query vs trigger during data load @ 2024-09-13 14:50 Adrian Klaver <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Adrian Klaver @ 2024-09-13 14:50 UTC (permalink / raw) To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]> On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: > Hello, I find it unlikely that the trigger will work properly, since the > reserved fields of the OLD subset have no value in an INSERT I'm not seeing that the OP is asking for OLD.* values, they are just looking to include the result of a lookup on another table in the INSERT. > > Atte > JRBN > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual query vs trigger during data load @ 2024-09-13 14:57 Adrian Klaver <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Adrian Klaver @ 2024-09-13 14:57 UTC (permalink / raw) To: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; yudhi s <[email protected]>; +Cc: pgsql-general <[email protected]> On 9/13/24 07:50, Adrian Klaver wrote: > On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: >> Hello, I find it unlikely that the trigger will work properly, since >> the reserved fields of the OLD subset have no value in an INSERT > > I'm not seeing that the OP is asking for OLD.* values, they are just > looking to include the result of a lookup on another table in the INSERT. My mistake I see the OLD reference now. > >> >> Atte >> JRBN >> > > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual query vs trigger during data load @ 2024-09-13 17:22 Rob Sargent <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 0 replies; 7+ messages in thread From: Rob Sargent @ 2024-09-13 17:22 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Juan Rodrigo Alejandro Burgos Mella <[email protected]>; yudhi s <[email protected]>; pgsql-general <[email protected]> > On Sep 13, 2024, at 10:57 AM, Adrian Klaver <[email protected]> wrote: > > On 9/13/24 07:50, Adrian Klaver wrote: >>> On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: >>> Hello, I find it unlikely that the trigger will work properly, since the reserved fields of the OLD subset have no value in an INSERT >> I'm not seeing that the OP is asking for OLD.* values, they are just looking to include the result of a lookup on another table in the INSERT. > > My mistake I see the OLD reference now. > >>> Personally I would cache the lookup on the java side a send the correct value to a simple insert statement > > > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual query vs trigger during data load @ 2024-09-13 19:24 yudhi s <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: yudhi s @ 2024-09-13 19:24 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; Juan Rodrigo Alejandro Burgos Mella <[email protected]>; Rob Sargent <[email protected]>; +Cc: pgsql-general <[email protected]> On Fri, Sep 13, 2024 at 8:27 PM Adrian Klaver <[email protected]> wrote: > On 9/13/24 07:50, Adrian Klaver wrote: > > On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: > >> Hello, I find it unlikely that the trigger will work properly, since > >> the reserved fields of the OLD subset have no value in an INSERT > > > > I'm not seeing that the OP is asking for OLD.* values, they are just > > looking to include the result of a lookup on another table in the INSERT. > > My mistake I see the OLD reference now. > > > > My mistake.The trigger was supposed to use "new.col2" and fetch the corresponding lookup value from the lookup table and insert that value to the target table. Now my question was ,in such a situation , the trigger will work fine , but is that the optimal way of doing ? Or should we convert the query someway such that the lookup table can be queried along with the INSERT at one shot from the database with a single DB call? And is it true that the trigger on the target table will suppress the batch insert and make it row by row, even if we call it in a batch fashion? As "thiemo" mentioned , it can be done as below method, but if we have multiple lookup tables to be populated for multiple columns , then , how can the INSERT query be tweaked to cater the need here? And I understand , the lookup table can be cached in Java and refreshed at a certain point in time, but I was trying to understand if this can be doable by directly querying the database, considering the lookup tables are having large data sets in them. Insert into tab1 (val1, val2) Select valA, valB From tab2 Where valC = :param1 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual query vs trigger during data load @ 2024-09-14 10:47 Peter J. Holzer <[email protected]> parent: yudhi s <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Peter J. Holzer @ 2024-09-14 10:47 UTC (permalink / raw) To: [email protected] 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 lookup tables to be populated for multiple columns , then , how can > the INSERT query be tweaked to cater the need here? Just use a join: insert into target(val1, val2, val3, val4) select :param1, cfgA.substA, :param3, cfgB.substB from cfgA, cfgB where cfgA.keyA = :param2 and cfgB.keyB = :param4 Or use a CTE per lookup which might be more readable: with cA as ( select substA from cfgA where keyA = :param2 ), cB as ( select substB from cfgB where keyB = :param4 ) insert into target(val1, val2, val3, val4) select :param1, cA.substA, :param3, cB.substB from cA, cB However, I agree with Rob here. It's probably better to do the substitution in Java. hp -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | [email protected] | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual query vs trigger during data load @ 2024-09-14 15:51 yudhi s <[email protected]> parent: Peter J. Holzer <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: yudhi s @ 2024-09-14 15:51 UTC (permalink / raw) To: [email protected] On Sat, Sep 14, 2024 at 4:17 PM Peter J. Holzer <[email protected]> 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 lookup tables to be populated for multiple columns , then , how > can > > the INSERT query be tweaked to cater the need here? > > Just use a join: > insert into target(val1, val2, val3, val4) > select :param1, cfgA.substA, :param3, cfgB.substB > from cfgA, cfgB > where cfgA.keyA = :param2 and cfgB.keyB = :param4 > > Or use a CTE per lookup which might be more readable: > > with cA as ( select substA from cfgA where keyA = :param2 ), > cB as ( select substB from cfgB where keyB = :param4 ) > insert into target(val1, val2, val3, val4) > select :param1, cA.substA, :param3, cB.substB > from cA, cB > > Thank you. I will try these options. Also we are trying to do something as below , which will separate the tables based on the specific lookup fields for the target tables and thus it 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. INSERT INTO tab_part1 (column1, column2, column3, column4, column5, part_date) VALUES ( :v_col1, (SELECT lookup_value FROM reference_tab1 WHERE lookup_key = :v_col2), :v_col3, :v_col4, :v_col5, CURRENT_DATE ); ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Manual query vs trigger during data load @ 2024-09-15 21:22 Peter J. Holzer <[email protected]> parent: yudhi s <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Peter J. Holzer @ 2024-09-15 21:22 UTC (permalink / raw) To: [email protected] On 2024-09-14 21:21:45 +0530, yudhi s wrote: > On Sat, Sep 14, 2024 at 4:17 PM Peter J. Holzer <[email protected]> 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 lookup tables to be populated for multiple > > columns , then , how can the INSERT query be tweaked to cater > > the need here? > > Just use a join: > insert into target(val1, val2, val3, val4) > select :param1, cfgA.substA, :param3, cfgB.substB > from cfgA, cfgB > where cfgA.keyA = :param2 and cfgB.keyB = :param4 > > Or use a CTE per lookup which might be more readable: > > with cA as ( select substA from cfgA where keyA = :param2 ), > cB as ( select substB from cfgB where keyB = :param4 ) > insert into target(val1, val2, val3, val4) > select :param1, cA.substA, :param3, cB.substB > from cA, cB > > > > Thank you. I will try these options. > Also we are trying to do something as below , which will separate the tables > based on the specific lookup fields for the target tables and thus it 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 = 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_key = > :v_col2), :v_col3, :v_col4, :v_col5, CURRENT_DATE ); 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 -- _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | [email protected] | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-09-15 21:22 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-13 14:50 Re: Manual query vs trigger during data load Adrian Klaver <[email protected]> 2024-09-13 14:57 ` Adrian Klaver <[email protected]> 2024-09-13 17:22 ` Rob Sargent <[email protected]> 2024-09-13 19:24 ` yudhi s <[email protected]> 2024-09-14 10:47 ` Peter J. Holzer <[email protected]> 2024-09-14 15:51 ` yudhi s <[email protected]> 2024-09-15 21:22 ` Peter J. Holzer <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox