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

Use numeric or decimal (they are interchangeable) to store monetary amounts and other quantities where exactness is required. Calculations on numeric or decimal values are slower than those performed on integer and floating-point types, but yield more exact results.

When defining a numeric or 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.56 has a precision of 5. 130023.303 has 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.240 has a scale of 3. 3490.12 has a scale of 2. Integers have a scale of zero. The scale must be zero or positive.

When defining a numeric or decimal column type the syntax is:

NUMERIC(precision, scale);
DECIMAL(precision, scale);

Examples:

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 numeric or decimal type by leaving off the scale or the precision AND scale.

DECIMAL(precision);
DECIMAL;

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 numeric or decimal type.

INSERT INTO num_tests VALUES('NaN');

When PostgreSQL rounds a numeric or decimal value like -2.5 it will round up to -3. This behaves slightly differently from real and 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:

• Integer Type
• Floating-Point Numbers
• Serial Numbers