|
Home > Archive > PostgreSQL Bugs > September 2005 > char field <> or != to a value does not select where field is null ???
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
char field <> or != to a value does not select where field is null ???
|
|
| Douglas Toltzman 2005-09-22, 1:24 pm |
| 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
| |
| Tom Lane 2005-09-22, 1:24 pm |
| Douglas Toltzman < doug@oakstreetsoftwa
re.com> writes:
> It was my understanding that a NULL value would be not-equal to any
> non-null value. Am I wrong about this?
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
|
|
|
|
|