PostgreSQL trigger to update field when other field in the same table is updated

Question

I'm fairly new to SQL triggers and I'm struggling whit this:

I have a table that stores data about properties and their prices:

id_property price   area    price_m2
1           1500    60      25
2           3500    85      41

The clients could change the price of their property often, but the area won't. So I want to create a trigger that updates the price_m2 column when the price column is updated.

I've tried something like that or similar variations:

First create the function

CREATE FUNCTION update_precio_m2() RETURNS TRIGGER
AS
    $$
    BEGIN
        update my_table
        set price_m2 = new.price/old.area
        where (old.id = new.id);

        RETURN new;
    end
    $$
language plpgsql;

Then create the trigger

CREATE TRIGGER update_price_m2
    AFTER UPDATE ON my_table
    FOR EACH ROW
    WHEN (
        old.price IS DISTINCT FROM new.price
        )
    EXECUTE PROCEDURE update_price_m2();

But when I change the price I got unexpected results, like the price_m2 column change for various id's when I only want to change the value for one id (the one who changed).

Note I know it's an antipattern to store columns whose value depends on the operation between two other columns, but there is a reason for that in this case

Thanks!

If you're using a recent version of postgres, it would be easier to use a generated column. — Jeremy
– Jeremy, Commented Jul 16, 2021 at 1:13
Hey, @Jeremy that's a great feature! Solved my problem. If you want post it as a response and I give you the accepted mark — David López
– David López, Commented Jul 16, 2021 at 2:51
Generated column is the best choice. Just to answer your question the problem is in the where. You should have wrote where my_table.id = new.id — Philippe
– Philippe, Commented Jul 16, 2021 at 6:54

Jeremy · Accepted Answer · 2021-07-16 18:39:12Z

2

Just to follow up on this question so it can be closed, my recommendation in the comments was to use a generated column, which have been available since postgres 12:

https://www.postgresql.org/docs/current/ddl-generated-columns.html

The syntax would be something like this:

CREATE TABLE my_table (
  id_property bigint GENERATED ALWAYS AS IDENTITY,
  price int,
  area int,
  price_m2 int GENERATED ALWAYS AS (price / area) STORED
);

answered Jul 16, 2021 at 18:39

Jeremy

6,77322 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL trigger to update field when other field in the same table is updated

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related