PostgreSQL – Boolean Check on Fields Except When All Have Same Value

postgresqlpostgresql-9.4

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

Best Answer

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

Related Question