current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

Postgres aggregates like SUM(foo) return a string, even if foo is an integer.

How do I cast the sum to an integer so the resulting array of relations contains an integer not a string?

I tried to cast it using ::integer

Widget.select("SUM(points) as totalpoints::integer, agent, company")

but postgres throws error PG::Error: ERROR: syntax error at or near "::"

It seems there should be some way to tell rails - short of iterating through each returned array element - that the SUM of an INT is an INT?

share|improve this question
add comment

1 Answer

up vote 1 down vote

This might just be the ActiveRecord adapter. I'm not entirely sure about that, though. But I think when you go down to the selection-level APIs it just returns strings, because that's what it actually reads back from the database. When you use the higher-level APIs I think it knows how to transform it back into the Ruby types because it's aware of your schema there.

I'm having trouble reproducing your query above, but I did the following:

Widget.connection.select_rows("SELECT sum(id) FROM widgets")

This returned:

[
    [0] [
        [0] "887"
    ]
]

And if I use a higher-level API:

2.0.0-p353 :034 > Widget.sum('id')
D, [2014-06-24T15:34:52.644852 #95176] DEBUG -- :    (0.5ms)  SELECT SUM("widgets."."id") AS sum_id FROM "widgets"
887

At this point it's giving me an Integer type in my console. So I suspect there's nothing wrong with Postgres, but that you may need to convert to native types yourself when you're using ActiveRecord select APIs.

share|improve this answer
1  
You're basically right. AR doesn't really understand anything about types from SQL that it doesn't write itself. –  mu is too short Jun 24 at 20:39
    
yes, postgres returns string. seems like should be possible to somehow CAST that to an int in the query. Right now I use a .map() to iteratively force it to int, but SUMing ints is such a fundamentally basic operation it seems hard to believe it's not a simple query operation. –  jpwynn Jun 25 at 4:48
    
No, you're looking at this the wrong way. When you're using some of the lower-level APIs to communicate your query to postgres, it's effectively like you're typing into a psql console. Postgres knows it's an integer, but it's basically just dumping blobs of text the same way you're getting in psql. Rails doesn't parse the SQL you're passing in so it doesn't know what types to expect in response, so it's unable to auto-convert as it usually does. So it's up to you to do that conversion yourself. You can't just CAST in Postgresql; postgres knows it's an integer. Rails doesn't. –  bratsche Jun 25 at 4:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.