PostgreSQL every() Function

The PostgreSQL every() function is an aggregate function that returns the result of 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 false otherwise.

The PostgreSQL every() function is the SQL standard that is equivalent to the bool_and() function.

every() Syntax

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

every(expr) -> bool

Typically, we use the every() function like:

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

Parameters

expr

Required. A column name or expression.

Return value

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

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

every() Examples

To demonstrate usages of the PostgreSQL every() 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 every() function to operate the values ​​of the joined column. The following statement completes this requirement:

SELECT
    t.name,
    every(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 every(t.joined) returned true meaning he joined all clubs.
• For Tom, his joined column has one true and one null, but since every() only handles non-null input values, so every(t.joined) returned true.
• For Jim, his joined column has one true and one false, so every(t.joined) returned false.