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) NULLIF(input,value) input or NULL return NULL if input = value NULLIF(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). Written by Karel Zak on 2000-01-24. Formatting functions provide a powerful set of tools for converting various datetypes (date/time, int, float, numeric) to formatted strings and reverse convert from formatted strings to original datetypes. Function Returns Description Example to_char(datetime, text) text convert datetime to string to_char('now'::datetime, 'HH12:MI:SS') to_char(timestamp, text) text convert timestamp to string to_char( now(), 'HH12:MI:SS') to_char(int, text) text convert int4/int8 to string to_char(125, '999') to_char(float, text) text convert float4/float8 to string to_char(125.8, '999D9') to_char(numeric, text) text convert numeric to string to_char(-125.8, '999D99S') to_datetime(text, text) datetime convert string to datetime to_datetime('05 Dec 2000 13', 'DD Mon YYYY HH') to_date(text, text) date convert string to date to_date('05 Dec 2000', 'DD Mon YYYY') to_timestamp(text, text) date convert string to timestamp to_timestamp('05 Dec 2000', 'DD Mon YYYY') to_number(text, text) numeric convert string to numeric to_number('12,454.8-', '99G999D9S') For all formatting functions is second argument format-picture. Format-picture Description HH hour of day (01-12) HH12 hour of day (01-12) MI minute (00-59) SS socond (00-59) SSSS seconds past midnight (0-86399) Y,YYY year (4 and more digits) with comma YYYY year (4 and more digits) YYY last 3 digits of year YY last 2 digits of year Y last digit of year MONTH full month name (9-letters) - all characters is upper Month full month name (9-letters) - first character is upper month full month name (9-letters) - all characters is lower MON abbreviated month name (3-letters) - all characters is upper Mon abbreviated month name (3-letters) - first character is upper mon abbreviated month name (3-letters) - all characters is lower MM month (01-12) DAY full day name (9-letters) - all characters is upper Day full day name (9-letters) - first character is upper day full day name (9-letters) - all characters is lower DY abbreviated day name (3-letters) - all characters is upper Dy abbreviated day name (3-letters) - first character is upper dy abbreviated day name (3-letters) - all characters is upper DDD day of year (001-366) DD day of month (01-31) D day of week (1-7; SUN=1) W week of month WW week number of year CC century (2-digits) J julian day (days since January 1, 4712 BC) Q quarter RM month in roman numeral (I-XII; I=JAN) All format-pictures allow use suffixes (postfix / prefix). The suffix is always valid for a near format-picture. The 'FX' is global prefix only. Suffix Description Example FM fill mode - prefix FMMonth TH upper ordinal number - postfix DDTH th lower ordinal number - postfix DDTH FX FX - (Fixed format) global format-picture switch. The TO_DATETIME / TO_DATE skip blank space if this option is not use. Must by used as first item in formt-picture. FX Month DD Day SP spell mode (not implement now) DDSP '\' - must be use as double \\, example '\\HH\\MI\\SS' '"' - string between a quotation marks is skipen and not is parsed. If you want write '"' to output you must use \\", example '\\"YYYY Month\\"'. text - the PostgreSQL's to_char() support text without '"', but string between a quotation marks is fastly and you have guarantee, that a text not will interpreted as a keyword (format-picture), exapmle '"Hello Year: "YYYY'. Format-picture Description 9 return value with the specified number of digits, and if digit is not available use blank space 0 as 9, but instead blank space use zero . (period) decimal point , (comma) group (thousand) separator PR return negative value in angle brackets S return negatice value with minus sign (use locales) L currency symbol (use locales) D decimal point (use locales) G group separator (use locales) MI return minus sign on specified position (if number < 0) PL return plus sign on specified position (if number > 0) - PostgreSQL extension SG return plus/minus sign on specified position - PostgreSQL extension RN return number as roman number (number must be between 1 and 3999) TH or th convert number to ordinal number (not convert numbers under zero and decimal numbers) - PostgreSQL extension V arg1 * (10 ^ n); - return a value multiplied by 10^n (where 'n' is number of '9's after the 'V'). The to_char() not support use 'V' and decimal poin together, example "99.9V99". EEEE science numbers. Now not supported. Note: A sign formatted via 'SG', 'PL' or 'MI' is not anchor in number; to_char(-12, 'S9999') produce: ' -12' , but to_char(-12, 'MI9999') produce: '- 12' . The Oracle not allow use 'MI' ahead of '9', in the Oracle must be it always after '9'. Input Output to_char(now(), 'Day, HH12:MI:SS') 'Tuesday , 05:39:18' to_char(now(), 'FMDay, HH12:MI:SS') 'Tuesday, 05:39:18' to_char( -0.1, '99.99') ' -.10' to_char( -0.1, 'FM9.99') '-.1' to_char( 0.1, '0.9') ' 0.1' to_char( 12, '9990999.9') ' 0012.0' to_char( 12, 'FM9990999.9') '0012' to_char( 485, '999') ' 485' to_char( -485, '999') '-485' to_char( 485, '9 9 9') ' 4 8 5' to_char( 1485, '9,999') ' 1,485' to_char( 1485, '9G999') ' 1 485' to_char( 148.5, '999.999') ' 148.500' to_char( 148.5, '999D999') ' 148,500' to_char( 3148.5,'9G999D999') ' 3 148,500' to_char( -485, '999S') '485-' to_char( -485, '999MI') '485-' to_char( 485, '999MI') '485' to_char( 485, 'PL999') '+485' to_char( 485, 'SG999') '+485' to_char( -485, 'SG999') '-485' to_char( -485, '9SG99') '4-85' to_char( -485, '999PR') '<485>' to_char( 485, 'L999') 'DM 485' to_char( 485, 'RN') ' CDLXXXV' to_char( 485, 'FMRN') 'CDLXXXV' to_char( 5.2, 'FMRN') 'V' to_char( 482, '999th') ' 482nd' to_char( 485, '"Good number:"999') 'Good number: 485' to_char( 485.8, '"Pre-decimal:"999" Post-decimal:" .999') 'Pre-decimal: 485 Post-decimal: .800' to_char( 12, '99V999') ' 12000' to_char( 12.4, '99V999') ' 12400' to_char( 12.45, '99V9') ' 125' 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')