PostgreSQL Update not returning zero count

Question

Hi folks,

I tried to update a particular record with same value again and again, but it returns the affected rows value as 1 always.

update users set active = 1 where id = 304
1 row(s) affected.

again the same query

update users set active = 1 where id = 304
1 row(s) affected.

but the second update should return 0 row(s) affected right? May I know how can I get zero count while its not updating the record originally.

Answer 1

Your query is updating the record, hence returning 1.

Update does not check if the value is replacing is already there.

Answer 2

Unconditionally setting a column's value always "affects" it, even if the new value is the same as the old. If you would like to only see whether the value is now different, you could check the number of rows affected by something like

UPDATE users SET active=1 WHERE id = 304 AND active != 1;

That way the update query only matches rows which are not already "active"

Answer 3

As Schlansker already pointed out the following will do what you're are asking for.

UPDATE users SET active = 1 WHERE id = 304 AND active <> 1;

But, don't forget about transaction isolation. I.e. if two parallell transactions is updating the same row (or if one of them are deleting it) you may end up in an unexpected state. This depends on transaction isolation level and how the rest of the application is designed.