Postgresql table values don't match Ruby on Rails Active Record query results

Question

I have a table named monthly_trips in Postgresql. There is a row with values id = 3 & al = 107.415. My problem is when I try to access the value of the "al" column in rails. Using the Rails Console with the code below I get the result of 6154.426156397738. I have no idea what is generating this larger number (57.295779513 times larger to be exact). Any help would be greatly appreciated! Let me know if you need more information.

trip = MonthlyTrip.find(3)
result = trip.al

monthly_trip.rb

class MonthlyTrip < ActiveRecord::Base
  attr_accessible :al, :month, :vehicle_id, :year
  belongs_to :vehicle
end

psql output (select id, al from monthly_trips where id = 3;)

 id |   al    
----+---------
  3 | 107.415
(1 row)

psql output (\d monthly_trips)

   Table "public.monthly_trips"
   Column   |            Type             |                         Modifiers                          
------------+-----------------------------+------------------------------------------------------------
 id         | integer                     | not null default nextval('monthly_trips_id_seq'::regclass)
 vehicle_id | integer                     | 
 year       | integer                     | 
 month      | integer                     | 
 al         | numeric(9,3)                | 
 ak         | numeric(9,3)                | 
 az         | numeric(9,3)                | 
 ar         | numeric(9,3)                | 
 ca         | numeric(9,3)                | 
 co         | numeric(9,3)                | 
 ct         | numeric(9,3)                | 
 de         | numeric(9,3)                | 
 fl         | numeric(9,3)                | 
 ga         | numeric(9,3)                | 
 hi         | numeric(9,3)                | 
 ida        | numeric(9,3)                | 
 il         | numeric(9,3)                | 
 ind        | numeric(9,3)                | 
 ia         | numeric(9,3)                | 
 ks         | numeric(9,3)                | 
 ky         | numeric(9,3)                | 
 la         | numeric(9,3)                | 
 me         | numeric(9,3)                | 
 md         | numeric(9,3)                | 
 ma         | numeric(9,3)                | 
 mi         | numeric(9,3)                | 
 mn         | numeric(9,3)                | 
 ms         | numeric(9,3)                | 
 mo         | numeric(9,3)                | 
 mt         | numeric(9,3)                | 
 ne         | numeric(9,3)                | 
 nv         | numeric(9,3)                | 
 nh         | numeric(9,3)                | 
 nj         | numeric(9,3)                | 
 nm         | numeric(9,3)                | 
 ny         | numeric(9,3)                | 
 nc         | numeric(9,3)                | 
 nd         | numeric(9,3)                | 
 oh         | numeric(9,3)                | 
 ok         | numeric(9,3)                | 
 ore        | numeric(9,3)                | 
 pa         | numeric(9,3)                | 
 ri         | numeric(9,3)                | 
 sc         | numeric(9,3)                | 
 sd         | numeric(9,3)                | 
 tn         | numeric(9,3)                | 
 tx         | numeric(9,3)                | 
 ut         | numeric(9,3)                | 
 vt         | numeric(9,3)                | 
 va         | numeric(9,3)                | 
 wa         | numeric(9,3)                | 
 wv         | numeric(9,3)                | 
 wi         | numeric(9,3)                | 
 wy         | numeric(9,3)                | 
 created_at | timestamp without time zone | not null
 updated_at | timestamp without time zone | not null
Indexes:
    "monthly_trips_pkey" PRIMARY KEY, btree (id)

Migration file

class CreateMonthlyTrips < ActiveRecord::Migration
  def change
    create_table :monthly_trips do |t|
      t.integer :vehicle_id
      t.integer :year
      t.integer :month
      t.decimal :al, precision: 9, scale: 3
      t.timestamps
    end
  end
end

pg_locks table

locktype | database | relation | virtualxid | virtualtransaction | pid | mode | granted

"relation" | 11955 | 11000 | blank | "8/18417" | 25475 | "AccessShareLock" | TRUE

"virtualxid" | blank | blank | "8/18417" | "8/18417" | 25475 | ExclusiveLock" | TRUE

