Re: impossible to update rows specifying columns with NULL

I found in our project the following construct to handle this situation:

UPDATE tab SET colx = ... WHERE ... coly IS NULL AND ? IS NULL OR coly = ? ...

- You must bind the value twice.
- We are using ORACLE, but it seems to bo standard like

Andreas

On Wed, Mar 02, 2005 at 09:32:06AM +0100, Guillaume Cottenceau wrote:
> Tom Lane <tgl 'at' sss.pgh.pa.us> writes:
>
> > Guillaume Cottenceau <gc(at)mnc(dot)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

--
Andreas Ulbrich
Otto Software Partner GmbH
Freiberger Str. 35; D-01067 Dresden
Tel.: [[0[049]351]49723]20