DECIMAL ​

The DECIMAL data type stores signed integer values with a user-defined precision and scale. NUMERIC and DEC are valid synonyms for DECIMAL. The sign character (+ or -) is optional. Leading zeroes and whitespace characters are allowed. Trailing whitespace characters are also allowed.

Precision and Scale ​

The precision of a DECIMAL column defines its maximum total number of digits, including decimal places (its scale). The maximum precision for a DECIMAL column is 38.

The scale of a DECIMAL column defines the maximum number of digits to the right of the decimal point. For example, the number 5999.95 has a precision of 6 and a scale of 2. A DECIMAL column that is created without a scale value has a default scale of 0. A DECIMAL column that is created without precision and scale values defaults to DECIMAL(18,0).

You cannot load a value into a DECIMAL column if the number of digits to the left of the decimal point exceeds the precision of the column minus its scale. The specified precision and scale determine the allowed range of values for the column. For example:

If the number of digits to the right of the decimal point exceeds the specified scale, the input value is rounded rather than rejected.

Examples with DECIMAL Columns ​

The following table contains two DECIMAL columns:

premdb=# create table dectest(c1 dec(5,2),c2 dec(13,9));
CREATE TABLE
premdb=# \d dectest
      Table "public.dectest"
 Column |     Type      | Modifiers 
--------+---------------+-----------
 c1     | numeric(5,2)  | 
 c2     | numeric(13,9) | 

Distribution: Hash (c1)

The following values are in range and are inserted into the table:

premdb=# insert into dectest values(178.56,2100.000000001);
INSERT 0 1
premdb=# select * from dectest;
   c1   |       c2       
--------+----------------
 178.56 | 2100.000000001
(1 row)

The following values have to be rounded to fit the specified scale. Note that the value 178.56666 is rounded up to 178.57.

premdb=# insert into dectest values(178.56666,2100.000000001111);
INSERT 0 1
premdb=# select * from dectest;
   c1   |       c2       
--------+----------------
 178.56 | 2100.000000001
 178.57 | 2100.000000001
(2 rows)

The following value is out of range for column c1 so the INSERT operation fails with an expected overflow error:

premdb=# insert into dectest(c1) values(1789.56666);
ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 2 must round to an absolute value less than 10^3.

In This Section

• Calculations with DECIMAL Values

• Examples of DECIMAL Calculations

Parent topic:SQL Data Types