Wednesday, May 23, 2012

Why I like Postgresql? It has first-class boolean

What would you rather read. This?

select personId
from personDailyDiet
group by personId
having count(case when fruit = 'apple' then 1 end) = count(*)


Or this?

select personId
from personDailyDiet
group by personId
having every(fruit = 'apple');

And every automatically short-circuit if it find a row that doesn't meet the condition. Not only it is readable, it is fast too.

EVERY is an alias for bool_and, you can use bool_and to make your query sounds computer-sciencey ;-)
select personId
from personDailyDiet
group by personId
having bool_and(fruit = 'apple');


It's unfortunate that ANY keyword is already taken, the bool_or could be aliased as ANY.

bool_and = EVERY
bool_or = no english-like keyword

No comments:

Post a Comment