@cdesrosiers al is declared as a decimal with precision: 9, scale: 3 in my migration. This translated into a numeric(9,3) in Postgresql. — Ferrari692, Dec 10 '12 at 04:58
@cdesrosiers I added the MonthlyTrip model for you. I have subtracted all the other state abbreviations from the attr_accessible so that it is not so long. — Ferrari692, Dec 10 '12 at 05:44
@muistooshort I have added the psql output for you. If you are wondering, the other abbreviations are for the remaining 50 states. They are all set-up the same as al (Alabama). — Ferrari692, Dec 10 '12 at 05:47
When you read `trip.al` in the console, you should be getting a `BigDecimal` object (`#`). What does that look like? — cdesrosiers, Dec 10 '12 at 06:08
@cdesrosiers when I enter trip.al all that displays is the number 6154.426156397738. There is no mention of BigDecimal. — Ferrari692, Dec 10 '12 at 18:02
Stupid question, but can you verify that your 'id' is unique (primary key)? And while you're at it, check that you have no uncommitted transactions open... — PinnyM, Dec 10 '12 at 21:41
@PinnyM: That's implied by the `create_table` in the migration, you can also see the PK at the bottom of the `\d` output from `psql`. — mu is too short, Dec 10 '12 at 21:42
Missed the psql output, and I wasn't sure if the migration was actually used to create the table ;)... Still, can you check on the transactional thing? — PinnyM, Dec 10 '12 at 21:43
One other thing, can you trace the rails command in console when you run `trip.al`? A long shot, but maybe something is overriding somewhere. It might help to post results for other ids as well besides for '3'... — PinnyM, Dec 10 '12 at 21:46
Further along PinnyM's last comment, does the problem only happen with `.al`? What happens with the non-Alabama accessors? — mu is too short, Dec 10 '12 at 21:48
@PinnyM regarding checking uncommitted transactions and tracing inside rails console I am not sure how to do that. If you can give me some hints I will gladly get you that info. — Ferrari692, Dec 10 '12 at 22:13
@muistooshort I have tried some of the other ids with the same result no matter what state column. It seems rails always thinks the number is 57x larger than the postgresql value. — Ferrari692, Dec 10 '12 at 22:13
For tracing, you can use [unroller](http://unroller.rubyforge.org/). It should be pretty self explanatory. For transactions, you can check [pg_locks](http://www.postgresql.org/docs/9.1/interactive/view-pg-locks.html). — PinnyM, Dec 10 '12 at 22:32
@PinnyM I posted pg_locks for you. Tried the unroller gem but now I get the following when I try to start the rails console. "gems/unroller-1.0.0/lib/unroller.rb:4:in `require': cannot load such file -- facets/methodspace (LoadError)" — Ferrari692, Dec 10 '12 at 23:29
Which version of Rails and `pg` are you using? I checked a 3.2 app that has a `numeric` (AKA `:decimal`) column in the database and it gets pulled out as a BigDecimal. You're getting a `Float` for some reason and that's not right. — mu is too short, Dec 11 '12 at 01:27
@muistooshort I am using 3.2 and Postgresql 9.1. The numbers in pg are input by a Ruby file that runs outside of Rails. I will check if it is inputting the numbers as Float and get back to you. — Ferrari692, Dec 12 '12 at 04:47
@muistooshort it turns out that the ruby file that inserts the data into PG is using Float. I looks like this is probably the issue. Would you recommend switching Rails/PG from decimal to float or switch the ruby file to insert decimal numbers. — Ferrari692, Dec 12 '12 at 20:34
Inserting decimals would probably be better, you might be able to send the strings (without a `to_f` or similar call) straight into PostgreSQL and let it convert them to `numeric(m,n)`s. Depending on how this other Ruby script works of course. It is strange that they look like the right values inside PostgreSQL though. — mu is too short, Dec 12 '12 at 21:03
@muistooshort I tried a to_d conversion on the Float in that ruby file and I am generating the same wrong number before it is inserted to PG. It must be a problem with Rails using Ruby's to_d to convert the Float. Thanks for pointing me toward this! — Ferrari692, Dec 12 '12 at 21:34
@Ferrari692, I didn't find any issues with latest stable versions of rails and pg. If you didn't solve this issue yet, then feel free to fork this test repo and update it with your Gemfile. https://github.com/cutalion/so_question_13795080_test — cutalion, Jan 22 '13 at 12:47

score 1 · Answer 1 · answered Apr 11 '13 at 03:13

Your "trip" variable results for an array that's why it returns BigDecimal

You can use this kind of method:

1) Indicated the 1st value returned from ActiveRecord

trip = MonthlyTrip.find(3).first
result = trip.al

OR

2) Looping each element found in the database.

trip = MonthlyTrip.find(3) trip.each do |t| puts t.al end

Postgresql table values don't match Ruby on Rails Active Record query results

1 Answers1