Thread: impossible to update rows specifying columns with NULL value?

impossible to update rows specifying columns with NULL value?

From
Guillaume Cottenceau
Date:
Hi,

I have a table with a column of type `integer'. I insert data in
this column with NULL value. The NULLs should be OK in the
database since when I perform a SELECT on this row, after a
getInt on this column (value obtained is 0), ResultSet#wasNull is
true.

The problem is when trying to UPDATE (changing values of other
columns): my PreparedStatement uses "... WHERE column = ?" to
specify the NULL value for the said column, and I set the value
with "ResultSet#setNull( 4, java.sql.Types.NUMERIC )", but when
executing the statement, no change occurs (0 rows affected).

I am using postgres 7.4.5 with postgresql-8.0-310.jdbc3.jar.

Any idea?

Thank you.

--
Guillaume Cottenceau

Re: impossible to update rows specifying columns with NULL

From
Oliver Jowett
Date:
Guillaume Cottenceau wrote:

> The problem is when trying to UPDATE (changing values of other
> columns): my PreparedStatement uses "... WHERE column = ?" to
> specify the NULL value for the said column,

You can't do that and get the results you expect. "column = NULL" is
never true (because "NULL = NULL" is false). You should use IS NULL to
match a NULL value.

See http://www.postgresql.org/docs/8.0/static/functions-comparison.html
for details.

-O

Re: impossible to update rows specifying columns with NULL value?

From
Guillaume Cottenceau
Date:
Oliver Jowett <oliver 'at' opencloud.com> writes:

> Guillaume Cottenceau wrote:
>
> > The problem is when trying to UPDATE (changing values of other
> > columns): my PreparedStatement uses "... WHERE column = ?" to
> > specify the NULL value for the said column,
>
> You can't do that and get the results you expect. "column = NULL" is
> never true (because "NULL = NULL" is false). You should use IS NULL to
> match a NULL value.

It's a pity. This would be very helpful. With such situation, we
can't use the same prepared-statement to match NULL and non-NULL
values then, or I missed something?

--
Guillaume Cottenceau

Re: impossible to update rows specifying columns with NULL

From
Oliver Jowett
Date:
Guillaume Cottenceau wrote:
> Oliver Jowett <oliver 'at' opencloud.com> writes:
>
>
>>Guillaume Cottenceau wrote:
>>
>>
>>>The problem is when trying to UPDATE (changing values of other
>>>columns): my PreparedStatement uses "... WHERE column = ?" to
>>>specify the NULL value for the said column,
>>
>>You can't do that and get the results you expect. "column = NULL" is
>>never true (because "NULL = NULL" is false). You should use IS NULL to
>>match a NULL value.
>
> It's a pity. This would be very helpful.

Unfortunately, that's just the way that SQL NULLs work..

> With such situation, we
> can't use the same prepared-statement to match NULL and non-NULL
> values then, or I missed something?

You are correct.

-O

Re: impossible to update rows specifying columns with NULL

From
Dave Cramer
Date:
Well, if you are really intent on using this you can set
transform_null_equals=true in postgresql.conf

Dave

Oliver Jowett wrote:

> Guillaume Cottenceau wrote:
>
>> Oliver Jowett <oliver 'at' opencloud.com> writes:
>>
>>
>>> Guillaume Cottenceau wrote:
>>>
>>>
>>>> The problem is when trying to UPDATE (changing values of other
>>>> columns): my PreparedStatement uses "... WHERE column = ?" to
>>>> specify the NULL value for the said column,
>>>
>>>
>>> You can't do that and get the results you expect. "column = NULL" is
>>> never true (because "NULL = NULL" is false). You should use IS NULL to
>>> match a NULL value.
>>
>>
>> It's a pity. This would be very helpful.
>
>
> Unfortunately, that's just the way that SQL NULLs work..
>
>> With such situation, we
>> can't use the same prepared-statement to match NULL and non-NULL
>> values then, or I missed something?
>
>
> You are correct.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
>      joining column's datatypes do not match
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


Re: impossible to update rows specifying columns with NULL

From
Markus Schaber
Date:
Hi, Guillaume,
Hi, Dave,

Dave Cramer schrieb:
> Well, if you are really intent on using this you can set
> transform_null_equals=true in postgresql.conf

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.

Markus

--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

Re: impossible to update rows specifying columns with NULL

From
Guillaume Cottenceau
Date:
Markus Schaber <schabios 'at' logi-track.com> writes:

> Hi, Guillaume,
> Hi, Dave,
>
> Dave Cramer schrieb:
> > Well, if you are really intent on using this you can set
> > transform_null_equals=true in postgresql.conf
>
> 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.

Ok thanks.

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

--
Guillaume Cottenceau

Re: impossible to update rows specifying columns with NULL

From
Tom Lane
Date:
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

Re: impossible to update rows specifying columns with NULL

From
Guillaume Cottenceau
Date:
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

Re: impossible to update rows specifying columns with NULL

From
Andreas Ulbrich
Date:
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@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

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



Re: impossible to update rows specifying columns with NULL

From
Markus Schaber
Date:
Hi, Andreas,

Andreas Ulbrich schrieb:
> 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

Maybe you can encapsulate this in a plpgsql function, so you need to
bind the value only once.

Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org