Thread

Re: [HACKERS] Counting bool flags in a complex query

Michael Richards <miker@scifair.acadiau.ca> — 1999-07-14T14:51:31Z

On Wed, 14 Jul 1999, Duane Currie wrote:

> > My final problem is to count all the messages with flagnew set to true.
> > The only way I can think to do this is to convert the bool value to a 1 or
> > 0 (which I think should be a standard conversion anyway) and run a sum()
> > on them.
> > 
> > Unless anyone can come up with a better way to do this, What is the best
> > way to implement a conversion from bool to int?
> 
> Of course, you could always use count() and a 'WHERE flagnew' clause...

Problem with that of course is that by limiting the query with a "where",
I'd lose all the records in the original count, and therefore the total
number of messages (a count that ignores the status of flagnew) would be
wrong.

What I was sort of hoping for was a way to implement a native conversion
from bool to int, and have it included in the standard postgres system. I
think the conversion if a reasonable logical one where true==1 and
false==0. The problem is, I don't have a sweet clue how to do this. I
think it should be a trivial matter to insert something into a system
table...

-Michael