PostgreSQL has a rich set of native data types available to users. Users can also add new types to PostgreSQL using the CREATE TYPE command.
Table 2-1 shows all general-purpose data types available to users. Most of the alternative names listed in the "Aliases" column are the names used internally by PostgreSQL for historical reasons. In addition, some internally used or deprecated types are available, but they are not documented here. Many of the built-in types have obvious external formats. However, several types are either unique to PostgreSQL, such as open and closed paths, or have several possibilities for formats, such as the date and time types.
Table 2-1. Data Types
Type Name | Aliases | Description |
---|---|---|
bigint | int8 | signed eight-byte integer |
bit | fixed-length bit string | |
bit varying(n) | varbit(n) | variable-length bit string |
boolean | bool | logical Boolean (true/false) |
box | rectangular box in 2D plane | |
character(n) | char(n) | fixed-length character string |
character varying(n) | varchar(n) | variable-length character string |
cidr | IP network address | |
circle | circle in 2D plane | |
date | calendar date (year, month, day) | |
double precision | float8 | double precision floating-point number |
float(p) | real or double precision | Approximate numeric (floating point) with at least the specified precision |
float | float8 | Approximate numeric (floating point) with default precision |
inet | IP host address | |
integer | int, int4 | signed four-byte integer |
serial | autoincrementing four-byte integer | |
interval | general-use time span | |
line | infinite line in 2D plane | |
lseg | line segment in 2D plane | |
macaddr | MAC address | |
numeric(p, s) | decimal(p, s), dec(p, s) | exact numeric with selectable precision and scale |
numeric(p) | decimal(p), dec(p) | exact numeric with selectable precision (scale = 0) |
numeric | decimal, dec | exact numeric with default precision |
oid | object identifier | |
path | open and closed geometric path in 2D plane | |
point | geometric point in 2D plane | |
polygon | closed geometric path in 2D plane | |
real | float4 | single precision floating-point number |
smallint | int2 | signed two-byte integer |
text | variable-length character string | |
time [ without time zone ] | time of day | |
time with time zone | time of day, including time zone | |
timestamp with time zone | date and time, including time zone | |
timestamp [ without time zone ] | date and time |
In the table above:
In the description of numeric, scale refers to the number of digit of the fractional component.
Compatibility | |
---|---|
The following types (or spellings thereof) are specified by SQL: bit, bit varying, boolean, char, character, character varying, varchar, date, double precision, integer, interval, numeric, decimal, real, smallint, time, timestamp (both with or without time zone). |
Most of the input and output functions corresponding to the base types (for example, integers and floating point numbers) do some error-checking. Some of the operators and functions (for example, addition and multiplication) do not perform run-time error-checking as this would adversely affect execution speed. On some systems, for example, the numeric operators for some data types may silently underflow or overflow.
Some of the input and output functions are not invertible. That is, the result of an output function may lose precision when compared to the original input.
Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating point numbers and fixed-precision decimals.
Table 2-2. Numeric Types
Type Name | Storage | Description | Range |
---|---|---|---|
smallint | 2 bytes | Fixed-precision | -32768 to +32767 |
integer | 4 bytes | Usual choice for fixed-precision | -2147483648 to +2147483647 |
bigint | 8 bytes | Very large range fixed-precision | about 18 decimal places |
decimal | variable | User-specified precision | no limit |
float(p) | real or double precision | Approximate numeric (floating point) with at least the specified precision | depends on machine implementation |
float | float8 | Approximate numeric (floating point) with default precision | depends on machine implementation |
numeric | variable | User-specified precision | no limit |
real | 4 bytes | Variable-precision | 6 decimal places |
double precision | 8 bytes | Variable-precision | 15 decimal places |
serial | 4 bytes | Identifier or cross-reference | 0 to +2147483647 |
The syntax of constants for the numeric types is described in the Section called Constants in Chapter 1. The numeric types have a full set of corresponding arithmetic operators and functions. Refer to Chapter 3 for more information.
The bigint type may not be available on all platforms since it relies on compiler support for eight-byte integers.
The serial type is a special type constructed by PostgreSQL from other existing components. It is typically used to create unique identifiers for table entries. Specifying the following:
CREATE TABLE tablename (colname SERIAL); |
CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename (colname integer DEFAULT nextval('tablename_colname_seq'); CREATE UNIQUE INDEX tablename_colname_key on tablename (colname); |
The implicit sequence created for the serial type will not be automatically removed when the table is dropped. |
CREATE TABLE tablename (colname SERIAL); DROP TABLE tablename; CREATE TABLE tablename (colname SERIAL); |