Re: SELECT WHERE NOT, is not working - Mailing list pgsql-general

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