PostgreSQL BOOL_OR Aggregate Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL BOOL_OR aggregate function to return true if any values in a set are true.

Getting Started with the PostgreSQL BOOL_OR Aggregate Function #

The BOOL_OR is an aggregate function that accepts a set of boolean values and returns:

• true if any of the values are true.
• false if all of them are false.

The BOOL_OR function ignores NULL values in the calculation.

Here’s the syntax of the BOOL_OR aggregate function:

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

In this syntax, the BOOL_OR aggregate function examines all values in column1 and returns true if any of them is true and false if all of them are false.

Note: The BOOL_OR function only works if the data type of column1 is boolean. If not, you must explicitly cast the values in column1 to boolean; otherwise, PostgreSQL will issue an error.

Examples of BOOL_OR Aggregate Function #

Let’s explore some examples of using the BOOL_OR aggregate function.

Basic BOOL_OR Aggregate Function Example #

The following statement uses the BOOL_OR aggregate function to check if any product has a safety stock that equals zero:

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

Try it

Output:

 bool_or
---------
 tCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that there is at least one product with a safety stock of zero.

In this example:

• The expression safety_stock = 0 compares all values in the safety_stock column with zero, returning true or false.
• The BOOL_OR function takes all the results of the expression and returns true if any values are true or false if all values are false.

Using BOOL_OR aggregate function with GROUP BY Clause #

The following SELECT statement uses the BOOL_OR aggregate function to check which category has at least one product with safety stock zero:

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

Try it

Output:

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

In this example:

• The GROUP BY clause groups the rows in the products table by category_id.
• The BOOL_OR function checks the values of the safety_stock column in each group and returns true if any value in the group is true or false if all values are false.

To retrieve the category name instead of ID, you can join the products table with the categories table:

SELECT
  category_name,
  BOOL_OR (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_or
---------------+---------
 Accessories   | f
 Audio Systems | t
 Desktops      | t
 Laptops       | t
 Smartphones   | t
 Tablets       | f
 Televisions   | f
 Wearables     | fCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using BOOL_OR aggregate function with HAVING Clause #

The following statement uses the BOOL_OR aggregate function in the HAVING clause to retrieve product categories that have at least one product with safety stock zero:

SELECT
  category_name,
  BOOL_OR (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_or
---------------+---------
 Audio Systems | t
 Desktops      | t
 Laptops       | tCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PostgreSQL BOOL_OR aggregate function to return true if any value in a set is true or false if all values are false.

Quiz #