[postgresql-9.1] Postgresql SQL: How check boolean field with null and True,False Value?

In my database table I am having one boolean column. which have some transaction with will False, True and Null.

These are the cases i have tried:

Case:1

select * from table_name where
boolean_column is null;

works well. Give the result with all transaction having null value for that column.

Case:2

select *from table_name where boolean_column = False;

Works well. Gives result with all the transaction having False value for that column.

Case:3 This is requirement which does not works. I want all the transaction having value False and Null.

I have tried these.

i) select *from table_name where boolean_column is False or Null;

Which only gives the result for False it does not shows null records.

ii) select *from table_name where boolean_column is Null or False;

*Which only gives the result for null it does not shows records with False value. *

iii) select *from table_name where boolean_column is Null or boolean_column = False;

This is simply display all the transaction does not applied any condition at all.

How to resolve this issue. Any guidance appreciated.

Thanks in Advance.

Rgds, Anil.

This question is related to postgresql-9.1

The answer is


Resurrecting this to post the DISTINCT FROM option, which has been around since Postgres 8. The approach is similar to Brad Dre's answer. In your case, your select would be something like

SELECT *
  FROM table_name
 WHERE boolean_column IS DISTINCT FROM TRUE

There are 3 states for boolean in PG: true, false and unknown (null). Explained here: Postgres boolean datatype

Therefore you need only query for NOT TRUE:

SELECT * from table_name WHERE boolean_column IS NOT TRUE;

On PostgreSQL you can use:

SELECT * FROM table_name WHERE (boolean_column IS NULL OR NOT boolean_column)

  1. select *from table_name where boolean_column is False or Null;

    Is interpreted as "( boolean_column is False ) or (null)".

    It returns only rows where boolean_column is False as the second condition is always false.

  2. select *from table_name where boolean_column is Null or False;

    Same reason. Interpreted as "(boolean_column is Null) or (False)"

  3. select *from table_name where boolean_column is Null or boolean_column = False;

    This one is valid and returns 2 rows: false and null.

I just created the table to confirm. You might have typoed somewhere.