Numeric Data Types

Numeric data types store a variety of number-related data. The 7.X series of releases has brought some changes to this area. Namely, PostgreSQL now uses a more descriptive naming convention for number-related data types (for example, BIGINT versus INT8).

Recently, some data types have become deprecated over the last few releases. For instance, use of the MONEY data type is no longer encouraged; instead, it is preferential to use the DECIMAL data type.

BIGINT (or INT8)

Description

Holds a very large integer.

Inputs

Large integer (approximately 1 x 10^18)

Storage Size

8 bytes

Notes

Versions of PostgreSQL before 7.1 might refer to this data type as INT8.

DECIMAL (or NUMERIC)

Description

Holds a number of user-defined length with a decimal-width specification.

Inputs

(x,y) 

x—Total length.

y—Decimal width.

Storage Size

8 bytes

Notes

Versions of PostgreSQL before 7.1 might refer to this data type as NUMERIC.

DOUBLE PRECISION (or FLOAT8)

Description

Holds a large floating-point number.

Inputs

Variable precision—15 decimal places.

Storage Size

8 bytes

Notes

Versions of PostgreSQL before 7.1 might refer to this data type as FLOAT8.

INTEGER (or INT4)

Description

Holds an integer.

Inputs

Range from -2,147,483,648 to +2,147,483,648

Storage Size

4 bytes

Notes

Versions of PostgreSQL before 7.1 might refer to this data type as INT4.

REAL (or FLOAT4)

Description

Holds a standard floating-point number.

Inputs

Variable precision up to six decimal places.

Storage Size

4 bytes

Notes

Versions of PostgreSQL before 7.1 might refer to this data type as FLOAT4.

SERIAL

Description

Holds an integer and is used for auto-increment fields.

Inputs

Range from 0 to + 2,147,483,647

Storage Size

4 bytes

Notes

The SERIAL data type is actually just a standard INTEGER type with some additional features: The SERIAL data type is an INTEGER with an automatically created SEQUENCE and INDEX on the specified column. When a table containing a SERIAL type is dropped, the associated SEQUENCE must also be explicitly dropped—it does not occur automatically.

SMALLINT (or INT2)

Description

Holds a small integer.

Inputs

Range from -32,768 to +32,768

Storage Size

2 bytes

Notes

Versions of PostgreSQL before 7.1 might refer to this data type as INT2.