This page in other versions: 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 / 8.4

Chapter 3. Data Types

Table of Contents
3.1. Numeric Types
3.2. Monetary Type
3.3. Character Types
3.4. Date/Time Types
3.4.1. Date/Time Input
3.4.2. Date/Time Output
3.4.3. Time Zones
3.4.4. Internals
3.5. Boolean Type
3.6. Geometric Types
3.6.1. Point
3.6.2. Line Segment
3.6.3. Box
3.6.4. Path
3.6.5. Polygon
3.6.6. Circle
3.7. Network Address Data Types
3.7.1. inet
3.7.2. cidr
3.7.3. inet vs cidr
3.7.4. macaddr
3.8. Bit String Types

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

Table 3-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 Postgres 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 Postgres, such as open and closed paths, or have several possibilities for formats, such as the date and time types.

Table 3-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
inet   IP host address
integer int, int4 signed four-byte integer
interval   general-use time span
line   infinite line in 2D plane
lseg   line segment in 2D plane
macaddr   MAC address
money   US-style currency
numeric(p, s) decimal(p, s) exact numeric with selectable 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
serial   autoincrementing four-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

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 (e.g., integers and floating point numbers) do some error-checking. Some of the operators and functions (e.g., addition and multiplication) do not perform run-time error-checking in the interests of improving 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.

3.1. Numeric Types

Numeric types consist of two-, four-, and eight-byte integers, four- and eight-byte floating point numbers and fixed-precision decimals.

Table 3-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
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 Section 1.1.2. The numeric types have a full set of corresponding arithmetic operators and functions. Refer to Chapter 4 for more information.

The bigint type may not be available on all platforms since it relies on compiler support for eight-byte integers.

3.1.1. The Serial Type

The serial type is a special-case type constructed by Postgres from other existing components. It is typically used to create unique identifiers for table entries. In the current implementation, specifying

CREATE TABLE tablename (colname SERIAL);
     
is equivalent to specifying:
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename
    (colname integer DEFAULT nextval('tablename_colname_seq');
CREATE UNIQUE INDEX tablename_colname_key on tablename (colname);
     
Caution

The implicit sequence created for the serial type will not be automatically removed when the table is dropped.

Implicit sequences supporting the serial are not automatically dropped when a table containing a serial type is dropped. So, the following commands executed in order will likely fail:
CREATE TABLE tablename (colname SERIAL);
DROP TABLE tablename;
CREATE TABLE tablename (colname SERIAL);
     
The sequence will remain in the database until explicitly dropped using DROP SEQUENCE.
Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group