CODEWITHSUNDEEP
sqlpostgresql
gohohgle
gohohgle

Reputation: 379

Select or boolean aggregate function in PostgreSQL

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?

Upvotes: 24

Views: 21333

Answers (3)

Dmytrii Nagirniak
Dmytrii Nagirniak

Reputation: 24108

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

Upvotes: 38

hammady
hammady

Reputation: 988

To know whether there is at least 1 true value:

select sum(cast(data as int)) > 0

This is better than the exists solution because it can be embedded into a larger query, maybe containing group by and other where clauses

Upvotes: 2

Thomas Mueller
Thomas Mueller

Reputation: 50127

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);

Upvotes: 6

Related Questions