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. SQL functions are constructs defined by the SQL92 standard which have function-like syntax but which can not be implemented as simple functions. Function Returns Description Example COALESCE(list) non-NULL return first non-NULL value in list COALESCE(r"le>, c2 + 5, 0) IFNULL(input,non-NULL substitute) non-NULL return second argument if first is NULL IFNULL(c1, 'N/A') CASE WHEN expr THEN expr [...] ELSE expr END expr return expression for first true clause CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END 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) SQL92 defines string functions with specific syntax. Some of these are implemented using other Postgres functions. The supported string types for SQL92 are char, varchar, and text. Function Returns Description Example char_length(string) int4 length of string char_length('jose') character_length(string) int4 length of string char_length('jose') lower(string) string convert string to lower case lower('TOM') octet_length(string) int4 storage length of string octet_length('jose') position(string in string) int4 location of specified substring position('o' in 'Tom') substring(string [from int] [for int]) string extract specified substring substring('Tom' from 2 for 2) trim([leading|trailing|both] [string] from string) string trim characters from string trim(both 'x' from 'xTomx') upper(text) text convert text to upper case upper('tom') Many additional string functions are available for text, varchar(), and char() types. Some are used internally to implement the SQL92 string functions listed above. 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') 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') textpos(text,text) text locate 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') Most functions explicitly defined for text will work for char() and varchar() arguments. The date/time functions provide a powerful set of tools for manipulating various date/time types. Function Returns Description Example abstime(datetime) abstime convert to abstime abstime('now'::datetime) age(datetime,datetime) timespan preserve 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 portion of date date_part('dow','now'::datetime) date_part(text,timespan) float8 portion of time date_part('hour','4 hrs 3 mins'::timespan) date_trunc(text,datetime) datetime truncate date date_trunc('month','now'::abstime) isfinite(abstime) bool a finite time? isfinite('now'::abstime) isfinite(datetime) bool a finite time? isfinite('now'::datetime) isfinite(timespan) bool 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). The geometric types point, box, lseg, line, path, polygon, and circle have a large set of native support 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 a closed path? isclosed('((0,0),(1,1),(2,0))'::path) isopen(path) bool 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 popen('[(0,0),(1,1),(2,0)]'::path) point(lseg,lseg) point intersection point('((-1,0),(1,0))'::lseg,'((-2,-2),(2,2))'::lseg) points(path) int4 number of points points('[(0,0),(1,1),(2,0)]'::path) popen(path) path convert path to open 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 width('((0,0),(1,1))'::box) 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 12-point polygon polygon('((0,0),2.0)'::circle) polygon(npts,circle) polygon convert to npts polygon polygon(12,'((0,0),2.0)'::circle) polygon(path) polygon convert to polygon polygon('((0,0),(1,1),(2,0))'::path) 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) Function Returns Description Example broadcast(cidr) text construct broadcast address as text broadcast('192.168.1.5/24') broadcast(inet) text construct broadcast address as text broadcast('192.168.1.5/24') host(inet) text extract host address as text host('192.168.1.5/24') masklen(cidr) int4 calculate netmask length masklen('192.168.1.5/24') masklen(inet) int4 calculate netmask length masklen('192.168.1.5/24') netmask(inet) text construct netmask as text netmask('192.168.1.5/24')