PostgreSQL bool_or(): TRUE, FALSE and NULL Examples

PostgreSQL bool_or() is an aggregate function that returns TRUE if any non-NULL input is TRUE. It returns FALSE when there is at least one non-NULL input and all such inputs are FALSE. When there are no non-NULL inputs, it returns SQL NULL, not FALSE.

Use it for questions such as “Did any order fail?” or “Does any row in this group meet the condition?”

Syntax

bool_or(boolean_expression)
bool_or(boolean_expression) FILTER (WHERE condition)

The input must evaluate to a Boolean value. bool_or() ignores SQL NULL inputs and produces a Boolean result, which can be TRUE, FALSE or NULL.

Examples

Return TRUE when any row matches

SELECT bool_or(is_active) AS any_active
FROM (
    VALUES (FALSE), (TRUE), (NULL::boolean)
) AS flags(is_active);

any_active
----------
t

One TRUE is sufficient. The NULL value is ignored.

Use bool_or() with GROUP BY

WITH flags(team, completed) AS (
    VALUES
        ('Alpha', TRUE),
        ('Alpha', FALSE),
        ('Beta', FALSE),
        ('Beta', NULL::boolean),
        ('Gamma', NULL::boolean)
)
SELECT
    team,
    bool_or(completed) AS any_completed,
    bool_and(completed) AS all_completed
FROM flags
GROUP BY team
ORDER BY team;

team   any_completed  all_completed
-----  -------------  -------------
Alpha  t              f
Beta   f              f
Gamma  NULL           NULL

bool_or() tests whether at least one known value is true; bool_and() tests whether all known values are true. The Gamma group contains only NULL, so both aggregates return SQL NULL.

All NULL values and empty results

SELECT
    bool_or(flag) IS NULL AS no_known_values,
    coalesce(bool_or(flag), FALSE) AS default_false
FROM (
    SELECT NULL::boolean AS flag
) AS flags;

no_known_values  default_false
---------------  -------------
t                f

coalesce(bool_or(flag), FALSE) converts an unknown result to false only if that is the intended business rule. Do not use this fallback when “no observations” needs to remain distinct from “observed, but false.”

An aggregate query over zero selected rows also produces SQL NULL:

SELECT bool_or(flag) IS NULL AS empty_result_is_null
FROM (
    SELECT TRUE AS flag WHERE FALSE
) AS no_rows;

empty_result_is_null
--------------------
t

Check a condition instead of a Boolean column

WITH orders(customer, status) AS (
    VALUES
        ('Ana', 'paid'),
        ('Ana', 'failed'),
        ('Ben', 'paid'),
        ('Ben', 'pending')
)
SELECT
    customer,
    bool_or(status = 'failed') AS has_failure
FROM orders
GROUP BY customer
ORDER BY customer;

customer  has_failure
--------  -----------
Ana       t
Ben       f

The comparison status = 'failed' is itself Boolean. If status is SQL NULL, the comparison is also NULL and is ignored by the aggregate.

Frequently asked questions

Does bool_or() return FALSE for an empty table?

No. It returns SQL NULL when there are no non-NULL values. Use COALESCE(bool_or(expression), FALSE) only when false is an appropriate default.

How is bool_or() different from bool_and()?

bool_or() answers “is any known value true?” while bool_and() answers “are all known values true?” Both ignore NULL and return NULL when no known values exist.

What is the difference between bool_or() and bit_or()?

bool_or() aggregates Boolean values with logical OR. bit_or() performs a bitwise OR on integer or bit-string values; it is not a drop-in Boolean replacement.

See the official PostgreSQL aggregate-function documentation and PostgreSQL Boolean data type.