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 rows with 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 rows having null value for that column.

Case 2:

select *from table_name where boolean_column = False;

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

Case 3:

This is requirement which does not works. I want all the rows having values 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 simply displays all the rows, it does not apply any condition at all.

What query can solve this problem?

The or in boolean_column is False or Null does not apply to boolean_column being null but whether boolean_column is False is TRUE OR Null is TRUE. Obviously not what your intending. — user1032531
– user1032531, Commented Oct 10, 2022 at 0:30

Brad Dre · Accepted Answer · 2017-09-28 16:35:46Z

189

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;

answered Sep 28, 2017 at 16:35

Brad Dre

3,8962 gold badges23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2023-11-30 14:32:59Z

92

Use the IS NOT TRUE operator:

SELECT * FROM table_name WHERE boolean_column IS NOT TRUE;

This will match values which are either false or NULL. Another option 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 third query will replace all NULL values with FALSE and then compare against FALSE in the WHERE condition.

edited Nov 30, 2023 at 14:32

answered Oct 28, 2015 at 4:57

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

Anil Kesariya Over a year ago

My Query is not single line. I have more than 20 Conditions Query is about 1 page. do I have to Repeat the same query again?

Chris Over a year ago

This is a poor answer - there is no need to run two queries.

Dmitry Ukolov · Accepted Answer · 2017-01-26 20:05:47Z

14

On PostgreSQL you can use:

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

answered Jan 26, 2017 at 20:05

Dmitry Ukolov

6787 silver badges10 bronze badges

Comments

shortstuffsushi · Accepted Answer · 2020-08-07 15:40:09Z

10

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

answered Aug 7, 2020 at 15:40

shortstuffsushi

2,32920 silver badges35 bronze badges

Comments

Paul Roub · Accepted Answer · 2017-08-25 13:55:15Z

8

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.

edited Aug 25, 2017 at 13:55

Paul Roub

36.5k27 gold badges88 silver badges95 bronze badges

answered Aug 25, 2017 at 13:52

Chris Chris

3881 gold badge3 silver badges10 bronze badges

Comments

Chris · Accepted Answer · 2022-12-21 08:25:47Z

@Brad Dre's answer is the least code and same efficiency as this one, but potentially harder to understand for future developers. Chris's third and Dmitry's answers already covered this, but I wanted to add an answer with a bit more background.

In the OP's words:

"I want all the transactions having value False and Null."

Re-worded in almost-sql:

"I want all the transactions where the value is False or the value is Null."

Re-worded into SQL:

SELECT * 
FROM transactions
WHERE 
    value IS FALSE 
    OR value IS NULL;

Often writing the code to be as close as natural language as possible will give future developers hints on the intention of the initial developer. The issue with @Brad Dre's anwser is a future developer might not think of the NULL case in Brad's code, or might have thought Brad forgot about it.

Collectives™ on Stack Overflow

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

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

Comments

Comments

Comments

Comments

Related