Taking the average of a boolean column in both postgres and sqlite3

Question

I've got a lot of ActiveRecord queries that find a percentage using the average of an expression, for example:

def self.undergraduate_degree_percentage
  self.average("undergrad_college_name != ''")
end

These were built for sqlite3 (the development database) but break down in postgres (the production database) because pg handles booleans differently. The error generated is something like ERROR: function avg(boolean) does not exist at character 8. Is there an elegant way to make this type of query work for both sqlite3 and PG?

Not a solution to your problem just an advice: Try using the same database in development that you are using in production. If you're on a Mac, Postgres is quite easy to use with [Postgres.app](http://postgresapp.com/) or installed with Homebrew. It's one of the best headache avoiding moves I ever made. — Thomas Klemm, Mar 14 '13 at 21:24

score 3 · Accepted Answer · edited May 23 '17 at 10:27

Boolean values in databases are difficult to work with portably:

MySQL and SQLite use C-style booleans natively.
The ActiveRecord driver for SQLite sometimes mistakenly uses 't' and 'f' strings for booleans. This might be fixed in newer versions.
PostgreSQL has a native boolean type.
Other databases do other things.
Sometimes there are automatic casts to numeric values and sometimes there aren't.
?
Profit.

If you want to do this portably, then you should convert the booleans by hand. In your case, you are lucky to have an expression that yields a native boolean value so you don't have to worry about AR's SQLite brain damage. A simple CASE should make things work the same everywhere:

self.average(%q{
  case
    when undergrad_college_name != '' then 1.0
    else 0.0
  end
})

That will give you a floating point result (probably a Float or BigDecimal) and then you can decide how you want deal with it from there.

Taking the average of a boolean column in both postgres and sqlite3

1 Answers1