PostgreSQL Arbitrary Precision Numbers ExplainedNov. 17, 2018
This post is part of a series on the numeric data types found in PostgreSQL. Read on to learn all about defining arbitrary precision numbers in PostgreSQL. Write a comment if you have any questions or corrections!
Arbitrary Precision Numbers
|numeric||variable||up to 131,072 digits before the decimal; up to 16,383 digits after the decimal|
|decimal||variable||same as numeric|
decimal (they are interchangeable) to store monetary amounts and other quantities where exactness is required. Calculations on
decimal values are slower than those performed on
floating-point types, but yield more exact results.
When defining a
decimal type, you need to specify the amount of precision and scale that you want.
- Precision is the total count of digits on both sides of the decimal. For example, a number like
237.56has a precision of 5.
130023.303has a precision of 9. The precision must be positive.
- Scale is count of decimal digits (i.e. the amount of digits to the right of the decimal). For example,
921.240has a scale of 3.
3490.12has a scale of 2. Integers have a scale of zero. The scale must be zero or positive.
When defining a
decimal column type the syntax is:
NUMERIC(precision, scale); DECIMAL(precision, scale);
NUMERIC(5, 2) -- Stores numbers up to 999.99
Let's imagine I've created a table called
num_tests and I have one column called
numbah with a data type of
NUMERIC(5, 2). This means that
numbah will have a precision of 5 and a scale of 2. It will store numbers up to 999.99.
Now let's explore what happens when we try to store different values into this column.
-- Table definition CREATE TABLE num_tests ( numbah NUMERIC(5, 2) ); /* Stores 2.32 as that number is in the precision and scale range defined. */ INSERT INTO num_tests VALUES(2.32); /* Stores 3.40. Rounds any digits that come after the scale defined. Since our scale is 2, but we're inserting .402 that gets rounded to .40 */ INSERT INTO num_tests VALUES(3.402); /* Stores 3.00. Adds the two zeros to satisfy our scale condition of 2. */ INSERT INTO num_tests VALUES(3); /* Errors out with error: numeric field overflow This happens because the number is greater than the precision specified. */ INSERT INTO num_tests VALUES(3023.23);
Alternatively, we can define the
decimal type by leaving off the scale or the precision AND scale.
With no scale defined, the scale defaults to zero. When you don't specify any precision or scale, this creates a column where any precision and scale can be stored, up to the range as defined in the table above. Best practice seems to be to always define your precision and scale.
The maximum allowed precision when explicitly defined as a type (
NUMERIC(5, 2)) is 1000.
Besides numeric values, you can also store
NaN ("not-a-number") values in the
INSERT INTO num_tests VALUES('NaN');
When PostgreSQL rounds a
decimal value like
-2.5 it will round up to
-3. This behaves slightly differently from
double precision rounding, which rounds to the nearest even number. For example:
x | numeric | double ------+-----------+----------- -3.5 | -4 | -4 -2.5 | -3 | -2 -1.5 | -2 | -2 -0.5 | -1 | -0 0.5 | 1 | 0 1.5 | 2 | 2 2.5 | 3 | 2 3.5 | 4 | 4
More Numeric Types
Learn about the other numeric data types in one of the following posts: