How to update a Postgres row atomically, with a computed value that depends on the new values

Question

I have the following table in Postgres:

CREATE TABLE avg (
    user_id INT,
    numer DOUBLE PRECISION,
    denom DOUBLE PRECISION,
    frac DOUBLE PRECISION
);

I want to accomplish the following, and I'm having a hard time getting this into one query. Basically I want to be able to update some averages atomically, by separately updating the numerator and denominator, then compute and update the average frac in the same operation, based on the new values:

Given params a and b, I want to find the row for a given user_id, then I want to do numer += a and denom += b, and calculate frac = numer / denom from the new values of numer and denom, then update the values of numer, denom, and frac in this row.
However, if there is no row for the requested user_id, or if there is a row, but the values of numer and denom are null, then I want to assume that the value of those sums is zero, in other words a new row should be inserted into the table, or an update operation should be performed if there is a row but the values are null, with the result being to set numer = a, denom = b, and frac = a / b for the user_id.
I want to ensure this all happens atomically.

There may be multiple numerators and denominators in a given table, hence the need to be able to do both insert (if the row doesn't exist) or update (if the row exists but the initial values of a specific numerator/denominator pair are null).

Laurenz Albe · Accepted Answer · 2024-02-02 08:55:38Z

The best practice is not to store redundant data in a database. My solution would be

CREATE TABLE real_avg (
    user_id bigint PRIMARY KEY,
    numer double precision,
    denom double precision
);

CREATE VIEW avg
AS SELECT user_id, numer, denom,
          numer / denom AS frac
   FROM real_avg;

Then you can perform your desired operation as follows:

INSERT INTO avg (user_id, numer, denom)
   VALUES (1, 100, 50)
ON CONFLICT (user_id) DO UPDATE
SET numer = avg.numer + EXCLUDED.numer,
    denom = avg.denom + EXCLUDED.denom;

If you insist on using a redundant table column, use a generated column that is automatically updated:

CREATE TABLE real_avg (
    user_id bigint PRIMARY KEY,
    numer double precision,
    denom double precision,
    frac double precision GENERATED ALWAYS AS (numer / denom) STORED
);

Thank you, this is super helpful!

Luke Hutchison
– Luke Hutchison

2024-02-05 21:48:01 +00:00
Commented Feb 5, 2024 at 21:48 — Luke Hutchison
– Luke Hutchison, Commented Feb 5, 2024 at 21:48

Stack Exchange Network

How to update a Postgres row atomically, with a computed value that depends on the new values

1 Answer 1

Your Answer

Hot Network Questions

How to update a Postgres row atomically, with a computed value that depends on the new values

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions