9

I'm converting a rails app from using mysql (mysql2 gem) to postgres (pg gem).

With mysql, ActiveRecord::Base.connection.select_value calls return values typed according to the data, for example:

> ActiveRecord::Base.connection.select_value("SELECT COUNT(*) FROM errors")
=> 86
> ActiveRecord::Base.connection.select_value("SELECT exception FROM errors where id=565")
=> "TechTalk.Genome.SqlExecutionException"
> ActiveRecord::Base.connection.select_value("SELECT id FROM errors where id=565")
=> 565

However, with postgres, connection.select_value always returns a string:

> ActiveRecord::Base.connection.select_value("SELECT COUNT(*) FROM errors")
=> "1"
> ActiveRecord::Base.connection.select_value("SELECT id FROM errors")
=> "1"
> ActiveRecord::Base.connection.select_value("SELECT source FROM errors limit 1")
=> "webapp"

This broke a few unit tests, and while those are fixable, I'm certain we have other code relying on these return values. Is there a way to get properly-typed return values from connection.select_value when using postgres?

Monica Woods
  • 147
  • 1
  • 6

2 Answers2

4

The short answer is no. The 'pg' driver intentionally provides as thin a layer as possible on top of the native 'libpq' driver. It doesn't do typecasting, as that's the responsibility of higher-level libraries that have some insight into the domain in which the results will be used. The rationale for this decision is documented on the PostgreSQL Wiki, and I'd be happy to discuss it with you further on the mailing list.

Michael Granger
  • 1,358
  • 9
  • 14
  • 5
    Worst best answer ever! ;) Thanks. – Monica Woods Sep 27 '12 at 23:30
  • "thin a layer as possible on top of the native 'libpq' driver." trouble is, string allocations in ruby are actually quite expensive, seems so wasteful – Sam Saffron Jan 16 '13 at 06:31
  • I think it's a matter of circumstance whether it's more wasteful to allocate strings for each column or try to map PostgreSQL types into Ruby by default. If your column types all have exact Numeric-derived (non-BigNum) equivalents in Ruby, it probably is wasteful to represent them all as Strings. However, it seems irresponsible to me to implement what can only ever be a partially-complete type-mapping system based on the potential memory waste in that comparatively rare circumstance. – Michael Granger Jan 24 '13 at 23:30
  • I agree with the idea expressed by Michael but in the context of Rails, I feel the driver should derive the data type according to its 'native_database_types' table. – gamov Oct 13 '14 at 09:50
0

For those who land here looking for an activerecord attributes (Rails) specific answer: I've done some tests (https://gist.github.com/gamov/8fe38733012931eb3360) and discovered that:

RequestedItem.where(id: 1).select(*, 10 AS tq).first.tq.class

returns a String with Rails < 4 and a Fixnum with Rails >= 4.

The Postgres adapter will forward the types from the DB to the Persistence module so casting can be done transparently.

gamov
  • 3,789
  • 1
  • 31
  • 28