Menu
I would like to ask you how in PostgreSQL can you check if one of boolean values in table's column is true using select or aggregate function?
968
An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*) , aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
PostgreSQL provides the standard SQL type boolean ; see Table 8.19. The boolean type can have several states: “true”, “false”, and a third state, “unknown”, which is represented by the SQL null value.
Like most other relational database products, PostgreSQL supports aggregate functions. An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count , sum , avg (average), max (maximum) and min (minimum) over a set of rows.
PostgreSQL supports a single Boolean data type: BOOLEAN that can have three values: true , false and NULL . PostgreSQL uses one byte for storing a boolean value in the database. The BOOLEAN can be abbreviated as BOOL . In standard SQL, a Boolean value can be TRUE , FALSE , or NULL .
There are few boolean specific aggregate functions you may use: bool_and, bool_or, every.
In your particular case you need bool_or aggregate.
The query is as simple as this:
SELECT bool_or(my_column) FROM my_table
You can't use SUM(DATA) but you could cast the value (see below) to int (0=false, 1=true). However it may be more efficient to use EXISTS(...), specially if you are not interested in the number of TRUE values.
create table test(data boolean);
insert into test values(true), (false);
select sum(cast(data as int)) from test;
select exists(select * from test where data);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
