[oracle] What is the difference between precision and scale?

What is the difference between precision and scale in Oracle? In tutorials they usually leave scale empty and set precision to 6 when creating a primary key.

What do precision and scale stand for?

This question is related to oracle types

The answer is


Maybe more clear:

Note that precision is the total number of digits, scale included

NUMBER(Precision,Scale)

Precision 8, scale 3 : 87654.321

Precision 5, scale 3 : 54.321

Precision 5, scale 1 : 5432.1

Precision 5, scale 0 : 54321

Precision 5, scale -1: 54320

Precision 5, scale -3: 54000


If value is 9999.988 and Precision 4, scale 2 then it means 9999(it represents precision).99(scale is 2 so .988 is rounded to .99)

If value is 9999.9887 and precision is 4, scale is 2 then it means 9999.99


Scale is the number of digit after the decimal point (or colon depending your locale)

Precision is the total number of significant digits

scale VS precision


precision: Its the total number of digits before or after the radix point. EX: 123.456 here precision is 6.

Scale: Its the total number of digits after the radix point. EX: 123.456 here Scaleis 3


Precision is the total number of digits, can be between 1 and 38.
Scale is the number of digits after the decimal point, may also be set as negative for rounding.

Example:
NUMBER(7,5): 12.12345
NUMBER(5,0): 12345

More details on the ORACLE website:
https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832


Precision 4, scale 2: 99.99

Precision 10, scale 0: 9999999999

Precision 8, scale 3: 99999.999

Precision 5, scale -3: 99999000


Precision is the number of significant digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.

Scale is the number of digits to the right (positive) or left (negative) of the decimal point. The scale can range from -84 to 127.

In your case, ID with precision 6 means it won't accept a number with 7 or more significant digits.

Reference:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

That page also has some examples that will make you understand precision and scale.