This page in other versions: 8.4 / 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel / 9.4  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3

Chapter 8. Data Types

PostgreSQL has a rich set of native data types available to users. Users may add new types to PostgreSQL using the CREATE TYPE command.

Table 8-1 shows all the built-in general-purpose data types. 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 listed here.

Table 8-1. Data Types

Name Aliases Description
bigint int8 signed eight-byte integer
bigserial serial8 autoincrementing eight-byte integer
bit [ (n) ]   fixed-length bit string
bit varying [ (n) ] varbit variable-length bit string
boolean bool logical Boolean (true/false)
box   rectangular box in the plane
bytea   binary data ("byte array")
character varying [ (n) ] varchar [ (n) ] variable-length character string
character [ (n) ] char [ (n) ] fixed-length character string
cidr   IPv4 or IPv6 network address
circle   circle in the plane
date   calendar date (year, month, day)
double precision float8 double precision floating-point number
inet   IPv4 or IPv6 host address
integer int, int4 signed four-byte integer
interval [ (p) ]   time span
line   infinite line in the plane
lseg   line segment in the plane
macaddr   MAC address
money   currency amount
numeric [ (p, s) ] decimal [ (p, s) ] exact numeric of selectable precision
path   geometric path in the plane
point   geometric point in the plane
polygon   closed geometric path in the plane
real float4 single precision floating-point number
smallint int2 signed two-byte integer
serial serial4 autoincrementing four-byte integer
text   variable-length character string
time [ (p) ] [ without time zone ]   time of day
time [ (p) ] with time zone timetz time of day, including time zone
timestamp [ (p) ] [ without time zone ]   date and time
timestamp [ (p) ] with time zone timestamptz date and time, including time zone

Compatibility: The following types (or spellings thereof) are specified by SQL: bit, bit varying, boolean, char, character varying, character, varchar, date, double precision, integer, interval, numeric, decimal, real, smallint, time (with or without time zone), timestamp (with or without time zone).

Each data type has an external representation determined by its input and output functions. Many of the built-in types have obvious external formats. However, several types are either unique to PostgreSQL, such as geometric paths, or have several possibilities for formats, such as the date and time types. Some of the input and output functions are not invertible. That is, the result of an output function may lose accuracy when compared to the original input.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group