I have the following table in Postgres:
CREATE TABLE avg (
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
b, I want to find the row for a given
user_id, then I want to do
numer += aand
denom += b, and calculate
frac = numer / denomfrom the new values of
denom, then update the values of
fracin this row.
- However, if there is no row for the requested
user_id, or if there is a row, but the values of
denomare 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 / bfor the
- 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).