Nullable boolean check in WHERE clause in PostgreSQL

Question

The following query yields an error

select 1 where case null when null then true when true then true else false end

ERROR:  operator does not exist: text = boolean

What is the correct way to perform a nullable boolean value check in the WHERE clause?

Udate
Suprisingly this one works fine

create table d (id serial, v boolean);
insert into d(v) values(null),(true),(false);
select id from d where case v when null then true when v then true else true end;

 id
----
  1
  2
  3
(3 rows)

PS: actually this does not work either because the null value is casted to boolean and hence else is triggered instead of when null.

The whole expression makes no sense to me. What is the actual underlying problem you are trying to solve with that? If you want to treat null as true why not simply use where v or v is null? — user330315
– user330315, Commented Jun 30, 2021 at 6:51
I need to construct the query from within nodejs code with the value defined explisitly as null/true/false as shown in the original example. — yaugenka
– yaugenka, Commented Jun 30, 2021 at 6:58
where $1 is null or $1? with $1 being the parameter. Or better: only append the condition if the parameter is not null — user330315
– user330315, Commented Jun 30, 2021 at 7:00
Laurenz Albe's solution resolves the case approach but your alternative solution suits my case better. thank you! — yaugenka
– yaugenka, Commented Jun 30, 2021 at 7:46

Jeremy · Accepted Answer · 2021-06-30 00:16:19Z

1

You need to cast that first null to a boolean. Postgres is casting it to text, which results in your error.

select 1 where case null::boolean when null then true when true then true else false end;

That said, it might be easier to use DISTINCT FROM or NOT DISTINCT FROM depending on your actual use case.

answered Jun 30, 2021 at 0:16

Jeremy

6,77322 silver badges21 bronze badges

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

3 Comments

Nick Barnes Over a year ago

In addition to DISTINCT FROM, booleans have their own set of null-aware comparison operators; this specific case looks like it boils down to NULL IS NOT FALSE

yaugenka Over a year ago

if you cast null to boolean then when null condition will never trigger

Jeremy Over a year ago

@yaugenka It solves the ERROR you were getting, but it's unclear to me what you behavior you are actually looking for. Some sample data and expected results would be useful.

Laurenz Albe · Accepted Answer · 2021-06-30 07:46:02Z

0

Your error stems from the fact that PostgreSQL decides on the data type for the NULL without considering the WHEN clauses. Lacking other information, it goes for text. You can prevent that with an explicit type cast to boolean.

But your real problem is different. Look at this:

SELECT CASE NULL::boolean
           WHEN NULL THEN 'is null'
           WHEN TRUE THEN 'is true'
           ELSE 'is false'
       END;

   case   
══════════
 is false
(1 row)

The problem here is that if first WHEN will evaluate NULL = NULL, which results in NULL and not in TRUE.

Either treat nullness in the ELSE branch:

CASE NULL::boolean
   WHEN TRUE THEN 'is true'
   WHEN FALSE THEN 'is false'
   ELSE 'is null'
END

or use

CASE
   WHEN NULL IS NULL THEN 'is null'
   WHEN NULL IS TRUE THEN 'is true'
   WHEN NULL IS FALSE THEN 'is false'
END

edited Jun 30, 2021 at 7:46

answered Jun 30, 2021 at 2:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

4 Comments

yaugenka Over a year ago

if you cast null to boolean then when null condition will never trigger

Laurenz Albe Over a year ago

That's what I am saying.

yaugenka Over a year ago

so the solution is to use "case when null is null.." instead of "case null when null".

Laurenz Albe Over a year ago

Right. Or use the ELSE branch, as stated in my answer.

Collectives™ on Stack Overflow

Nullable boolean check in WHERE clause in PostgreSQL

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related