SELECT WHERE NOT, is not working

Try:

select count(*) from resource where reengine <> 'X';

Alex Turner
NetEconomist

On Wed, 5 Jan 2005 13:51:58 -0800, MargaretGillon@chromalloy.com
<MargaretGillon@chromalloy.com> wrote:

On Wed, 2005-01-05 at 13:51 -0800, MargaretGillon@chromalloy.com wrote:

I have a small table in which I have a Character(1) field called reengine.
The field either has an "X" or is empty. This field does not have NULL
values. There are 27 records in the table, 25 are marked with an 'X' in
reengine.

I am querying the table from pgadmin v1.1.0 for windows.

When I write
select count(*) from resource where reengine = 'X';
the result is 25

when I write
select count(*) from resource where NOT (reengine = 'X');
the result is zero even though there are two records without the 'X'.

it really looks like you have NULLs where you say that the field is
empty. did you try:

select count(*) from resource where reengine is NULL 'X';

[...]
The records are being written with insert statements from a Windows2000
computer using ODBC.

maybe ODBC (or your client) maps empty strings to NULLs ?

gnari

On Wed, 2005-01-05 at 22:32 +0000, Ragnar Hafsta� wrote:

select count(*) from resource where reengine is NULL 'X';

typo. I meant of course:

select count(*) from resource where reengine is NULL;

gnari

MargaretGillon@chromalloy.com writes:

I have a small table in which I have a Character(1) field called reengine.
The field either has an "X" or is empty. This field does not have NULL
values. There are 27 records in the table, 25 are marked with an 'X' in
reengine.

When I write
select count(*) from resource where reengine = 'X';
the result is 25

when I write
select count(*) from resource where NOT (reengine = 'X');
the result is zero even though there are two records without the 'X'.

I don't think I believe your statement that those records don't have
NULL values.

regards, tom lane

Thanks, Ragnar,

You are right in what is happening. The code was supposed to be sending ''
but it is sending NULL instead. I see now -- in PostgreSQL to look for any
record without the 'X' I have to use a combined condition because a NULL is
not included in a != statement.

select count(*) from resource where reengine is NULL or NOT (reengine =
'X') ;

*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297

On Wednesday 05 January 2005 2:51 pm, MargaretGillon@chromalloy.com
wrote:

Thanks, Ragnar,

You are right in what is happening. The code was supposed to be
sending '' but it is sending NULL instead. I see now -- in
PostgreSQL to look for any record without the 'X' I have to use a
combined condition because a NULL is not included in a !=
statement.

select count(*) from resource where reengine is NULL or NOT
(reengine = 'X') ;

Or use the coalesce statement (picks the first non-null argument):
select count(*) from resource where coalesce(reengine, '') != 'X';

Cheers,
Steve