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.

Improve this question
1

1 Answer 1

Reset to default
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).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.