PostgreSQL tracking votes in a table?

Question

I have a table with a simple up/down vote column which I originally created as a boolean. true is a vote up, false is a vote down. However, I'm not sure how to use aggregate functions to achieve this kind of query result. For example, 5 true rows and 2 false should equal a vote of +3.

I'm thinking that I need to change the column to a smallint with +1 and -1. Is this correct? Is there a better way to query something like this?

Answer 1

No need to change the datatype, simply use a CASE to convert it to -1 and 1, then sum over the expression:

SELECT sum(case when vote_column then 1 else -1 end)
FROM your_table

To properly deal with NULL values, use the following

SELECT sum(case vote_column 
              when true then 1 
              when false then -1 
              else 0 
           end)
FROM your_table