All we need is an easy explanation of the problem, so here it is.
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?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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:
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