Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT WHERE NOT, is not working

SELECT WHERE NOT, is not working

From
MargaretGillon@chromalloy.com
Date:
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'.

I have also tried:
select * FROM RESOURCE where reengine > 'X';
select * FROM RESOURCE where reengine < 'X';
select * FROM RESOURCE where reengine = '';

but nothing works except the " reengine = 'X' "

I have other fields in this table that I can query with the "NOT" and the
query works.

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

Can someone explain this. I tried looking in archives but didn't find
anything.

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

Re: SELECT WHERE NOT, is not working

From
Alex Turner
Date:
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:
> 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'.
>
> I have also tried:
> select * FROM RESOURCE where reengine > 'X';
> select * FROM RESOURCE where reengine < 'X';
> select * FROM RESOURCE where reengine = '';
>
> but nothing works except the " reengine = 'X' "
>
> I have other fields in this table that I can query with the "NOT" and the
> query works.
>
> The records are being written with insert statements from a Windows2000
> computer using ODBC.
>
> Can someone explain this. I tried looking in archives but didn't find
> anything.
>
> *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
> ***
> Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Re: SELECT WHERE NOT, is not working

From
Ragnar Hafstað
Date:
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

Re: SELECT WHERE NOT, is not working

From
Ragnar Hafstað
Date:
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

Re: SELECT WHERE NOT, is not working

From
MargaretGillon@chromalloy.com
Date:
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

Re: SELECT WHERE NOT, is not working

From
Tom Lane
Date:
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

Re: SELECT WHERE NOT, is not working

From
Steve Crawford
Date:
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