PGBUILTIN(INTRO)
DESCRIPTION
This section describes the data types, functions and oper-
ators available to users in Postgres as it is distributed.
PGBUILTIN TYPES
Built-in types are installed in every database. psql has
a \d command to show these types.
Users may add new types to Postgres using the define type
command described in this manual.
There are some data types defined by SQL/92 syntax which
are mapped directly into native Postgres types. Note that
the "exact numerics" decimal and numeric have fully imple-
mented syntax but currently (postgres v6.2) support only a
limited range of the values allowed by SQL/92.
List of SQL/92 types
POSTGRES Type SQL/92 Type Meaning
char(n) character(n) fixed-length character string
varchar(n) character varying(n) variable-length character string
float4/8 float(p) floating-point number with precision p
float8 double precision double-precision floating-point number
float8 real double-precision floating-point number
int2 smallint signed two-byte integer
int4 int signed 4-byte integer
int4 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
timestamp timestamp with time zone date/time
timespan interval general-use time span
There are some constants and functions defined in SQL/92.
List of SQL/92 constants
SQL/92 Function Meaning
current_date date of current transaction
current_time time of current transaction
current_timestamp date and time of current transaction
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.
Syntax of date and time types
Most date and time types share code for data input. For
those types ( datetime, abstime, timestamp, timespan,
reltime, date, and time) the input can have any of a wide
variety of styles. For numeric date representations, Euro-
pean and US conventions can differ, and the proper inter-
pretation is obtained by using the set(l) command before
entering data. Output formats can be set to one of three
styles: ISO-8601, SQL (traditional Oracle/Ingres), and
traditional Postgres (see section on absolute time) with
the SQL style having European and US variants (see
set(l)).
In future releases, the number of date/time types will
decrease, with the current implementation of datetime
becoming timestamp, timespan becoming interval, and (pos-
sibly) abstime and reltime being deprecated in favor of
timestamp and interval.
DATETIME
General-use date and time is input using a wide range of
styles, including ISO-compatible, SQL-compatible, tradi-
tional 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.
The special values `current', `infinity' and `-infinity'
are provided. `infinity' specifies a time later than any
valid time, and `-infinity' specifies a time earlier than
any 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 time, and differs from `current' in that the
current time is immediately substituted for it. `epoch'
means Jan 1 00:00:00 1970 GMT.
TIMESPAN
General-use time span is input using a wide range of syn-
taxes, including ISO-compatible, SQL-compatible, tradi-
tional Postgres (see section on relative time ) and other
permutations of time span. Output formats can be ISO-com-
patible, SQL-compatible, or traditional Postgres, with the
default set to be Postgres-compatible. Months and years
are a "qualitative" time interval, and are stored sepa-
rately 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',
or abbreviations or plurals of these units.
Direction is `ago'.
ABSOLUTE TIME
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. 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.
RELATIVE TIME
Relative time (reltime) is a limited-range (+/- 68 years)
and limited-precision (1 sec) time span data type. times-
pan may be preferred, since it covers a larger range with
greater precision, allows multiple units for an entry, and
correctly handles qualitative time units such as year and
month. For reltime, only one quantity and unit is allowed
per entry, which can be inconvenient for complicated time
spans.
Relative time is specified with the following syntax:
@ Quantity Unit [Direction]
where
Quantity is `1', `2', ...
Unit is ``second'', ``minute'', ``hour'', ``day'', ``week'',
``month'' (30-days), or ``year'' (365-days),
or PLURAL of these units.
Direction is ``ago''
(Note: Valid relative times are less than or equal to 68
years.) In addition, the special relative time "Undefined
RelTime" is provided.
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 date-
time.
TIME RANGES
Time ranges are specified as:
[ 'abstime' 'abstime']
where abstime is a time in the absolute time format. Spe-
cial abstime values such as "current", "infinity" and
"-infinity" can be used.
Syntax of geometric types
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
LSEG
Line segments 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 oppo-
site 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 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 sup-
plied 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 Post-
gres 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 sup-
port 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. The last for-
mat is supplied to be backward compatible with v6.0 and
earlier path formats and will not be supported in future
versions of Postgres. a single leading "(" indicates a
v6.0-compatible format ( x1 , ... , xn , y1 , ... , yn )
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 coordi-
nates, 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.
Built-in operators and functions
OPERATORS
Postgres provides a large number of built-in operators on
system types. These operators are declared in the system
catalog "pg_operator". Every entry in "pg_operator"
includes the object ID of the procedure that implements
the operator.
Users may invoke operators using the operator name, as in:
select * from emp where salary < 40000;
Alternatively, users may call the functions that implement
the operators directly. In this case, the query above
would be expressed as:
select * from emp where int4lt(salary, 40000);
psql has a \d command to show these operators.
FUNCTIONS
Many data types have functions available for conversion to
other related types. In addition, there are some type-
specific functions. Functions which are also available
through operators are documented as operators only.
Some functions defined for text are also available for
char() and varchar().
For the date_part() and date_trunc() functions, arguments
can be `year', `month', `day', `hour', `minute', and `sec-
ond', as well as the more specialized quantities `decade',
`century', `millenium', `millisecond', and `microsecond'.
date_part() allows `dow' to return day of week and `epoch'
to return seconds since 1970 for datetime and 'epoch' to
return total elapsed seconds for timespan.
Functions:
integer
float8 float(int) convert integer to floating point
float4 float4(int) convert integer to floating point
float
int integer(float) convert floating point to integer
text
text lower(text) convert text to lower case
text lpad(text,int,text) left pad string to specified length
text ltrim(text,text) left trim characters from text
text position(text,text) extract specified substring
text rpad(text,int,text) right pad string to specified length
text rtrim(text,text) right trim characters from text
text substr(text,int[,int]) extract specified substring
text upper(text) convert text to upper case
abstime
bool isfinite(abstime) TRUE if this is a finite time
datetime datetime(abstime) convert to datetime
date
datetime datetime(date) convert to datetime
datetime datetime(date,time) convert to datetime
datetime
timespan age(datetime,datetime) date difference preserving months and years
float8 date_part(text,datetime) specified portion of date field
datetime date_trunc(text,datetime) truncate date at specified units
bool isfinite(datetime) TRUE if this is a finite time
abstime abstime(datetime) convert to abstime
reltime
timespan timespan(reltime) convert to timespan
time
datetime datetime(date,time) convert to datetime
timespan
float8 date_part(text,timespan) specified portion of time field
bool isfinite(timespan) TRUE if this is a finite time
reltime reltime(timespan) convert to reltime
box
box box(point,point) convert points to box
float8 area(box) area of box
path
bool isopen(path) TRUE if this is an open path
bool isclosed(path) TRUE if this is a closed path
circle
circle circle(point,float8) convert to circle
polygon polygon(npts,circle) convert to polygon with npts points
float8 center(circle) radius of circle
float8 radius(circle) radius of circle
float8 diameter(circle) diameter of circle
float8 area(circle) area of circle
SQL/92 defines functions with specific syntax. Some of
these are implemented using other Postgres functions.
SQL/92 Functions:
text
text position(text in text) extract specified substring
text substring(text [from int] [for int])
extract specified substring
text trim([leading|trailing|both] [text] from text)
trim characters from text
ADDITIONAL INFORMATION
psql has a variety of \d commands for showing system
information. Consult those psql commands for more list-
ings.
SEE ALSO
set(l) show(l) reset(l) psql(1). For examples on spec-
ifying literals of built-in types, see SQL(l).
BUGS
Although most of the input and output functions corre-
sponding to the base types (e.g., integers and floating
point numbers) do some error-checking, some are not par-
ticularly rigorous about it. More importantly, few of the
operators and functions (e.g., addition and multiplica-
tion) perform any error-checking at all. Consequently,
many of the numeric operators can (for example) silently
underflow or overflow.
Some of the input and output functions are not invertible.
That is, the result of an output function may lose preci-
sion when compared to the original input.