Postgresql using Trigger to update column values after inserting values

Question

I want to insert the values into a tableA and then update the column[amount_hkd]. I have successfully use the trigger but the performance is really slow, it takes an hour to finish the insertion of 8400 rows after I apply the trigger. How can I improve the performance? Thanks for your help

The statement to exectue:

INSERT INTO tableA (suppliers, invoice_dates, shipment_dates, amounts, currency, currency_conversion_rate)
SELECT l.supplier, l.invoice_date, l.shipment_date, l.amount, l.currency, o.currency_conversion_rate
FROM tableB l
LEFT JOIN tableC o
ON l.currency = o.currency_code
WHERE l.supplier = 'ABC'

The function I created:

CREATE OR REPLACE FUNCTION cal() 
RETURNS TRIGGER AS $$ 
BEGIN 
UPDATE tableA 
SET amount_hkd = NEW.amounts * NEW.currency_conversion_rate;
RETURN NEW; 
END; 
$$ LANGUAGE plpgsql;

The 1st Trigger I tried:

CREATE CONSTRAINT TRIGGER update_amount
AFTER INSERT ON tableA
DEFERRABLE
INITIALLY DEFERRED
FOR EACH ROW 
EXECUTE PROCEDURE cal();

The 2nd Trigger I tried:

CREATE TRIGGER update_amount 
AFTER INSERT ON tableA
FOR EACH ROW 
EXECUTE PROCEDURE cal();

Your trigger updates all rows with the same value for amount_hkd every time you insert one. That's why it is slow. Is this what you really need? — Stefanov.sm
– Stefanov.sm, Commented Aug 26, 2021 at 11:04

Lal · Accepted Answer · 2022-11-20 18:58:32Z

8

It is terribly inefficient to update the row after you inserted it. Better is to use a BEFORE trigger that can modify the new row before it is inserted:

CREATE OR REPLACE FUNCTION cal() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN 
   NEW.amount_hkd := NEW.amounts * NEW.currency_conversion_rate;
   RETURN NEW; 
END;$$;

CREATE TRIGGER update_amount 
   BEFORE INSERT ON tableA FOR EACH ROW 
   EXECUTE PROCEDURE cal();

edited Nov 20, 2022 at 18:58

Lal

14.9k4 gold badges50 silver badges76 bronze badges

answered Aug 26, 2021 at 11:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Belayer · Accepted Answer · 2021-08-26 17:54:28Z

3

You did not specify your Postgres version, but if at least V12 you can avoid triggers altogether. Define the column amount_hkd as a generated column. Not only do you avoid triggers, but you also eliminate maintenance issues of updating the currency_conversion_rate and/or amount and forgetting to also update amount_hkd, or a direct update to amount_hkd. Something like: ( see example here)

create table table_a( a_id integer generated always as identity 
                    , amounts  decimal
                    , currency_conversion_rate decimal
                    , amount_hkd decimal generated always as ( amounts * currency_conversion_rate ) stored
                    );

answered Aug 26, 2021 at 17:54

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Collectives™ on Stack Overflow

Postgresql using Trigger to update column values after inserting values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related