Chapter 8. Data Types

Table of Contents
8.1. Numeric Types
8.1.1. Integer Types
8.1.2. Arbitrary Precision Numbers
8.1.3. Floating-Point Types
8.1.4. Serial Types
8.2. Monetary Types
8.3. Character Types
8.4. Binary Data Types
8.5. Date/Time Types
8.5.1. Date/Time Input
8.5.2. Date/Time Output
8.5.3. Time Zones
8.5.4. Internals
8.6. Boolean Type
8.7. Geometric Types
8.7.1. Points
8.7.2. Line Segments
8.7.3. Boxes
8.7.4. Paths
8.7.5. Polygons
8.7.6. Circles
8.8. Network Address Types
8.8.1. inet
8.8.2. cidr
8.8.3. inet vs. cidr
8.8.4. macaddr
8.9. Bit String Types
8.10. Arrays
8.10.1. Declaration of Array Types
8.10.2. Array Value Input
8.10.3. Accessing Arrays
8.10.4. Modifying Arrays
8.10.5. Searching in Arrays
8.10.6. Array Input and Output Syntax
8.11. Composite Types
8.11.1. Declaration of Composite Types
8.11.2. Composite Value Input
8.11.3. Accessing Composite Types
8.11.4. Modifying Composite Types
8.11.5. Composite Type Input and Output Syntax
8.12. Object Identifier Types
8.13. Pseudo-Types
8.14. XML Document Support

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

bigintint8signed eight-byte integer
bigserialserial8autoincrementing eight-byte integer
bit [ (n) ] fixed-length bit string
bit varying [ (n) ]varbitvariable-length bit string
booleanboollogical 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 precisionfloat8double precision floating-point number
inet IPv4 or IPv6 host address
integerint, int4signed 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
realfloat4single precision floating-point number
smallintint2signed two-byte integer
serialserial4autoincrementing four-byte integer
text variable-length character string
time [ (p) ] [ without time zone ] time of day
time [ (p) ] with time zonetimetztime of day, including time zone
timestamp [ (p) ] [ without time zone ] date and time
timestamp [ (p) ] with time zonetimestamptzdate 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.