0

I have a column called closing_price which is DECIMAL(6,2). My understanding is that this means it can take 6 digits and 2 decimal places, so the largest number that could fit in this column would be 999999.99.

However, I am getting this error now:

Numeric value out of range: 1264 Out of range value for column 'closing_price' at row 1 (SQL: insert into `stock_history` (`date`, `closing_price`, `stock_id`, `updated_at`, `created_at`) values (2000-02-01, 51900.000000, 214, 2020-02-07 09:51:03, 2020-02-07 09:51:03))

I don't understand how this could fall out of range of DECIMAL(6,2). Please correct me if my understanding is wrong, or help me troubleshoot this if I am right.

I don't know why the number 51900 is showing zeros for 6 decimal places. I am getting these values from an API and a lot of them have up to 6 zeros at the end, but this is the first one that threw and error after over a million inserts.

sveti petar
  • 3,637
  • 13
  • 67
  • 144
  • Your understanding is incorrect the maximum you can store is -9999.99 - 9999.99 see https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html – P.Salmon Feb 07 '20 at 10:35

1 Answers1

1

Your understanding is slightly off. DECIMAL(6,2) means that there are 6 total places of precision, with two of those places occurring after the decimal point. So, to store a value as large as 999999.99, you would actually need DECIMAL(8,2).

If you want a generally good type for storing currency values, you might follow the advice of this canonical SO answer, which suggests using DECIMAL(15,2).

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360