Re: pg_affected Change Request

On Thu, Feb 10, 2005 at 05:56:33AM -0500, Jan wrote:
>
> I write a program that mines data from a small few websites.  I revisit
> those websites on a daily basis.  I find a matching key (actually two fields
> comprise my unique key) and with the data collected on this visit I attempt
> to UPDATE an existing record.  I want to know whether I just changed the
> data or that the data collected is the same as on my last visit.
>
> I use PHP.  If I check pg_affected_rows($result) I find one record is always
> "affected" even when no data has actually changed.  Nothing has changed so
> the rows affected should be zero.  The "affected" is actually "attempted".

PostgreSQL stores a new version of each row regardless of whether
the update changed any columns or not, so in that sense all of the
rows were "affected."  Presumably there's a reason for doing this,
although at the moment I'm not remembering why.

The following is a bit ugly, but if you want to update only those
rows where a value has changed, then you could do something like
this:

UPDATE tablename SET col1 = <col1value>, col2 = <col2value>, ...
WHERE keycol = <keyvalue>
  AND (col1 IS DISTINCT FROM <col1value> OR
       col2 IS DISTINCT FROM <col2value> ...)

This statement uses IS DISTINCT FROM instead of <> so the comparisons
will handle NULLs properly.  If the columns are all NOT NULL then
you could use <>.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/