Michael, I've never seen that syntax. Is that ANSI standard? The last SQL database I used did not require that syntax to return the "affected" count I needed. Is there any industry standard concerning the implementation of "affected"? Thanks ----- Original Message ----- From: "Michael Fuhr" <mike@xxxxxxxx> To: "Jan" <jan@xxxxxxxxxxxxxxxxxxxx> Cc: <pgsql-general@xxxxxxxxxxxxxx> Sent: Thursday, February 10, 2005 12:31 PM Subject: 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/ > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
