Does PostgreSQL support precision and scale for Rails?

Question

I have an Rails application that defines a migration that contains a decimal with precision 8 and scale 2. The database I have set up is PostgreSQL 9.1 database.

class CreateMyModels < ActiveRecord::Migration
    def change
        create_table :my_models do |t|
            t.decimal :multiplier, precison: 8, scale: 2
            t.timestamps
        end
    end
end

When I run rake db:migrate, the migration happens successfully, but I noticed an error when I was trying to run a MyModel.find_or_create_by_multiplier. If I ran the following command twice, the object would get created twice:

MyModel.find_or_create_by_multiplier(multiplier: 0.07)

I am assuming this should create the object during the first call and then find the object during the second call. Unfortunately, this does not seem to be happening with the multiplier set to 0.07.

This DOES work as expected for every other number I have thrown at the above command. The following commands work as expected (creating the object during the first call and then finding the object during the second call).

MyModel.find_or_create_by_multiplier(multiplier: 1.0)

MyModel.find_or_create_by_multiplier(multiplier: 0.05)

MyModel.find_or_create_by_multiplier(multiplier: 0.071)

When I view the PostgreSQL database description of the MyModel table, I notice that the table does not have a restriction on the numeric column.

   Column    |            Type             |                         Modifiers
-------------+-----------------------------+-------------------------------------------------------
 id          | integer                     | not null default nextval('my_models_id_seq'::regclass)
 multiplier  | numeric                     | 
 created_at  | timestamp without time zone | not null
 updated_at  | timestamp without time zone | not null

My db/schema.rb file also does not state the precision and scale:

ActiveRecord::Schema.define(:version => 20121206202800) do

...
    create_table "my_models", :force => true do |t|
        t.decimal  "multiplier"
        t.datetime "created_at",                   :null => false
        t.datetime "updated_at",                   :null => false
    end
...

So my first question is, why do I not see precision and scale pushed down to PostgreSQL when I migrate? Documentation states that it should be supported.

My second question is, why is 0.07 not correctly comparing using the MyModel.find_or_create_by_multiplier(multiplier: 0.07) command? (If I need to open another question for this, I will).

Answer 1

This is embarrassing...

I have precision misspelled.

Changing the migration to:

t.decimal :multiplier, precision: 8, scale: 2

fixed everything.

Answer 2

PostgreSQL 9.1 will let you declare a column in any of these ways.

column_name decimal
column_name numeric
column_name decimal(8, 2)
column_name numeric(8, 2)

If you look at that column using, say, pgAdminIII, it will show you exactly how it was created. If you (or Rails) created the column as numeric, it will say "numeric". If you (or Rails) created the column as decimal(8, 2), it will say "decimal(8, 2)".

So it looks to me like Rails is not passing precision and scale to PostgreSQL. Instead, it's simply telling PostgreSQL to create that column with type "numeric". Rails docs suggest it should not be doing that.

Example syntax in that link is different from yours.

td.column(:bill_gates_money, :decimal, :precision => 15, :scale => 2)