Result of NULL != value in an SQL query (postgres and rails3)

There is an IS DISTINCT FROM comparison operator in PostgreSQL:

Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL. When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM constructs:

expression IS DISTINCT FROM expressionexpression IS NOT DISTINCT FROM expression

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, IS NOT DISTINCT FROM is identical to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null. Thus, these constructs effectively act as though null were a normal data value, rather than "unknown".

For example, given your sample data:

=> select * from orders where state is distinct from 'success'; id |  state  ----+---------  1 |   3 | failure(2 rows)

So you could say this:

Order.where('orders.state is distinct from ?', 'success').pluck(:id)

Note that I also switched to pluck rather than your map(&:id), that will send this SQL to the database:

select id from orders where orders.state is distinct from 'success'

rather than select orders.* ... with client-side filter to extract the ids.

This is part of the SQL standard, where a comparison between two values involving at least one NULL returns neither true, nor false, but unknown.

From http://www-cs-students.stanford.edu/~wlam/compsci/sqlnulls

• A boolean comparison between two values involving a NULL returns neither true nor false, but unknown in SQL's three-valued logic. [3] For example, neither NULL equals NULL nor NULL not-equals NULL is true. Testing whether a value is NULL requires an expression such as IS NULL or IS NOT NULL.
• An SQL query selects only values whose WHERE expression evaluates to true, and groups whose HAVING clause evaluates to true.

One solution is to use COALESCE:

Order.where('COALESCE(orders.state,"NIL") != ?', 'success').map(&:id)

If orders.state is NULL, it will replace it with 'NIL', which will then return true or false from the comparison.

You can of course use an additional condition:

Order.where('orders.state is null OR orders.state != ?', 'success').map(&:id)