Обсуждение: impossible to update rows specifying columns with NULL value?
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
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
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
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
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
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
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
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
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
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
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