PostgreSQL bool_and() Function

The PostgreSQL bool_and() function is an aggregate function that returns the “logical AND” of all specified non-null Boolean values ​​in a group. That is, this function returns true if all non-null input values ​​are true, and otherwise returns false.

bool_and() Syntax

Here is the syntax of the PostgreSQL bool_and() function:

bool_and(expr) -> bool

Typically, we use the bool_and() function like:

SELECT bool_and(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression.

Return value

The PostgreSQL bool_and() function returns a boolean value. This function returns true if all non-null input values ​​are true, otherwise returns false.

Note that the bool_and() function only handles non-null values. That is, null values ​​are ignored by the bool_and() function.

bool_and() Examples

To demonstrate usages of the PostgreSQL bool_and() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 'Tim' name, 'Football' club, true joined
UNION
SELECT 'Tim' name, 'Baseball' club, true joined
UNION
SELECT 'Tom' name, 'Football' club, true joined
UNION
SELECT 'Tom' name, 'Baseball' club, null joined
UNION
SELECT 'Jim' name, 'Football' club, true joined
UNION
SELECT 'Jim' name, 'Baseball' club, false joined;

 name |   club   | joined
------+----------+--------
 Tim  | Football | t
 Tim  | Baseball | t
 Tom  | Football | t
 Tom  | Baseball | <null>
 Jim  | Football | t
 Jim  | Baseball | f
(6 rows)

Here, we have some rows about whether the user joined a club or not. The name column is the name of the user, the club column is the name of the club, and the joined column is a boolean value indicating whether the club has joined.

Suppose, to determine whether a user has joined all clubs, you can use the GROUP BY clause to group all rows by name and use the bool_and() function to operate the values ​​of the joined column. The following statement completes this requirement:

SELECT
    t.name,
    bool_and(t.joined) joined_all
FROM
    (
    SELECT 'Tim' name, 'Football' club, true joined
    UNION
    SELECT 'Tim' name, 'Baseball' club, true joined
    UNION
    SELECT 'Tom' name, 'Football' club, true joined
    UNION
    SELECT 'Tom' name, 'Baseball' club, null joined
    UNION
    SELECT 'Jim' name, 'Football' club, true joined
    UNION
    SELECT 'Jim' name, 'Baseball' club, false joined
    ) t
GROUP BY t.name;

 name | joined_all
------+------------
 Tom  | t
 Tim  | t
 Jim  | f
(3 rows)

Here,

• For Tim, his joined column has two true, so bool_and(t.joined) returned true meaning he joined all clubs.
• For Tom, his joined column has one true and one null, but since bool_and() only handles non-null input values, so bool_and(t.joined) returned true.
• For Jim, his joined column has one true and one false, so bool_and(t.joined) returned false.