Maybe this is just my misunderstanding of how this should work, but
I've got a table with a char(6) field named member_status. When I
select "member_status != '*'" or "member_status <> '*'", I get 14856
rows. However, if I select "member_status is null or member_status !=
'*'", I get 20308 rows.

It was my understanding that a NULL value would be not-equal to any
non-null value. Am I wrong about this? I'm seing this behavior on
7.3.2, on 7.4.5 and on 8.0.3.

On my 8.0.3 database, I created a test table with just a single char(6)
column and inserted 2 rows; 1 with a null value and one with a '*'.
When selecting != '*', I get zero rows, and ='*' returns 1 row. It
seems logical to me, to expect that != and = would return complimentary
results. What am I missing?

Douglas Toltzman, Oak Street Software, Inc.
voice: 910-526-5938
http://www.oakstreetsoftware.com/


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

Douglas Toltzman <doug (AT) oakstreetsoftware (DOT) com> writes:
Yes. A NULL is neither equal nor unequal to anything else.

You can use IS DISTINCT FROM to get the behavior you are looking for,
but it might be better to rethink how you are using NULL.

regards, tom lane

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