The following bug has been logged online:

Bug reference: 1921
Logged by: BogDan Vatra
Email address: bog_dan_ro (AT) yahoo (DOT) com
PostgreSQL version: 8.0.3
Operating system: win xp pro
Description: NULL<>NULL ?!?!?!?!?!?!?
Details:

Please execute this query:

create table test (id serial, tt varchar(50));
insert into test (tt) values(NULL);
select tt from test where tt=NULL;

This should return 1 row but ...
An then try

select tt from test where tt<>NULL;

0 row(s) !!!

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

BogDan Vatra wrote:
This is not a bug. If you want find all values with that
field null you have to do:

select tt from test where tt is null;

And I can assure you this is what the standard say.


Regards
Gaetano Mendola


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Hi,

The SQL standard requires that null values do not equate any value,
including themselves.

Corrected syntax of your queries is shown below.

If I recall it correctly, this nehaviour was enforced in the 7.2->7.3 or
7.1->7.2 evolution.

If for instance a char(1) field called 'myfield' admits several values
rangeing from 'A' to 'I' and null, extracting all rows where char is neither
'C' or 'F' should imply the following where-clause

.... where myfield is null or myfield is not in ('C','F')

A cluase like "where myfield is not in ('C','F')" would not extract all rows
where myfield is null.

Hope this helps

Regards
J6M

----- Original Message -----
From: "BogDan Vatra" <bog_dan_ro (AT) yahoo (DOT) com>
To: <pgsql-bugs (AT) postgresql (DOT) org>
Sent: Thursday, September 29, 2005 10:38 AM
Subject: [BUGS] BUG #1921: NULL<>NULL ?!?!?!?!?!?!?


select tt from test where tt is null ;

select tt from test where tt is not null ;


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Thu, Sep 29, 2005 at 09:38:54AM +0100, BogDan Vatra wrote:
It is not a bug. NULL isn't a datum. It's a state of ignorance. If
I don't know what your birthday is and I don't know what your
coworker's birthday is, I also don't know whether they are equal, and
so the only right answer to "Is BogDan's birthday unequal to his
coworker's?" is "I don't know."

HTH

Cheers,
D
--
David Fetter david (AT) fetter (DOT) org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match