Re: select * from mytable where myfield=null;

Hi,
I agree with you when you say that '= null' isn't ANSI Standard.
But the trouble is that the jdbc driver does this error.
A query such as "select * from mytable where date=?" becomes "select *
from mytable where date=null"
with the jdbc driver when we put a null value in the preparedStatement
while it should become
"select * from mytable where date is null".

am I right ?

Thanks
Bruno


Barry Lind wrote:

> This isn't really a jdbc question and would probably better be
> addressed to pgsql-general.  The behavior you are now seeing is ANSI
> Standard behavior.  '= null' should always return false according to
> the standard.  In 7.2 this non-standard behavior was fixed.  You will
> see this behavior in all of the interfaces to postgres, not just jdbc.
>
> There is a parameter in the postgresql.conf file that will revert back
> to the old buggy behavior (transform_null_equals = true).
>
> thanks,
> --Barry
>
>
> blamouret wrote:
>
>> Hi,
>> I'm using Postgresql 7.2.1, with JBoss2.4.4. and jdk1.4
>>
>> Here is my table :
>> id   |  date                   |   value
>> -----+-------------------------+---------
>>   1 | 2002-05-10 10:00:00:+02 |       5
>>   2 |  (null)                 |      10
>> ...
>>
>> With the jdbc driver, this query doesn't any result :
>> Stmt = con.prepareStatement(select * from mytable where date=?)
>> Stmt.setNull(1,java.sql.Types.TIMESTAMP);
>> Stmt.executeQuery();
>>
>> I think the jdbc driver transform the query on "select * from mytable
>> where date=null" and not "select * from mytable where date is null".
>>
>> Il seems that select * from mytable where date=null was supported by
>> postgres before 7.0 version, but not with the 7.2.1
>>
>> How can i do ?
>> thanks
>> Bruno.
>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>
>