Returning in query whether a column has been changed by the current query or not

by

The question:

I have a query like

UPDATE my_table 
  SET my_value = 
    CASE WHEN random() > 0.5 THEN my_value * 2 ELSE my_value END
RETURNING *;

Now, inside the RETURNING statement I’d like to have a boolean indicating whether my_value has been changed by the current query or not.

Let’s assume, I cannot pass the previous value of my_value as a param to the query.

So, is there a way to obtain something like a list of columns which have different values after the UPDATE? Or get the values at the state before UPDATE in RETURNING?

In my example, I could, of course, put the result of random() in a CTE like

WITH random_cte AS (
    SELECT random() AS my_random
)
UPDATE my_table 
  SET my_value = 
    CASE WHEN my_random > 0.5 THEN my_value * 2 ELSE my_value END
FROM random_cte
RETURNING *, my_random > 0.5 AS value_changed;

But that would bloat the query somewhat up. So I’m wondering if I could do that in a more elegant way?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

The two queries are not equivalent. The first query would evaluate the VOLATILE function random() for every row, while the second evaluates it once in the CTE, so my_random is the same for all rows.

And no, the CTE won’t be inlined. The manual:

However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query

Bold emphasis mine.

But that’s probably just an accident in the construction of your test.

Either query updates all rows, even if nothing changes.

To get your value_changed reliably, compare pre-UPDATE with post-UPDATE values:

UPDATE my_table t
SET    my_value = CASE WHEN random() > 0.5 THEN t.my_value * 2 ELSE t.my_value END
FROM  (SELECT id, my_value FROM my_table) pre
WHERE  t.id = pre.id
RETURNING t.*, t.my_value IS DISTINCT FROM pre.my_value AS value_changed;

id being the PK or any other (combination of) unique not-null column(s).

random() > 0.5 can be true and my_value still unchanged. Think of NULL or 0.

There is a potential race condition under concurrent write load. See:

If your use case is really that simple (only a single column to be updated), you’d rather suppress empty updates to begin with. See:


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment