True or False every which way

PostgreSQL has aggregate functions called bool_and and bool_or which it's had for as far back as I can remember. What do they do? given rows of premises (things that resolve to booleans), bool_and will return true if all of the premises are true. Similarly bool_or will return true if any of the premises in the set of rows is true. What if however your boolean expressions are not in rows, but instead passed in as a sequence of arbitrary statements of questionable fact. We want a function like bool_or or bool_and that takes an arbitrary number of boolean arguments. Are there functions that fit the bill. Indeed there are, but they don't scream out and say I work with booleans because they fit into a class of function we discussed in The wonders of Any Element and that also happen to be variadic functions. These are none other than greatest and least and they are old timer functions that you can find in most versions of PostgreSQL. We'll demonstrate how to use all 4 with booleans in this article. It must be said that greatest and least are much more useful when applied to other data types like dates and numbers, but we were amused at the parallel with booleans.

Side note: we've started to write our book on PostgreSQL that will be published by O'Reilly. We hope to finish this book within the next 5-7 months but have preliminary e-Book drafts before then for early purchasers to scan. The focus of the book will be PostgreSQL 9.1 with some highlights the upcoming PostgreSQL 9.2. Of course oldie but goodie topics are in the book too. It's a thrill to be writing again.

Drafting our data

Lets create a table of people and capture their date of birth, eye color, and gender. Then we'll use the various boolean functions to make not so interesting conclusions about these people.

CREATE TABLE people(p_name varchar(50) primary key, dob date, gender char(1), eye_color varchar(20)); 
INSERT INTO people(p_name,dob,gender,eye_color)
VALUES ('Blue Boy', '1770-10-15','M', 'blue')
    , ('Mahatma Gandi', '1869-10-02', 'M', 'brown')
    , ('Amelia Earhart', '1897-07-24', 'F', 'blue')
    , ('Edwin Land', '1909-05-07', 'M', NULL);

bool_and and bool_or in action

-- do any of our people have blue eyes or over 110 years by gender?
SELECT gender, bool_and(eye_color = 'blue') As all_blue
    , bool_or(eye_color = 'blue') As any_blue
    , bool_and(age(dob) > '110 years'::interval) As all_over_110
    , bool_or(age(dob) > '110 years'::interval) As any_over_110
  FROM people
  GROUP BY gender;
gender | all_blue | any_blue | all_over_110 | any_over_110
F      | t        | t        | t            | t
M      | f        | t        | f            | t

Positing compound statements with greatest and least

It must be noted that instead of greatest and least, you can use AND / OR. There are pros and cons to each. AND / OR are a bit more verbose but have the benefit of being able to be short-circuited. Sadly greatest/least would we think require all expressions to be resolved and also since it's generic, probably doesn't know that it can stop without evaluating all expressions. If your expressions are simple and not time consuming to process, then the brevity of the statement out-weighs the tediousness of AND/OR.

-- For each answer the question
-- blue, female, and has an a in the name
SELECT p_name
    , greatest(eye_color = 'blue', gender = 'F', p_name LIKE '%a%') As any_condition
    , least(eye_color = 'blue', gender = 'F', p_name LIKE '%a%') As all_conditions
FROM people 
ORDER BY p_name;

     p_name     | any_condition | all_conditions
 Amelia Earhart | t             | t
 Blue Boy       | t             | f
 Edwin Land     | t             | f
 Mahatma Gandi  | t             | f

bool_and, bool_or, greatest and least in one breath

One caveat is that bool_and and bool_or similar to other aggregates discard NULLs unless if that is all there is. Similar behavior with greatest and least. This is sometimes desirable and sometimes not.

-- Only caution bool aggs and also least/greatest discard unknowns
-- so if we don't know the eye_color and all known conditions are met
-- , the overall condition is considered met
SELECT eye_color, bool_or(least(eye_color = 'blue',  p_name LIKE '%a%')) As any_have_all,
  bool_and(greatest(eye_color = 'blue', p_name LIKE '%a%')) AS all_have_any
FROM people
group by eye_color;

 eye_color | any_condition | all_conditions
           | t             | t
 blue      | t             | t
 brown     | f             | t