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:

1. 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.
2. 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.
3. 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).

Answer 1

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
);

• Luke Hutchison: Thank you, this is super helpful!