0

In a statement like this:

update tab1 set (col1,col2)=(val1,val2) returning "?"

I send whole row for update on new values, RETURNING * gives back the whole row, but is there a way to check which exactly column has changed when others remained the same?

I understand that UPDATE rewrites the values, but maybe there is some built-in function for such comparison?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ivan Kolyhalov
  • 902
  • 11
  • 16
  • perhaps a trigger on update? – Jim Jones Sep 21 '20 at 10:45
  • Can you provide an example? – Ivan Kolyhalov Sep 21 '20 at 10:46
  • 2
    It would be helpful if you provide some data yourself: 1) create table statement, 2) sample data and 3) the queries that reproduce your scenario. Just to avoid that we misunderstand your question. – Jim Jones Sep 21 '20 at 10:49
  • Well it's a typical scenario. update tab1 set (col1,col2)=(val1,val2) returning "?" – Ivan Kolyhalov Sep 21 '20 at 10:56
  • 2
    There are ways. How exactly do you `send whole row for update on new values`. Please show your current code. And how does "upsert" play into this. At first, you only speak of "update". – Erwin Brandstetter Sep 21 '20 at 11:07
  • I think I provided it in previous comment, or you want the original code? It is just the same, but much longer. Upsert through insert or just update - no matter for me, if you have some working example please provide it. I do not stick to method, but solution. – Ivan Kolyhalov Sep 21 '20 at 11:24
  • 2
    Please [edit] all defining information into the question. Comments are not the place. The short version is good, as long as the principal problem is the same. – Erwin Brandstetter Sep 21 '20 at 11:27

1 Answers1

2

Basically, you need the pre-UPDATE values of updated rows to compare. That's kind of hard as RETURNING only returns post-UPDATE state. But can be worked around. See:

So this does the basic trick:

WITH input(col1, col2) AS (
   SELECT 1, text 'post_up'  -- "whole row"
   )
, pre_upd AS (
   UPDATE tab1 x
   SET    (col1, col2) = (i.col1, i.col2)
   FROM   input i
   JOIN   tab1  y USING (col1)
   WHERE  x.col1 = y.col1
   RETURNING y.*
   )
TABLE  pre_upd
UNION ALL
TABLE  input;

db<>fiddle here

This is assuming that col1 in your example is the PRIMARY KEY. We need some way to identify rows unambiguously.

Note that this is not safe against race conditions between concurrent writes. You need to do more to be safe. See related answer above.

The explicit cast to text I added in the CTE above is redundant as text is the default type for string literals anyway. (Like integer is the default for simple numeric literals.) For other data types, explicit casting may be necessary. See:

Also be aware that all updates write a new row version, even if nothing changes at all. Typically, you'd want to suppress such costly empty updates with appropriate WHERE clauses. See:

While "passing whole rows", you'll have to check on all columns that might change, to achieve that.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Well looks like generally (and hopefully safer and easier) this can be done in app code by querying current statement and then with "returning * " compare values in code. I hoped there would be some easier solution... – Ivan Kolyhalov Sep 21 '20 at 11:45
  • 1
    @IvanKolyhalov: Doing it in app code simplifies the logic in SQL. **But** you need two round trips to the server if you first fetch update candidates, and then do the actual update. That widens the time window for interfering concurrency. Row locks (or heavier guns) to defend against race conditions have to stay in place (much) longer, possibly hampering performance for write-heavy use cases. – Erwin Brandstetter Sep 21 '20 at 12:00