Queries are returning integer columns as strings

[Jim Cain]

For every table and every query I try that includes integer columns, they are returned as strings instead of numbers.

For example, I have created and populated a table:

create table alpha (id serial, name varchar(30));

insert into alpha (name) values ('Jim');

When I query this table using ruby-pg, the ID column is returned as a string:

irb(main):005:0> db.exec("select id,name from alpha"){|r| r.each {|t| p t}}

{"id"=>"1", "name"=>"Jim"}

What am I missing? Thanks for the help.

Cheers,

Jim

[Lars Kanis]

This is true. Ruby-pg is build as a thin wrapper around libpq and
libpq provides only text or binary result sets. So, it's the same for
ruby-pg. You have to do the type conversion by yourself or use a
higher level library like activerecord for the job.

--
Regards,
Lars

[Jim Cain]

That's very unfortunate. I suppose I'll have to use something else, like MySQL.

[Michael Granger]

On Saturday, March 10, 2012 1:43:53 PM UTC-8, Jim Cain wrote:

That's very unfortunate. I suppose I'll have to use something else, like MySQL.

Judging from your peevish reaction, I'm guessing you won't much care, but in case you're at all interested in the reasoning behind the design, ruby-pg follows the guidelines for driver development on the PostgreSQL Wiki. The relevant section in this case is the Type Conversions section.

That notwithstanding, I've been thinking lately about implementing an optional typecasting layer based on the pg_type table after several (considerably more collaborative and fruitful) conversions with Aaron Patterson. He already rewrote the typecasting layer for ActiveRecord, so I didn't commit my own until I had a chance to look at, and hopefully (with his permission, of course) steal all or parts of his implementation.

I'm not sure how this would look, but I'm thinking it would be a mixin that adds typecasted corollaries for the appropriate PG::Result methods such that enabling it wouldn't conflict with the existing API. It would be optional so users that don't need typecasting don't have to incur the overhead of building the conversion tables for each connection. I have a partial implementation already in an MQ patch, but I don't know when I'll have time to finish it.

Anyway, godspeed on your MySQL adventure.

[Jim Cain]

I decided to create my own library that talks to PostgreSQL directly and provides optional type translation:

https://github.com/jec/moses_pg

[Jeff Davis]

I'm glad to see that -- one of the main reasons for doing strings only
in the 'pg' gem is so that you _could_ write your own libraries on top
of it. [ Aside: if you haven't already looked at Sequel, you might
consider whether that solves your problem. ]

Data type conversions are never 100% settled, so we want to leave that
to high level languages where people can be more clever and change the
code more rapidly. Being clever and changing the code rapidly is great
when it's ruby code; but when it's C code, that leads to segfaults.

Another reason is that if it works like you want 99% of the time, that's
great. But that 1% can cause a lot of frustration ;) So a higher level
library can give you access to the lower level one in the 1% case,
letting you do what you need to do.

So, it's not that we think you only want strings. It's that the code is
modular; and we chose the point of separation carefully; and the part
that turns strings into a DateTime (or whatever) is somewhere else*.

Regards,
Jeff Davis

*: "Somewhere else" in a figurative/philosophical sense, not necessarily
a physical separation. It sounds like Michael has plans to work on this
problem within the pg gem, which sounds reasonable to me.

[Michael Granger]

Neat! I see that your Yak is now neatly shaved. :)