Sums return String, only with postgresql

Question

I am moving the database from mysql to postgres. The migration itself was fine, following the postgres documentation.

I am currently fixing our specific mysql queries.

At some point, we have something like this:

select(%( SUM(CASE WHEN income THEN value ELSE 0 END) AS rents, SUM(CASE WHEN not income THEN value ELSE 0 END) AS expenses )) 

In mysql, it was sum(if(incomes, value, 0)) , etc., and it worked as expected.

With PG, it returns a string instead of a numeric one.

I already checked the database and the correct data type.

What to do, except cast to_d or to_f ?

---

EDIT : full query:

 SELECT SUM(CASE WHEN income THEN value ELSE 0 END) AS rents, SUM(CASE WHEN not income THEN value ELSE 0 END) AS expenses FROM "transactions" WHERE "transactions"."type" IN ('Transaction') AND "transactions"."user_id" = 1 AND "transactions"."paid" = 't' AND (transactions.date between '2013-09-01' and '2013-09-30') LIMIT 1 

Answer 1

As far as I know, using .to_f , .to_i or something like that - the Rails PostGres adapter seems adamant that everything is a string if it is not an ActiveRecord model.

See: connection.select_value returns rows only in postgres with pg gem

I do not particularly approve of this, but as they say, "works as intended."