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
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?
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.
The two queries are not equivalent. The first query would evaluate the
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
WITHquery is non-recursive and side-effect-free (that is, it is a
SELECTcontaining 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 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
my_value still unchanged. Think of
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