CODEWITHSUNDEEP
mysqlruby-on-railsactiverecordruby-on-rails-4
randombits
randombits

Reputation: 48450

ActiveRecord decimal type field truncating to integer

I have a table that was created like this in MySQL using a Rails 4 migration:

| nba_average_stats | CREATE TABLE `nba_average_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ppg` decimal(2,0) DEFAULT '0',
  `apg` decimal(2,0) DEFAULT '0',
  `rpg` decimal(2,0) DEFAULT '0',
  `tpm` decimal(2,0) DEFAULT '0',
  `blk` decimal(2,0) DEFAULT '0',
  `stl` decimal(2,0) DEFAULT '0',
  `year` int(11) DEFAULT '0',
  `player_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=414 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

The problem I have here is when I do something like:

stat.ppg = 4.3; stat.save

It shows up in the database as just the integer 4

Is my database type wrong? am I doing something wrong on the Rails end?

Upvotes: 2

Views: 575

Answers (2)

Ben Simpson
Ben Simpson

Reputation: 4049

You would want to increase the scale of the decimal:

https://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-characteristics.html

| nba_average_stats | CREATE TABLE `nba_average_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ppg` decimal(2,1) DEFAULT '0',
  `apg` decimal(2,1) DEFAULT '0',
  `rpg` decimal(2,1) DEFAULT '0',
  `tpm` decimal(2,1) DEFAULT '0',
  `blk` decimal(2,1) DEFAULT '0',
  `stl` decimal(2,1) DEFAULT '0',
  `year` int(11) DEFAULT '0',
  `player_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=414 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

The first argument of decimal is the precision, and the second argument is the scale. The precision is inclusive of the scale, so precision must always be greater or equal than scale. Scale is the number of digits after the decimal. To store the value 4.3 from your example above, you would need at least decimal(2,1) which would mean a maximum of 2 digits, 1 being after decimal.

Changing this to decimal(2,1) would allow you to store 4.3 as 4.3 instead of rounding down to 4.

Since you mentioned Rails 4 and migrations, you can specify precision and scale as optional arguments to the decimal column type in the migration DSL. For example:

add_column :nba_stats, :ppg, :decimal, :precision => 2, :scale => 1

http://guides.rubyonrails.org/active_record_migrations.html#column-modifiers

Upvotes: 1

Jacek
Jacek

Reputation: 412

decimal(2,0) means that You can store only integer numbers.

Upvotes: 0

Related Questions