Postgresql SQL How check boolean field with null and True False Value

Question

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

User · Answer

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

User · Answer

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  select  from table name where boolean column is Null or False   Same reason  Interpreted as   boolean column is Null  or  False   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

User · Answer

I m not expert enough in the inner workings of Postgres to know why your query with the double condition in the WHERE clause be not working   But one way to get around this would be to use a UNION of the two queries which you know do work   SELECT   FROM table name WHERE boolean column IS NULL UNION SELECT   FROM table name WHERE boolean column   FALSE   You could also try using COALESCE   SELECT   FROM table name WHERE COALESCE boolean column  FALSE    FALSE   This second query will replace all NULL values with FALSE and then compare against FALSE in the WHERE condition

User · Answer

On PostgreSQL you can use   SELECT   FROM table name WHERE  boolean column IS NULL OR NOT boolean column

User · Answer

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

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

Examples related to postgresql-9.1