update with many common sub-expressions

Question

What is the best way to calculate a table where many columns are calculated from other columns in the same table and the formulas build on each other in complex ways.

If you don't care about performance, you could simply calculate each column with its own update statement:

update x set b1 = a1 + a2;
update x set b2 = a3 * b1;
update x set b3 = a4 * b2;
...

Or you can do everything in a single update if you expand the formulas manually:

update x set
  b1 = a1 + a2,
  b2 = a3 * (a1 + a2),
  b3 = a4 * (a3 * (a1 + a2));

The problem here is that these formulas can get huge and hard to change and debug.

Is there some way in Postgres (or maybe even SQL in general) so that you can have both performance and maintainability in these cases?

Here is something I wish I could do:

update x set
  b1 = _b1,
  b2 = _b2,
  b3 = _b3
with
  _b1 = a1 + a2,
  _b2 = a3 * _b1,
  _b3 = a4 * _b2;

I have one working solution where the formulas are defined in a function with multiple return values, but this is rather inflexible in some other ways, so I am looking for alternatives.

I actually don't have a problem with your current approach, and a workaround which doesn't involve dynamic SQL might be more work than it is worth. — Tim Biegeleisen, Feb 22 '19 at 10:48

score 1 · Answer 1 · answered Feb 22 '19 at 14:55

1

You can use the following in recent PostgreSQL versions:

UPDATE atable
SET (col1, col2, col3) =
SELECT expr1, expr2, expr3
FROM ...

If you need to reuse calculated expressions in the query, you can use WITH:

WITH t1(x1) AS (
   SELECT /* complicated */
), t2(x2) AS (
   SELECT /* complicated using t1 */
) ...
SELECT /* final results */

I am not sure if that makes the code more readable to you, but it avoids using a function and is standard compliant (I think, didn't check).

answered Feb 22 '19 at 14:55

Laurenz Albe

209,280
17
206
263

Thanks. I saw this in the Postgres documentation, however I am not sure how to apply this to my use case of updating within a single table without any selects involved. – cypres Feb 26 '19 at 10:25
My answer can only be as specific as your question. – Laurenz Albe Feb 26 '19 at 10:45

score 1 · Answer 2 · answered Apr 11 '19 at 14:25

I came up with something close to what I was looking for:

update x set (b1, b2, b3) = ((
  select b1, b2, b3
  from (select b1, b2, a4 * b2 as b3
  from (select b1, a3 * b1 as b2
  from (select a1 + a2 as b1
  ) as _) as _) as _
));

It is still more verbose than I would like, but basically achieves the goal of not having to expand the formulas, without using a function.

update with many common sub-expressions

2 Answers2