PostgreSQL bool_or() Function

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

bool_or() Syntax

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

bool_or(expr) -> bool

Typically, we use the bool_or() function like:

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

Parameters

expr

Required. A column name or expression.

Return value

The PostgreSQL bool_or() function returns a boolean value. This function returns true if any of the non-null input values ​​is true, otherwise it returns false.

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

bool_or() Examples

To demonstrate usages of the PostgreSQL bool_or() 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, false joined
UNION
SELECT 'Tom' name, 'Football' club, true joined
UNION
SELECT 'Tom' name, 'Baseball' club, null joined
UNION
SELECT 'Jim' name, 'Football' club, false joined
UNION
SELECT 'Jim' name, 'Baseball' club, false joined;

 name |   club   | joined
------+----------+--------
 Tim  | Football | t
 Tim  | Baseball | f
 Tom  | Football | t
 Tom  | Baseball | <null>
 Jim  | Baseball | f
 Jim  | Football | 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 at least one club, you can use the GROUP BY clause to group all rows by name and use the bool_or() function to operate the values ​​of the joined column. The following statement completes this requirement:

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

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

here,

• For Tim, his joined column has true, so bool_or(t.joined) returned true.
• For Tom, his joined column has one true and one null, so bool_or(t.joined) returned true.
• For Jim, his joined column has two false, so bool_or(t.joined) returned false.