PostgreSQL's numeric types are used to represent both whole integers and decimal floating point values. From a general perspective, PostgreSQL's supported numeric types consist of:

  • Two, four, and eight byte integers

  • Eight byte floating point numbers

  • Fixed precision decimals

PostgreSQL has support for special types which fall under the family of numeric types, including the deprecated money type, and the special serial construct.

Table 3-12. Numeric Types Overview

Data type

Storage

Range

bigint , int8

8 bytes

Whole integer values, -9223372036854775807 to +9223372036854775807

double precision , float8 , float

8 bytes

15 significant digits, unlimited size (with limited precision)

integer , int , int4

4 bytes

Whole integer values, -2147483648 to +2147483647

numeric(p,s) , decimal (p,s)

Variable

Up to p digits (including to the right of the decimal), with a maximum of s total digits

real , float4

4 bytes

6 significant digits, unlimited size (with limited precision)

smallint , int2

2 bytes

Whole integers, -32768 to +32767

money

4 bytes

Decimal values with a scale of 2 digits to the right of the decimal, -21474836.48 to +21474836.47

serial

4 bytes

Whole integers, 0 to 2147483647

As shown in Table 3-12 , several numeric data types have aliases, which are functionally identical in meaning to their associated data type. If you're not careful, however, they may not behave as you expect them to. For example, the default data type for the float alias is double precision , though you might have been expecting a real , or float4 type. The difference may seem trivial, but storage and precision conditions can be vital in designing table requirements.

Remember that to explicitly change such a type to real , you may use either the SQL type notation, or PostgreSQL-style type casts, as shown in Example 3-23

Example 3-23. Casting double precision to real 

booktown=# SELECT REAL '3.1415' AS real_cast,
booktown-#        CAST('3.1415' AS real) AS real_cast,
booktown-#             '3.1415'::real AS real_cast;
 real_cast | real_cast | real_cast
-----------+-----------+-----------
    3.1415 |    3.1415 |    3.1415
(1 row)
Note bigint/int8 Compatibility
 

The bigint type may not be available on your system, as it depends upon support for eight-byte integers within the compiler used when installing and compiling PostgreSQL. If you are unsure, check with your compiler's vendor for details on its multi-byte integer support.