Re: impossible to update rows specifying columns with NULL

Tom Lane <tgl 'at' sss.pgh.pa.us> writes:

> 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.

Thanks for your advices.

Actually my "workaround" has been very logical: I use two
different PreparedStatement.

About data model, you may be right, I absolutely don't pretend to
be any good in data modeling :). The table I'm dealing with
represents a money balance, counted to send warnings to users
when they reach configurable levels of expenses. The column that
can be NULL represents a subset of locations where expenses can
occur. We use non-NULL values when we want to count per-user and
per-location, and NULL value when we want to count per-user but
for all locations together. These two situations exist because
of external constraints.

--
Guillaume Cottenceau