Boolean check on fields returning any of them except if all have same value

Question

Assuming we have 4 boolean fields a,b,c,d

How can we return any of the fields that are true, but not nothing when all 4 are true?

where a=true or b=true or c=true or d=true

will return a row where even all 4 fields are set tot true. How can I exclude this row from the query?

Same would apply if searched value is false

Answer 1

You can use something like this:

where (true in (a,b,c,d)
       and (a,b,c,d) <> (true,true,true,true));

Which can be simplified (suggested by ypercubeᵀᴹ) to:

where true in (a,b,c,d) and false in (a,b,c,d)

Note that it will not handle NULL values properly. If you want that as well, you will need to use coalesce().

Answer 2

This is a direct translation of "not when all 4 are true" into SQL:

where NOT(a=true AND b=true AND c=true AND d=true)

Edit:

@a_horse_with_no_name shortened it further to

where NOT(a AND b AND c AND d)

Edit2:

This doesn't work correctly (will return a row when all four columns are false), so mixing with @a_horse_with_no_name's answer:

where NOT(a AND b AND c AND d) AND (a OR b OR c OR d)

Answer 3

Another way using Postgres aggregate functions that I thought would work but actually doesn't!
(don't try this):

where bool_or(a, b, c, d) and not bool_and(a, b, c, d)

This will work but is much more verbose than the other answers:

where exists 
     (select 1
      from (values (a), (b), (c), (d)) as b(i)
      having bool_or(i) and not bool_and(i) 
     ) 

(somewhat simplified):

where (select bool_or(i) and not bool_and(i) 
       from (values (a), (b), (c), (d)) as b(i)
      ) 

---

This however, works. It's based on FALSE < TRUE ordering:

where greatest(a,b,c,d) = true and least(a,b,c,d) = false

or:

where greatest(a,b,c,d) and not least(a,b,c,d) 

or just:

where greatest(a,b,c,d) > least(a,b,c,d)