Functions
Describes the built-in functions available in Postgres.
Many data types have functions available for conversion to other related types.
In addition, there are some type-specific functions. Some functions are also
available through operators and may be documented as operators only.
Mathematical Functions
Mathematical Functions
Function
Returns
Description
Example
dexp(float8)
float8
raise e to the specified exponent
dexp(2.0)
dpow(float8,float8)
float8
raise a number to the specified exponent
dpow(2.0, 16.0)
float(int)
float8
convert integer to floating point
float(2)
float4(int)
float4
convert integer to floating point
float4(2)
integer(float)
int
convert floating point to integer
integer(2.0)
String Functions
SQL92 defines string functions with specific syntax. Some of these
are implemented using other Postgres functions.
SQL92 String Functions
Function
Returns
Description
Example
position(text in text)
int4
location of specified substring
position('o' in 'Tom')
substring(text [from int] [for int])
text
extract specified substring
substring('Tom' from 2 for 2)
trim([leading|trailing|both] [text] from text)
text
trim characters from text
trim(both 'x' from 'xTomx')
Many string functions are available for text, varchar(), and char() types.
Some are used internally to implement the SQL92 string functions listed above.
String Functions
Function
Returns
Description
Example
char(text)
char
convert text to char type
char('text string')
char(varchar)
char
convert varchar to char type
char(varchar 'varchar string')
initcap(text)
text
first letter of each word to upper case
initcap('thomas')
lower(text)
text
convert text to lower case
lower('TOM')
lpad(text,int,text)
text
left pad string to specified length
lpad('hi',4,'??')
ltrim(text,text)
text
left trim characters from text
ltrim('xxxxtrim','x')
position(text,text)
text
extract specified substring
position('high','ig')
rpad(text,int,text)
text
right pad string to specified length
rpad('hi',4,'x')
rtrim(text,text)
text
right trim characters from text
rtrim('trimxxxx','x')
substr(text,int[,int])
text
extract specified substring
substr('hi there',3,5)
text(char)
text
convert char to text type
text('char string')
text(varchar)
text
convert varchar to text type
text(varchar 'varchar string')
translate(text,from,to)
text
convert character in string
translate('12345', '1', 'a')
varchar(char)
varchar
convert char to varchar type
varchar('char string')
varchar(text)
varchar
convert text to varchar type
varchar('text string')
upper(text)
text
convert text to upper case
upper('tom')
Most functions explicitly defined for text will work for char() and varchar() arguments.
Date/Time Functions
The date/time functions provide a powerful set of tools for manipulating various date/time types.
Date/Time Functions
Function
Returns
Description
Example
abstime(datetime)
abstime
convert to abstime
abstime('now'::datetime)
age(datetime,datetime)
timespan
span preserving months and years
age('now','1957-06-13'::datetime)
datetime(abstime)
datetime
convert to datetime
datetime('now'::abstime)
datetime(date)
datetime
convert to datetime
datetime('today'::date)
datetime(date,time)
datetime
convert to datetime
datetime('1998-02-24'::datetime, '23:07'::time);
date_part(text,datetime)
float8
specified portion of date field
date_part('dow','now'::datetime)
date_part(text,timespan)
float8
specified portion of time field
date_part('hour','4 hrs 3 mins'::timespan)
date_trunc(text,datetime)
datetime
truncate date at specified units
date_trunc('month','now'::abstime)
isfinite(abstime)
bool
TRUE if this is a finite time
isfinite('now'::abstime)
isfinite(datetime)
bool
TRUE if this is a finite time
isfinite('now'::datetime)
isfinite(timespan)
bool
TRUE if this is a finite time
isfinite('4 hrs'::timespan)
reltime(timespan)
reltime
convert to reltime
reltime('4 hrs'::timespan)
timespan(reltime)
timespan
convert to timespan
timespan('4 hours'::reltime)
For the
date_part and date_trunc
functions, arguments can be
`year', `month', `day', `hour', `minute', and `second',
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)
or 'epoch' to return total elapsed seconds (for timespan).
Geometric Functions
The geometric types point, box, lseg, line, path, polygon, and circle have a large set of native
support functions.
Geometric Functions
Function
Returns
Description
Example
area(box)
float8
area of box
area('((0,0),(1,1))'::box)
area(circle)
float8
area of circle
area('((0,0),2.0)'::circle)
box(box,box)
box
boxes to intersection box
box('((0,0),(1,1))','((0.5,0.5),(2,2))')
center(box)
point
center of object
center('((0,0),(1,2))'::box)
center(circle)
point
center of object
center('((0,0),2.0)'::circle)
diameter(circle)
float8
diameter of circle
diameter('((0,0),2.0)'::circle)
height(box)
float8
vertical size of box
height('((0,0),(1,1))'::box)
isclosed(path)
bool
TRUE if this is a closed path
isclosed('((0,0),(1,1),(2,0))'::path)
isopen(path)
bool
TRUE if this is an open path
isopen('[(0,0),(1,1),(2,0)]'::path)
length(lseg)
float8
length of line segment
length('((-1,0),(1,0))'::lseg)
length(path)
float8
length of path
length('((0,0),(1,1),(2,0))'::path)
pclose(path)
path
convert path to closed variant
popen('[(0,0),(1,1),(2,0)]'::path)
point(lseg,lseg)
point
convert to point (intersection)
point('((-1,0),(1,0))'::lseg,'((-2,-2),(2,2))'::lseg)
points(path)
int4
number of points in path
points('[(0,0),(1,1),(2,0)]'::path)
popen(path)
path
convert path to open variant
popen('((0,0),(1,1),(2,0))'::path)
radius(circle)
float8
radius of circle
radius('((0,0),2.0)'::circle)
width(box)
float8
horizontal size of box
width('((0,0),(1,1))'::box)
Geometric Type Conversion Functions
Function
Returns
Description
Example
box(circle)
box
convert circle to box
box('((0,0),2.0)'::circle)
box(point,point)
box
convert points to box
box('(0,0)'::point,'(1,1)'::point)
box(polygon)
box
convert polygon to box
box('((0,0),(1,1),(2,0))'::polygon)
circle(box)
circle
convert to circle
circle('((0,0),(1,1))'::box)
circle(point,float8)
circle
convert to circle
circle('(0,0)'::point,2.0)
lseg(box)
lseg
convert diagonal to lseg
lseg('((-1,0),(1,0))'::box)
lseg(point,point)
lseg
convert to lseg
lseg('(-1,0)'::point,'(1,0)'::point)
path(polygon)
point
convert to path
path('((0,0),(1,1),(2,0))'::polygon)
point(circle)
point
convert to point (center)
point('((0,0),2.0)'::circle)
point(lseg,lseg)
point
convert to point (intersection)
point('((-1,0),(1,0))'::lseg,'((-2,-2),(2,2))'::lseg)
point(polygon)
point
center of polygon
point('((0,0),(1,1),(2,0))'::polygon)
polygon(box)
polygon
convert to polygon with 12 points
polygon('((0,0),(1,1))'::box)
polygon(circle)
polygon
convert to polygon with 12 points
polygon('((0,0),2.0)'::circle)
polygon(npts,circle)
polygon
convert to polygon with npts points
polygon(12,'((0,0),2.0)'::circle)
polygon(path)
polygon
convert to polygon
polygon('((0,0),(1,1),(2,0))'::path)
Geometric Upgrade Functions
Function
Returns
Description
Example
isoldpath(path)
path
test path for pre-v6.1 form
isoldpath('(1,3,0,0,1,1,2,0)'::path)
revertpoly(polygon)
polygon
convert pre-v6.1 polygon
revertpoly('((0,0),(1,1),(2,0))'::polygon)
upgradepath(path)
path
convert pre-v6.1 path
upgradepath('(1,3,0,0,1,1,2,0)'::path)
upgradepoly(polygon)
polygon
convert pre-v6.1 polygon
upgradepoly('(0,1,2,0,1,0)'::polygon)
IP V4 Functions
PostgresIP V4 Functions
Function
Returns
Description
Example
broadcast(cidr)
text
construct broadcast address as text
broadcast('192.168.1.5/24') ==> '192.168.1.255'
broadcast(inet)
text
construct broadcast address as text
broadcast('192.168.1.5/24') ==> '192.168.1.255'
host(inet)
text
extract host address as text
host('192.168.1.5/24') ==> '192.168.1.5'
masklen(cidr)
int4
calculate netmask length
masklen('192.168.1.5/24') ==> 24
masklen(inet)
int4
calculate netmask length
masklen('192.168.1.5/24') ==> 24
netmask(inet)
text
construct netmask as text
netmask('192.168.1.5/24') ==> '255.255.255.0'