Null query not working

[matyjo]

I have a weird bug that has appeared just recently. I created a user-password table (id, user_id, password). When a record is created only the id is entered initially. The user_id and password is added later (but start out as null).

When I do a query of all records I get user_id = null, password = null for those records. But if I do a query of only those with a user_id = null (SELECT * FROM users WHERE user_id = null
I get 0 records.

I have used this query within our intranet for a year and half and it has always worked. However, I just upgraded Postgresql to version 7.2.2-1 that comes with RedHat 8.0.

Any idea on how to solve this?

Matt

 

[uniware]

I think your usage is not right.
Judge whether a field is null or not should use:

SELECT * FROM users WHERE user_id IS NULL

 

[matyjo]

Thanks, that worked!

 

[rycamor]

Yes, it's a common misconception to treat NULL like a value, when in fact it is not a value. So logically speaking you cannot test for equality with null (WHERE whatever=NULL). SQL has an operator specifically for dealing with that, using IS NULL or IS NOT NULL.

I know, some SQL systems allow you to treat null like a value, but that usage is wrong.

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html