Data Types
Describes the built-in data types available in
Postgres.
Postgres has a rich set of native data
types available to users.
Users may add new types to Postgres using the
define type
command described elsewhere.
In the context of data types, the following sections will discuss
SQL standards compliance, porting issues, and usage.
Some Postgres types correspond directly to
SQL92-compatible types. In other
cases, data types defined by SQL92 syntax are mapped directly
into native Postgres types.
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 date and time types.
Postgres Data Types
Data Types
Postgres Type
SQL92 or SQL3 Type
Description
bool
boolean
logical boolean (true/false)
box
rectangular box in 2D plane
char(n)
character(n)
fixed-length character string
cidr
IP version 4 network or host address
circle
circle in 2D plane
date
date
calendar date without time of day
float4/8
float(p)
floating-point number with precision p
float8
real, double precision
double-precision floating-point number
inet
IP version 4 network or host address
int2
smallint
signed two-byte integer
int4
int, integer
signed 4-byte integer
int4
decimal(p,s)
exact numeric for p <= 9, s = 0
int4
numeric(p,s)
exact numeric for p == 9, s = 0
int8
signed 8-byte integer
line
infinite line in 2D plane
lseg
line segment in 2D plane
money
decimal(9,2)
US-style currency
path
open and closed geometric path in 2D plane
point
geometric point in 2D plane
polygon
closed geometric path in 2D plane
serial
unique id for indexing and cross-reference
time
time
time of day
timespan
interval
general-use time span
timestamp
timestamp with time zone
date/time
varchar(n)
character varying(n)
variable-length character string
The cidr and inet types are designed to handle any IP type
but only ipv4 is handled in the current implementation.
Everything here that talks about ipv4 will apply to ipv6 in a future release.
Postgres Function Constants
Constants
Postgres Function
SQL92 Constant
Description
getpgusername()
current_user
user name in current session
date('now')
current_date
date of current transaction
time('now')
current_time
time of current transaction
timestamp('now')
current_timestamp
date and time of current transaction
Postgres has features at the forefront of
ORDBMS development. In addition to
SQL3 conformance, substantial portions
of SQL92 are also supported.
Although we strive for SQL92 compliance,
there are some aspects of the standard
which are ill considered and which should not live through subsequent standards.
Postgres will not make great efforts to
conform to these features; however, these tend to apply in little-used
or obsure cases, and a typical user is not likely to run into them.
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.
Note that 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.
The original Postgres v4.2 code received from
Berkeley rounded all double precision floating point results to six digits for
output. Starting with v6.1, floating point numbers are allowed to retain
most of the intrinsic precision of the type (typically 15 digits for doubles,
6 digits for 4-byte floats).
Other types with underlying floating point fields (e.g. geometric
types) carry similar precision.
Numeric Types
Numeric types consist of two- and four-byte integers and four- and eight-byte
floating point numbers.
Postgres Numeric Types
Numerics
Numeric Type
Storage
Description
Range
float4
4 bytes
Variable-precision
6 decimal places
float8
8 bytes
Variable-precision
15 decimal places
int2
2 bytes
Fixed-precision
-32768 to +32767
int4
4 bytes
Usual choice for fixed-precision
-2147483648 to +2147483647
int8
8 bytes
Very large range fixed-precision
+/- > 18 decimal places
serial
4 bytes
Identifer or cross-reference
0 to +2147483647
The numeric types have a full set of corresponding arithmetic operators and
functions. Refer to
and for more information.
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 INT4 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.
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.
The exact numerics decimal and
numeric
have fully implemented syntax but currently
(Postgres v6.4)
support only a small range of precision and/or range values.
The int8 type may not be available on all platforms since
it relies on compiler support for this.
Monetary Type
The money type supports US-style currency with
fixed decimal point representation.
If Postgres is compiled with USE_LOCALE
then the money type
should use the monetary conventions defined for
locale(7).
Postgres Numeric Types
Numerics
Monetary Type
Storage
Description
Range
money
4 bytes
Fixed-precision
-21474836.48 to +21474836.47
numeric
should eventually replace the money type. It has a
fully implemented syntax but currently
(Postgres v6.4)
support only a small range of precision and/or range values
and cannot adequately substitute for the money type.
Character Types
SQL92 defines two primary character types:
char and varchar.
Postgres supports these types, in
addition to the more general text type,
which unlike varchar
does not require an upper
limit to be declared on the size of the field.
Postgres Character Types
Characters
Character Type
Storage
Recommendation
Description
char
1 byte
SQL92-compatible
Single character
char(n)
(4+n) bytes
SQL92-compatible
Fixed-length blank padded
text
(4+x) bytes
Best choice
Variable-length
varchar(n)
(4+n) bytes
SQL92-compatible
Variable-length with limit
There is one other fixed-length character type.
The name type
only has one purpose and that is to provide
Postgres with a
special type to use for internal names.
It is not intended for use by the general user.
It's length is currently defined as 32 chars
but should be reference using NAMEDATALEN.
This is set at compile time and may change in a future release.
Postgres Specialty Character Type
Specialty Characters
Character Type
Storage
Description
name
32 bytes
Thirty-two character internal type
Date/Time Types
There are two fundamental kinds of date and time measurements:
absolute clock times and relative time intervals.
Both quantities should demonstrate continuity and smoothness, as does time itself.
Postgres supplies two primary user-oriented
date and time types,
datetime and timespan, as well as
the related SQL92 types timestamp,
interval,
date and time.
In a future release, datetime and timespan are likely
to merge with the SQL92 types timestamp,
interval.
Other date and time types are also available, mostly
for historical reasons.
Postgres Date/Time Types
Date/Time
Date/Time Type
Storage
Recommendation
Description
abstime
4 bytes
original date and time
limited range
date
4 bytes
SQL92 type
wide range
datetime
8 bytes
best general date and time
wide range, high precision
interval
12 bytes
SQL92 type
equivalent to timespan
reltime
4 bytes
original time interval
limited range, low precision
time
4 bytes
SQL92 type
wide range
timespan
12 bytes
best general time interval
wide range, high precision
timestamp
4 bytes
SQL92 type
limited range
timestamp is currently implemented separately from
datetime, although they share input and output routines.
Postgres Date/Time Ranges
Ranges
Date/Time Type
Earliest
Latest
Resolution
abstime
1901-12-14
2038-01-19
1 sec
date
4713 BC
32767 AD
1 day
datetime
4713 BC
1465001 AD
1 microsec to 14 digits
interval
-178000000 years
178000000 years
1 microsec
reltime
-68 years
+68 years
1 sec
time
00:00:00.00
23:59:59.99
1 microsec
timespan
-178000000 years
178000000 years
1 microsec (14 digits)
timestamp
1901-12-14
2038-01-19
1 sec
Postgres endevours to be compatible with
SQL92 definitions for typical usage.
The SQL92 standard has an odd mix of date and
time types and capabilities. Two obvious problems are:
Although the date type
does not have an associated time zone, the
time type can or does.
The default time zone is specified as a constant integer offset
from GMT/UTC.
However, time zones in the real world can have no meaning unless
associated with a date as well as a time
since the offset may vary through the year with daylight savings
time boundaries.
To address these difficulties, Postgres
associates time zones only with date and time
types which contain both date and time,
and assumes local time for any type containing only
date or time. Further, time zone support is derived from
the underlying operating system
time zone capabilities, and hence can handle daylight savings time
and other expected behavior.
In future releases, the number of date/time types will decrease,
with the current implementation of
datetime becoming timestamp,
timespan becoming interval,
and (possibly) abstime and reltime
being deprecated in favor of timestamp and interval.
The more arcane features of the date/time definitions from
the SQL92 standard are not likely to be pursued.
Date/Time Styles
Output formats can be set to one of four styles:
ISO-8601, SQL (Ingres), traditional
Postgres, and German.
Postgres Date Styles
Styles
Style Specification
Description
Example
ISO
ISO-8601 standard
1997-12-17 07:37:16-08
SQL
Traditional style
12/17/1997 07:37:16.00 PST
Postgres
Original style
Wed Dec 17 07:37:16 1997 PST
German
Regional style
17.12.1997 07:37:16.00 PST
The SQL style has European and non-European (US) variants,
which determines whether month follows day or vica versa.
Postgres Date Order Conventions
Order
Style Specification
Description
Example
European
Regional convention
17/12/1997 15:37:16.00 MET
NonEuropean
Regional convention
12/17/1997 07:37:16.00 PST
US
Regional convention
12/17/1997 07:37:16.00 PST
There are several ways to affect the appearance of date/time types:
The PGDATESTYLE environment variable used by the backend directly
on postmaster startup.
The PGDATESTYLE environment variable used by the frontend libpq
on session startup.
SET DateStyle SQL command.
For Postgres v6.4 (and earlier)
the default date/time style is
"non-European traditional Postgres".
In future releases, the default may become ISO-8601, which alleviates
date specification ambiguities and Y2K collation problems.
Time Zones
Postgres obtains time zone support
from the underlying operating system.
All dates and times are stored internally in Universal Coordinated Time (UTC),
alternately known as Greenwich Mean Time (GMT).
Times are converted to local time on the database server before being
sent to the client frontend, hence by default are in the server time zone.
There are several ways to affect the time zone behavior:
The TZ environment variable used by the backend directly
on postmaster startup as the default time zone.
The PGTZ environment variable set at the client used by libpq
to send time zone information to the backend upon connection.
The SQL command SET TIME ZONE
sets the time zone for the session.
If an invalid time zone is specified,
the time zone becomes GMT (on most systems anyway).
Date/Time Input
General-use date and time is input using a wide range of
styles, including ISO-compatible, SQL-compatible,
traditional Postgres
and other permutations of date and time. In cases where interpretation
can be ambiguous (quite possible with many traditional styles of date
specification) Postgres uses a style setting
to resolve the ambiguity.
Most date and time types share code for data input. For those types
the input can have any of a wide variety of styles. For numeric date
representations,
European and US conventions can differ, and the proper interpretation
is obtained
by using the
set datestyle
command before entering data.
Note that the style setting does not preclude use of various styles for input;
it is
used primarily to determine the output style and to resolve ambiguities.
The special values `current',
`infinity' and `-infinity' are provided.
`infinity' specifies a time later than any other valid time, and
`-infinity' specifies a time earlier than any other valid time.
`current' indicates that the current time should be
substituted whenever this value appears in a computation.
The strings
`now',
`today',
`yesterday',
`tomorrow',
and `epoch' can be used to specify
time values. `now' means the current transaction time, and differs from
`current' in that the current time is immediately substituted
for it. `epoch' means Jan 1 00:00:00 1970 GMT.
Postgres Date/Time Special Constants
Constants
Constant
Description
current
Current transaction time, deferred
epoch
1970-01-01 00:00:00+00 (Unix system time zero)
infinity
Later than other valid times
-infinity
Earlier than other valid times
invalid
Illegal entry
now
Current transaction time
today
Midnight today
tomorrow
Midnight tomorrow
yesterday
Midnight yesterday
datetime
General-use date and time is input using a wide range of
styles, including ISO-compatible, SQL-compatible, traditional
Postgres (see section on "absolute time")
and other permutations of date and time. Output styles can be ISO-compatible,
SQL-compatible, or traditional
Postgres, with the default set to be compatible
with Postgres v6.0.
datetime is specified using the following syntax:
Year-Month-Day [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
YearMonthDay [ Hour : Minute : Second ] [AD,BC] [ Timezone ]
Month Day [ Hour : Minute : Second ] Year [AD,BC] [ Timezone ]
where
Year is 4013 BC, ..., very large
Month is Jan, Feb, ..., Dec or 1, 2, ..., 12
Day is 1, 2, ..., 31
Hour is 00, 02, ..., 23
Minute is 00, 01, ..., 59
Second is 00, 01, ..., 59 (60 for leap second)
Timezone is 3 characters or ISO offset to GMT
Valid dates are from Nov 13 00:00:00 4013 BC GMT to far into the future.
Timezones are either three characters (e.g. "GMT" or "PST") or ISO-compatible
offsets to GMT (e.g. "-08" or "-08:00" when in Pacific Standard Time).
Dates are stored internally in Greenwich Mean Time. Input and output routines
translate time to the local time zone of the server.
timespan
General-use time span is input using a wide range of
syntaxes, including ISO-compatible, SQL-compatible,
traditional
Postgres (see section on "relative time")
and other permutations of time span. Output formats can be ISO-compatible,
SQL-compatible, or traditional
Postgres,
with the default set to be Postgres-compatible.
Months and years are a "qualitative" time interval, and are stored separately
from the other "quantitative" time intervals such as day or hour.
For date arithmetic,
the qualitative time units are instantiated in the context of the
relevant date or time.
Time span is specified with the following syntax:
Quantity Unit [Quantity Unit...] [Direction]
@ Quantity Unit [Direction]
where
Quantity is ..., `-1', `0', `1', `2', ...
Unit is `second', `minute', `hour', `day', `week', `month', `year',
'decade', 'century', millenium', or abbreviations or plurals of these units.
Direction is `ago'.
abstime
Absolute time (abstime) is a limited-range (+/- 68 years) and
limited-precision (1 sec)
date data type. datetime may be preferred, since it
covers a larger range with greater precision.
Absolute time is specified using the following syntax:
Month Day [ Hour : Minute : Second ] Year [ Timezone ]
where
Month is Jan, Feb, ..., Dec
Day is 1, 2, ..., 31
Hour is 01, 02, ..., 24
Minute is 00, 01, ..., 59
Second is 00, 01, ..., 59
Year is 1901, 1902, ..., 2038
Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04
2038 GMT.
Historical Note
As of Version 3.0, times are no longer read and written
using Greenwich Mean Time; the input and output routines default to
the local time zone.
All special values allowed for datetime are also
allowed for "absolute time".
reltime
Relative time reltime is a limited-range (+/- 68 years)
and limited-precision (1 sec) time span data type.
timespan should be preferred, since it
covers a larger range with greater precision and, more importantly,
can distinguish between
relative units (months and years) and quantitative units (days, hours, etc).
Instead, reltime
must force months to be exactly 30 days, so time arithmetic does not
always work as expected.
For example, adding one reltime year to abstime today does not
produce today's date one year from
now, but rather a date 360 days from today.
reltime shares input and output routines with the other
time span types.
The section on timespan covers this in more detail.
timestamp
This is currently a limited-range absolute time which closely resembles the
abstime
data type. It shares the general input parser with the other date/time types.
In future releases this type will absorb the capabilities of the
datetime type
and will move toward SQL92 compliance.
timestamp is specified using the same syntax as for
datetime.
interval
interval is an SQL92 data type which is
currently mapped to the timespan
Postgres data type.
tinterval
Time ranges are specified as:
[ 'abstime' 'abstime']
where
abstime is a time in the absolute time format.
Special abstime values such as
`current', `infinity' and `-infinity' can be used.
Boolean Type
Postgres supports bool as
the SQL3 boolean type.
bool can have one of only two states: 'true' or 'false'.
A third state, 'unknown', is not
implemented and is not suggested in SQL3;
NULL is an
effective substitute. bool can be used in any boolean expression,
and boolean expressions
always evaluate to a result compatible with this type.
bool uses 4 bytes of storage.
Postgres Boolean Type
Booleans
State
Output
Input
True
't'
TRUE, 't', 'true', 'y', 'yes', '1'
False
'f'
FALSE, 'f', 'false', 'n', 'no', '0'
Geometric Types
Geometric types represent two-dimensional spatial objects.
The most fundamental type,
the point, forms the basis for all of the other types.
Postgres Geometric Types
Geometrics
Geometric Type
Storage
Representation
Description
point
16 bytes
(x,y)
Point in space
line
32 bytes
((x1,y1),(x2,y2))
Infinite line
lseg
32 bytes
((x1,y1),(x2,y2))
Finite line segment
box
32 bytes
((x1,y1),(x2,y2))
Rectangular box
path
4+32n bytes
((x1,y1),...)
Closed path (similar to polygon)
path
4+32n bytes
[(x1,y1),...]
Open path
polygon
4+32n bytes
((x1,y1),...)
Polygon (similar to closed path)
circle
24 bytes
<(x,y),r>
Circle (center and radius)
A rich set of functions and operators is available to perform various geometric
operations such as scaling, translation, rotation, and determining
intersections.
Point
Points are specified using the following syntax:
( x , y )
x , y
where
x is the x-axis coordinate as a floating point number
y is the y-axis coordinate as a floating point number
Line Segment
Line segments (lseg) are represented by pairs of points.
lseg is specified using the following syntax:
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
where
(x1,y1) and (x2,y2) are the endpoints of the segment
Box
Boxes are represented by pairs of points which are opposite
corners of the box.
box is specified using the following syntax:
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
where
(x1,y1) and (x2,y2) are opposite corners
Boxes are output using the first syntax.
The corners are reordered on input to store
the lower left corner first and the upper right corner last.
Other corners of the box can be entered, but the lower
left and upper right corners are determined from the input and stored.
Path
Paths are represented by connected sets of points. Paths can be "open", where
the first and last points in the set are not connected, and "closed",
where the first and last point are connected. Functions
popen(p)
and
pclose(p)
are supplied to force a path to be open or closed, and functions
isopen(p)
and
isclosed(p)
are supplied to select either type in a query.
path is specified using the following syntax:
( ( x1 , y1 ) , ... , ( xn , yn ) )
[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
where
(x1,y1),...,(xn,yn) are points 1 through n
a leading "[" indicates an open path
a leading "(" indicates a closed path
Paths are output using the first syntax.
Note that Postgres versions prior to
v6.1 used a format for paths which had a single leading parenthesis,
a "closed" flag,
an integer count of the number of points, then the list of points followed by a
closing parenthesis.
The built-in function upgradepath is supplied to convert
paths dumped and reloaded from pre-v6.1 databases.
Polygon
Polygons are represented by sets of points. Polygons should probably be
considered equivalent to closed paths, but are stored differently
and have their own set of support routines.
polygon is specified using the following syntax:
( ( x1 , y1 ) , ... , ( xn , yn ) )
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
where
(x1,y1),...,(xn,yn) are points 1 through n
Polygons are output using the first syntax.
Note that Postgres versions prior to
v6.1 used a format for polygons which had a single leading parenthesis, the list
of x-axis coordinates, the list of y-axis coordinates,
followed by a closing parenthesis.
The built-in function upgradepoly is supplied to convert
polygons dumped and reloaded from pre-v6.1 databases.
Circle
Circles are represented by a center point and a radius.
circle is specified using the following syntax:
< ( x , y ) , r >
( ( x , y ) , r )
( x , y ) , r
x , y , r
where
(x,y) is the center of the circle
r is the radius of the circle
Circles are output using the first syntax.
IP Version 4 Networks and Host Addresses
The cidr type stores networks specified
in CIDR notation.
The inet type stores hosts and networks in CIDR notation.
PostgresIP Version 4 Type
IPV4
IPV4 Type
Storage
Description
Range
cidr
variable
CIDR networks
Valid IPV4 CIDR blocks
inet
variable
nets and hosts
Valid IPV4 CIDR blocks
inet for IP Networks
The cidr type holds a CIDR network.
The format for specifying networks is "x.x.x.x/y" where "x.x.x.x" is the
network and "/y" is the number of bits in the netmask.
If the "/y" part is left off, it is calculated using assumptions from
the old class system except that it is extended to include at least
all of the octets in the input.
Here are some examples.
Input
Output
select '192.168.1'::cidr
192.168.1/24
select '192.168'::cidr
192.168.0/24
select '128.1'::cidr
128.1/16
select '128':::cidr
128.0/16
select '128.1.2'::cidr
128.1.2/24
select '10.1.2'::cidr
10.1.2/24
select '10.1'::cidr
10.1/16
select '10'::cidr
10/8
inet for IP Networks
The inet type is designed to hold, in one field, all of the information
about a host including the CIDR style subnet that it is in.
Note that if you want to store proper CIDR networks, see the cidr type.
The inet type is similar to the cidr type except that the bits in the
host part can be non-zero.
Functions exist to extract the various elements of the field.
The input format for this function is "x.x.x.x/y" where "x.x.x.x" is
an internet host and y is the number of bits in the netmask.
If the "/y" part is left off, it is treated as "/32."
On output, the "/y" part is not printed if it is /32.
This allows the type to be used as a straight host type by just leaving of
the bits part.