[GENERAL] SELECT WHERE NOT, is not working
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: [GENERAL] 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, [EMAIL PROTECTED] [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SELECT WHERE NOT, is not working
On Wed, 2005-01-05 at 13:51 -0800, [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT WHERE NOT, is not working
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SELECT WHERE NOT, is not working
On Wed, 2005-01-05 at 22:32 +, 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SELECT WHERE NOT, is not working
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SELECT WHERE NOT, is not working
On Wednesday 05 January 2005 2:51 pm, [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
