The following bug has been logged online:

Bug reference: 2417
Logged by: James
Email address: james@mercstudio.com
PostgreSQL version: 8.1
Operating system: Microsoft Windows XP Professional
Description: bug for finding string in column
Details:

hi,

i've encounter bug where when a field is empty / null (type varchar(255) ),
when i use the sql query <> 'myvalue', it seems to be false all the time.


example:
select * from user_profile where acc_lock <> 'Y'

where the acc_lock field for all rows were empty...

2nd, hopefully that postgresql can be flexible to accept empty '' as null
search as well...

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, 2 May 2006, James wrote:

>
> The following bug has been logged online:
>
> Bug reference: 2417
> Logged by: James
> Email address: james@mercstudio.com
> PostgreSQL version: 8.1
> Operating system: Microsoft Windows XP Professional
> Description: bug for finding string in column
> Details:
>
> hi,
>
> i've encounter bug where when a field is empty / null (type varchar(255) ),
> when i use the sql query <> 'myvalue', it seems to be false all the time.

If it's null, it's actually returning unknown (not true or false) and
as far as we know that's not a bug because that's how SQL defines the
comparison operators.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Tue, May 02, 2006 at 10:34:12 +0000,
James <james@mercstudio.com> wrote:
>
> i've encounter bug where when a field is empty / null (type varchar(255) ),
> when i use the sql query <> 'myvalue', it seems to be false all the time.

Empty and NULL are not the same thing. NULL <> to any value evaluates
to NULL which will not satisfy a WHERE clause. This is according to the
SQL (i.e. it's not a bug).

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