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

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

• Extra point: it does not matter if you search for true or false. The answer (query and result) are the same ;) – ypercubeᵀᴹ Jun 2 '15 at 17:50

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().

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)

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)