PostgreSQL BOOL_OR() Function
Summary: in this tutorial, you will learn about the PostgreSQLBOOL_OR() function to aggregate boolean values across rows within a group.
Introduction to the PostgreSQL BOOL_OR() function
TheBOOL_OR() is an aggregate function that allows you to aggregate boolean values across rows within a group.
Here’s the syntax of theBOOL_OR() function:
bool_or(expression)In this syntax, theexpression is the boolean expression to evaluate.
TheBOOL_OR() function returns true if at least one value in the group is true. If all values are false, the function returns false.
Please note that theBOOL_OR function ignoresNULLs within the group.
PostgreSQL BOOL_OR() function examples
Let’s explore some examples of using theBOOL_OR() 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 members ( member_idSERIAL PRIMARY KEY, member_nameVARCHAR(100)NOT NULL, active bool, team_idINT 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 members (member_name, team_id, active)VALUES('Alice',1, true),('Bob',2, true),('Charlie',1,null),('David',2, false),('Peter',3, false),('Joe',3,null)RETURNING*;Theteams table:
team_id | team_name---------+----------- 1 | Team A 2 | Team B 3 | Team C(3 rows)Themembers table:
member_id | member_name | active | team_id-----------+-------------+--------+--------- 1 | Alice | t | 1 2 | Bob | t | 2 3 | Charlie | null | 1 4 | David | f | 2 5 | Peter | f | 3 6 | Joe | null | 3(6 rows)2) Basic BOOL_OR() function example
The following example uses theBOOL_OR() function to test if there are any active members in themembers table:
SELECT BOOL_OR(active) active_member_existsFROM members;Output:
active_member_exists---------------------- t(1 row)TheBOOL_OR() function returns true indicating that themembers table has active members.
2) Using BOOL_OR() function with GROUP BY clause
The following example uses theBOOL_OR() function with theGROUP BY clause to check if there are any active members in each team:
SELECT team_name, BOOL_OR(active) active_member_existsFROM members INNER JOIN teamsUSING (team_id)GROUP BY team_name;Output:
team_name | active_member_exists-----------+---------------------- Team A | t Team B | t Team C | f(3 rows)The output indicates that teams A and B have active members whereas team C does not have any active members.
3) Using BOOL_OR() function in HAVING clause
The following example uses theBOOL_OR() function with theGROUP BY andHAVING clauses to retrieve teams that have active members:
SELECT team_name, BOOL_OR(active) active_member_existsFROM members INNER JOIN teams USING (team_id)GROUP BY team_nameHAVING BOOL_OR(active) = true;Output:
team_name | active_member_exists-----------+---------------------- Team A | t Team B | t(2 rows)Summary
- Use the
BOOL_OR()function to aggregate boolean values across rows within a group. - The
BOOL_OR()function ignores NULLs in the group.
Last updated on