What is the difference between bool_or and bool_and

I would like to know the difference between bool_or and bool_and in PostgreSQL.

For example, what is the difference between

select bool_or(start_date > CURRENT_DATE) AS isActive

and

select bool_and(start_date > CURRENT_DATE) AS isActive

Thanks!

1

4 Answers

Both are aggregate functions and operate on multiple rows, not just a single expression.

Consider the following query:

with sample_data (id, start_date) as ( values (1, current_date - 1), (2, current_date + 1)
)
select bool_or(start_date > CURRENT_DATE) AS or_result, bool_and(start_date > CURRENT_DATE) AS and_result
from sample_data; 

The above returns:

or_result | and_result
----------+----------
true | false 

sample_data contains two rows, one fulfills the condition start_date > CURRENT_DATE the other doesn't. bool_and() thus returns false because not all of the values fulfill the condition. bool_or() returns true because for at least one row the condition is true.

The result of bool_and() for the above query is equivalent to:

select (select start_date > current_date from sample_data where id = 1) AND (select start_date > current_date from sample_data where id = 2);

Now imagine you would need to write that for a million rows - that's what bool_and() and bool_or() are for.

From the postgresql documentation :

bool_and(expression) : true if all input values are true, otherwise false

bool_or(expression) : true if at least one input value is true, otherwise false

2

bool_and returns true if all the values are matching on the contrary,
bool_or returns true if single value is matched.

Find more details here. Hope this helps!

Edit 1

In your query conditions in the block states that it will return true/false from single condition (so both the conditions will work as same, as these statements works on multiple rows and not the single statement), so both the expressions will work based on conditions satisfied.

I constructed a very intuitive demo.

WITH cte ( start_date
) AS ( VALUES ('2022-11-01'::date), ('2022-11-02'), ('2022-11-03'), ('2022-11-04'))
SELECT start_date, bool_and(start_date > CURRENT_DATE) OVER w, bool_or(start_date > CURRENT_DATE) OVER w
FROM cte WINDOW w AS (ORDER BY start_date ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING);

as today is 2022-11-01(YYYY-MM-DD).
it returns:

 start_date | bool_and | bool_or
------------+----------+--------- 2022-11-01 | f | f 2022-11-02 | f | t 2022-11-03 | t | t 2022-11-04 | t | t
(4 rows)

the window frame is current row and the next following rows order by start_date asc order.

So bool_and will check if in the frame (window clause), both row meet the condition (start_date > CURRENT_DATE) or not. if meet then true else false.
It will only evaulate non-null input values.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.