Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

postgres decimal/numeric type has no precision with add_column

#3436
jruby 1.4.0RC1 (ruby 1.8.7 patchlevel 174) (2009-09-30 80c263b) (Java HotSpot(TM) 64-Bit Server VM 1.6.0_13) [x86_64-java]
psql (PostgreSQL) 8.3.7
Mac OSX 10.4
Rails 2.3.4

I saw an old bug similar to this one on the old TRAC server. Unfortunately no time this week to look at the code, but here's the issue.

#    add_column :target_prices, :tp, :decimal, :precision => 21, :scale => 5, :default => 0
#    add_column :target_prices, :tp_min, :decimal, :precision => 21, :scale => 5, :default => 0
#    add_column :target_prices, :tp_max, :decimal, :precision => 21, :scale => 5, :default => 0

My workaround for the time being:
    execute "ALTER TABLE rn_a_target_prices ADD column tp decimal(21,5) default 0"
    execute "ALTER TABLE rn_a_target_prices ADD column tp_min decimal(21,5) default 0"
    execute "ALTER TABLE rn_a_target_prices ADD column tp_max decimal(21,5) default 0"

The problem is a bit nasty in that the migrations complete without error, however the screens are showing and saving integers. The database looks like this:

                                      Table "public.rn_a_target_prices"
   Column    |            Type             |                            Modifiers                            
-------------+-----------------------------+-----------------------------------------------------------------
 id          | integer                     | not null default nextval('rn_a_target_prices_id_seq'::regclass)
 tp_type     | character varying(255)      | 
 security_id | integer                     | 
 created_at  | timestamp without time zone | 
 updated_at  | timestamp without time zone | 
 tp          | numeric                     | default 0::numeric
 tp_min      | numeric                     | default 0::numeric
 tp_max      | numeric                     | default 0::numeric
Indexes:
    "rn_a_target_prices_pkey" PRIMARY KEY, btree (id)

It IS possible to add decimals to the table via SQL, however activerecord does not seem to do so, nor does it read the values as decimals.

After the alter table command directly, the table looks like this:

                                      Table "public.rn_a_target_prices"
   Column    |            Type             |                            Modifiers                            
-------------+-----------------------------+-----------------------------------------------------------------
 id          | integer                     | not null default nextval('rn_a_target_prices_id_seq'::regclass)
 tp_type     | character varying(255)      | 
 security_id | integer                     | 
 created_at  | timestamp without time zone | 
 updated_at  | timestamp without time zone | 
 tp          | numeric(21,5)               | default 0
 tp_min      | numeric(21,5)               | default 0
 tp_max      | numeric(21,5)               | default 0
Indexes:
    "rn_a_target_prices_pkey" PRIMARY KEY, btree (id)

And AR, does indeed pick up the correct types.

As mentioned, I'm on a time critical deliver at the moment (who isn't!), but will take a look as soon as I can, see if I can send in a patch.

Reported by Gresh · October 28th, 2009 @ 06:43 AM

State: stale
Milestone: none
Assigned to: nobody
Importance: none

Activity

  1. Rohit Arondekar
    Rohit Arondekar
    • State changed from new to stale
    • Importance changed from to

    Marking ticket as stale. If this is still an issue please leave a comment with suggested changes, creating a patch with tests, rebasing an existing patch or just confirming the issue on a latest release or master/branches.

    October 6th, 2010 @ 06:34 AM