Introduction to the PostgreSQL BOOL_AND() function

TheBOOL_AND() is an aggregate function that allows you to aggregate boolean values across rows within a group.

The following shows the syntax of theBOOL_AND() function:

BOOL_AND(expression)

In this syntax:

• expression: This is a boolean expression to evaluate.

TheBOOL_AND() function returns true if all values in the group are true, or false otherwise.

It’s important to note that theBOOL_AND() function ignoresNULLs within the group.

PostgreSQL BOOL_AND() function examples

Let’s explore some examples of using theBOOL_AND() function.

1) Setting up sample tables

First,create tables calledteams andmembers:

CREATE TABLE teams (    team_idSERIAL PRIMARY KEY,    team_nameVARCHAR(100)NOT NULL);CREATE TABLE projects(    project_idSERIAL PRIMARY KEY,    name VARCHAR(255)NOT NULL,    active BOOL,    team_idINT NOT NULL REFERENCES teams(team_id));

Second,insert rows into the tables:

INSERT INTO teams (team_name)VALUES('Team A'),('Team B'),('Team C')RETURNING*;INSERT INTO projects(name, active, team_id)VALUES('Intranet', false,1),('AI Chatbot', true,1),('Robot', true,2),('RPA', true,2),('Data Analytics', true,3),('BI',NULL,3)RETURNING*;

Theteams table:

team_id | team_name---------+-----------       1 | Team A       2 | Team B       3 | Team C(3 rows)

Themembers table:

project_id |      name      | active | team_id------------+----------------+--------+---------          1 | Intranet       | f      |       1          2 | AI Chatbot     | t      |       1          3 | Robot          | t      |       2          4 | RPA            | t      |       2          5 | Data Analytics | t      |       3          6 | BI             | null   |       3(6 rows)

2) Basic BOOL_AND() function example

The following example uses theBOOL_AND() function to test if all projects are active in theprojects table:

SELECT  BOOL_AND(active)FROM  projects;

Output:

bool_and---------- f(1 row)

TheBOOL_AND() function returns true indicating that there are inactive projects in theprojects table.

2) Using BOOL_AND() function with GROUP BY clause

The following example uses theBOOL_AND() function with theGROUP BY clause to check if there are active projects in each team:

SELECT  team_name,  BOOL_AND(active) active_projectsFROM  projects  INNER JOIN teamsUSING (team_id)GROUP BY  team_name;

Output:

team_name | active_projects-----------+----------------- Team A    | f Team B    | t Team C    | t(3 rows)

The output indicates that teams B and C have projects that are active (or NULL) whereas team C has inactive projects.

3) Using BOOL_AND() function in HAVING clause

The following example uses theBOOL_AND() function with theGROUP BY andHAVING clauses to retrieve teams that have active projects:

SELECT  team_name,  BOOL_AND(active) active_projectsFROM  projects  INNER JOIN teams USING (team_id)GROUP BY  team_nameHAVING  BOOL_AND(active) = true;

Output:

team_name | active_projects-----------+----------------- Team B    | t Team C    | t(2 rows)

Summary

• Use theBOOL_AND() function to aggregate boolean values across rows, which returns true if all values are true or false otherwise.
• TheBOOL_AND() function ignores NULLs in the group.

Last updated onMarch 22, 2024

On this page

• Introduction to the PostgreSQL BOOL_AND() function
• PostgreSQL BOOL_AND() function examples
• Summary