sums return String, only with postgresql

Question

I am migrating a database from mysql to postgres. The migration itself was ok, following the postgres documentation.

Right now, I'm fixing our specific mysql queries.

In some point, we have now 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 a sum(if(incomes, value, 0)) etc, and it was working as expected.

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

I already checked the database and the data type is correct.

What can I do, besides cast to_d or to_f?

EDIT: the complete 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

@mvp: Doesn't matter, `Model.select('1 as x').first.x.class` says `String` too. — mu is too short, Sep 12 '13 at 02:43
What do `Model.select('1 as x').first.x.class` and `Model.select("'1' as x").first.x.class` say with MySQL? I'd check myself but I don't have any Rails/MySQL apps set up. — mu is too short, Sep 12 '13 at 06:26
Now I'm in work, but as far as I can remember, it's BigDecimal. — caarlos0, Sep 12 '13 at 11:38
If `value` is numeric, there is no way PostgreSQL would return string for such expression. Could you show the real query? — MatheusOl, Sep 12 '13 at 11:39

score 3 · Accepted Answer · edited May 23 '17 at 12:20

3

As far as I know, using .to_f, .to_i or whatever is the answer - the Rails PostGres adapter seems adamant that everything is a String unless it's an ActiveRecord model.

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

I don't particularly approve of this, but it is, as the saying goes, 'working as intended'.

edited May 23 '17 at 12:20

Community

1
1

answered Sep 12 '13 at 19:43

MrTheWalrus

9,670
2
42
66

Yes, definitely a PostgreSQL activerecord issue rather than the database itself. – David Aldridge Sep 12 '13 at 20:02
You could do things like `Model.select(...).each { |m| def m.x; attributes['x'].to_i; end }` if desired. – mu is too short Sep 12 '13 at 20:38
that's basically what I did... but, it feels wrong... Thanks anyway. – caarlos0 Sep 13 '13 at 00:22

sums return String, only with postgresql

1 Answers1

Linked