PostgreSQL BOOL_AND Aggregate Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL BOOL_AND() aggregate function to return true if all values in a set of values are true.

Getting Started with the PostgreSQL BOOL_AND aggregate function #

In PostgreSQL, the BOOL_AND is an aggregate function that takes a set of boolean values and returns a single value.

If all values are true, then BOOL_AND returns true. If any value is false, the BOOL_AND aggregate function returns false.

Here’s the syntax of the BOOL_AND aggregate function:

SELECT
  BOOL_AND (column1)
FROM
  table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the BOOL_AND function takes all boolean values in column1 and returns true if all values are true or false if any of the values are false.

The data type of column1 must be boolean. If it is not, then you need to cast the values to boolean explicitly.

Note that the BOOL_AND function ignores NULLs.

PostgreSQL BOOL_AND Aggregate Function Examples #

The following statement uses the BOOL_AND aggregate function to check if all products have safety stock greater than zero:

SELECT
  BOOL_AND (safety_stock > 0)
FROM
  products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 bool_and
----------
 fCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that one or more products have a safety stock of zero.

In this example:

• The expression safety_stock > 0 evaluates all values in the safety_stock column. The result can be true or false.
• The BOOL_AND() function takes all the results and returns true if all of them are true or false otherwise.

Using BOOL_AND aggregate function with GROUP BY Clause #

The following example uses the BOOL_AND aggregate function to check which category has safety stock greater than zero:

SELECT 
     category_id, 
     BOOL_AND(safety_stock > 0)
FROM products
GROUP BY category_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_id | bool_and
-------------+----------
          11 | f
           9 | f
           3 | f
           5 | t
           4 | t
           6 | t
          12 | f
           8 | tCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

• The GROUP BY clause groups the rows in the products table into groups by category_id.
• The BOOL_AND takes the values of the safety_stock in each group and returns a single value for each.

To get the category name instead of the id, you can join the products table with the categories table:

SELECT
  category_name,
  BOOL_AND (safety_stock > 0)
FROM
  products
  JOIN categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  category_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_name | bool_and
---------------+----------
 Accessories   | t
 Audio Systems | f
 Desktops      | f
 Laptops       | f
 Smartphones   | f
 Tablets       | t
 Televisions   | t
 Wearables     | tCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using BOOL_AND aggregate function with HAVING BY Clause #

The following example uses the BOOL_AND aggregate function in the HAVING clause to retrieve product categories that have safety stock greater than zero:

SELECT
  category_name,
  BOOL_AND (safety_stock > 0)
FROM
  products
  JOIN categories USING (category_id)
GROUP BY
  category_name
HAVING
  BOOL_AND (safety_stock > 0) = true
ORDER BY
  category_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_name | bool_and
---------------+----------
 Accessories   | t
 Tablets       | t
 Televisions   | t
 Wearables     | tCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the BOOL_AND aggregate function to return true if all values in a set of values are true.

Quiz #