MySQL Workbench out of range value for decimal number

Question

I wanted to create an table

create table Oceny_projekty
(
   OProj_Id             int not null  comment '',
   ID_Projektu          int  comment '',
   ID_Studenta          int  comment '',
   OProj_Ocena          decimal(1,1)  comment '',
   OProj_Data           date  comment '',
   primary key (OProj_Id)
);

And fill it with sample data generated by PowerDesigner

insert into Oceny_projekty (OProj_Id, ID_Projektu, ID_Studenta, OProj_Ocena, OProj_Data)
    values (2, 18, 10, '2.5', '1857-12-25');

And I've got this:

insert into Oceny_projekty (OProj_Id, ID_Projektu, ID_Studenta, OProj_Ocena, OProj_Data) values (2, 18, 10, '2.5', '1857-12-25') Error Code: 1264. Out of range value for column 'OProj_Ocena' at row 1

How can I modify command that can accept decimal numbers? (with integer numbers there is no problem) Using MySQL Workbench 6.3, PowerDesigner 16.6

Thanks in advance!

https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html — Paul Spiegel, Dec 09 '17 at 12:26
Possible duplicate of [MySQL Decimal Type Out Of Range Error](https://stackoverflow.com/questions/33348508/mysql-decimal-type-out-of-range-error) — Gord Thompson, Dec 09 '17 at 12:56
Did you associate a specific Test Data Profile with the column OProj_oCena? It seems strange that it generates numbers between quotes. — pascal, Dec 10 '17 at 13:55

Marek Grzenkowicz · Accepted Answer · 2017-12-09T13:41:09.323

1

Declaring OProj_Ocena as decimal(1,1) means that it has 0 integer digits (precision - scale = 1 - 1 = 0) and 1 fractional digit. It can only store values 0, 0.1, 0.2 ... 0.9.

The datatype you need is probably decimal(2,1).

edited Dec 09 '17 at 13:41

answered Dec 09 '17 at 13:16

Marek Grzenkowicz

17,024
9
81
111

score 0 · Answer 2 · answered Dec 22 '20 at 18:57

Putting Marek Grzenkowicz's answer in another way, DECIMAL(M, D):

M = D + (the number of whole number digits). See 12.25.2 DECIMAL Data Type Characteristics

So, determine the number of whole number digits you want, those to the left of the decimal point, and the number of decimal places (D) that you need and add them to get M. IMO, they should have used the phrase "whole number digits" or "integer digits" to make this a bit more obvious even though what they say means that as M is for ALL digits. I misinterpreted this at first until I reread their description a few times.

MySQL Workbench out of range value for decimal number

2 Answers2