Re: impossible to update rows specifying columns with NULL

Guillaume Cottenceau <gc@mnc.ch> writes:
> Markus Schaber <schabios 'at' logi-track.com> writes:
>> You don't have to do this globally, you can also issue
>> set transform_null_equals to true;
>> as statement so this setting is only for your connection.

> In the doc pointed by Oliver I can read that this NULL != NULL
> behaviour is per SQL standard, so I'm unsure if I should go the
> way of forcing the non standard behaviour..

I don't think it will help you anyway.  That kluge only deals with
the literal syntax "something = NULL" where the NULL is written out
as the keyword NULL.  You appear to be wishing that "something = $n"
would be treated as "something IS NULL" if the parameter $n happened
to have the value NULL, and that most definitely isn't going to happen.

A workaround in recent PG versions is to use "IS DISTINCT FROM", which
is a version of != that works the way you want with nulls.  However this
is guaranteed not to be indexable so I don't know how useful it is in
real-world cases.

In my mind, if you are up against this it suggests that you are misusing
NULL as a "real" data value, which is going to be a big headache given
the SQL sematics for NULL.  You ought to rethink your data
representation.

            regards, tom lane