Numeric Types#

Name	Storage Size	Description	Range
smallint	2 bytes	small-range integer	-32768 to +32767
integer	4 bytes	typical choice for integer	-2147483648 to +2147483647
bigint	8 bytes	large-range integer	-9223372036854775808 to +9223372036854775807
decimal	variable	user-specified precision, exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric	variable	user-specified precision, exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real	4 bytes	variable-precision, inexact	6 decimal digits precision
double precision	8 bytes	variable-precision, inexact	15 decimal digits precision
smallserial	2 bytes	small autoincrementing integer	1 to 32767
serial	4 bytes	autoincrementing integer	1 to 2147483647
bigserial	8 bytes	large autoincrementing integer	1 to 9223372036854775807

1. Integer Types#

smallint, integer and bigint store whole numbers (without fractional parts).

Attempting to store a value outside the allowed range throws an error.

integer (int) offers the best range-storage-performance balance.

2. Arbitrary Precision Numbers#

numeric and decimal are equivalent, and are both part of the SQL standard. They are especially recommended for storing quantities where exactness is required e.g money.

Calculations on numeric values yields exact results where possible, but are relatively much slower than in integer or floating-point types.

precision: the total count of significant digits to both sides of the decimal point. Must be positive.
scale: the count of decimal digits in the fractional part. Positive or zero.

NUMERIC(precision, scale)
NUMERIC(precision)    -- zero scale
NUMERIC    -- unconstrained

NOTE: The maximum precision that can be explicitly specified in a NUMERIC type declaration is 1000.

An unconstrained NUMERIC is subject to the implementation limits in the table above.

The SQL standard requires a default scale of 0 (coercion to integer precision), so always specify precision and scale to ensure portability.

Values with a larger scale than that set will be rounded to the set scale. Then, if the new precison exceeds that declared, an error is raised.

NOTE: Numeric values are stored without extra leading or trailing zeroes. The declared precison and scale are maximums, not fixed allocations (akin to varchar).

The actual storage requirement is 2 bytes per 4 decimal digits, plus a 3 to 8 byte overhead.

The numeric type also includes the special values 'Infinity' ('inf'), '-Infinity' ('-inf') and 'NaN', case insensitive.

inf + x = inf
inf + inf = inf
inf - inf = NaN
x / inf = 0

NaN is used to represent undefined calculation results. Operations with a NaN input yield another NaN, with some exceptions e.g. NaN ^ 0.

NOTE: In most implementations, NaN is considered not equal to any other numeric value (including NaN).

In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

When rounding values, the numeric type rounds ties away from zero, while float types round ties to the nearest even number:

mydb=> SELECT x,
mydb->        round(x::numeric) AS numeric_round,
mydb->        round(x::double precision) AS double_round
mydb->   FROM generate_series(-3.5, 3.5, 1) as x;
  x   | numeric_round | double_round 
------+---------------+--------------
 -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
(8 rows)

3. Floating-Point Types#

real and double precision are inexact, variable-precision numeric types - implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively).

Some values can’t be converted exactly to the internal format, and are stored as approximations, such that storing and retrieving a value might show slight discrepancies.

real has a range of around 1E-37 to E+37, with a precision of at least 6 decimal digits.

double precision has a range of 1E-307 to 1E+308, with a precision of at least 15 digits.

Values that are too large/small raise an error.

Input values with excess precison might be rounded.

Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.

By default, floating point values are output in text form in their shortest precise decimal representation:

mydb=> SELECT 4.213327242424::real;
  float4   
-----------
 4.2133274
(1 row)

The extra_floats_digits parameter can be used to select the rounded decimal output. Setting zero restores the default. Negative values reduce significant decimals, and positive values select the shortest-precise format.

Floating-point types also include special values 'Infinity' ('inf'), '-Infinity' ('-inf') and 'NaN'.

PostgreSQL also supports the SQL-standard notations float and float(p) for specifying inexact numeric types, where p specifies the minimum acceptable precision in binary digits.

float(1) to float(24) select the real type. float(25) to float(53) select double precision. Values of p outside the allowed range draw an error.

float with no precision specified is taken to mean double precision.

4. Serial Types#

smallserial, serial, and bigserial are not true types, but notational convenience for creating unique identifier columns (similar to AUTO_INCREMENT).

The tables from the queries below are equivalent:

CREATE TABLE table1 (
    col1 SERIAL
);

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE table2 (
    col1 integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY table2.col1;

i.e:

create a sequence
create an integer column whose default values are assigned from a sequence generator
Add constraints e.g PRIMARY KEY to ensure values are unique and non-null.
mark the sequence as owned by the column, so that it will be dropped if the column or table is dropped.

NOTE: Because smallserial, serial and bigserial are implemented using sequences, there may be gaps in the sequence of values which appears in the column, even if no rows are ever deleted.

A value allocated from the sequence is still “used up” even if a row containing that value is never successfully inserted into the table column e.g in rolled back transactions.

To insert a value into a serial column, either exclude it from the list of columns or use the DEFAULT keyword.

serial and serial4 are equivalent: both create integer columns.

bigserial and serial8 create bigint columns.

smallserial and serial2 create smallint columns.