1 <chapter id="functions">
2 <title id="functions-title">Functions</title>
6 Describes the built-in functions available
7 in <productname>Postgres</productname>.
12 Many data types have functions available for conversion to other related types.
13 In addition, there are some type-specific functions. Some functions are also
14 available through operators and may be documented as operators only.
18 <title id="sql-funcs">SQL Functions</title>
21 <firstterm><acronym>SQL</acronym> functions</firstterm> are constructs
22 defined by the <acronym>SQL92</acronym> standard which have
23 function-like syntax but which can not be implemented as simple
29 <title>SQL Functions</title>
33 <entry>Function</entry>
34 <entry>Returns</entry>
35 <entry>Description</entry>
36 <entry>Example</entry>
41 <entry>COALESCE(<replaceable class="parameter">list</replaceable>)</entry>
42 <entry>non-NULL</entry>
43 <entry>return first non-NULL value in list</entry>
44 <entry>COALESCE(rle, c2 + 5, 0)</entry>
47 <entry>NULLIF(<replaceable class="parameter">input</replaceable>,<replaceable class="parameter">value</replaceable>)</entry>
48 <entry><replaceable class="parameter">input</replaceable> or NULL</entry>
50 <replaceable class="parameter">input</replaceable> =
51 <replaceable class="parameter">value</replaceable>,
52 else <replaceable class="parameter">input</replaceable>
54 <entry>NULLIF(c1, 'N/A')</entry>
57 <entry>CASE WHEN <replaceable class="parameter">expr</replaceable> THEN <replaceable class="parameter">expr</replaceable> [...] ELSE <replaceable class="parameter">expr</replaceable> END</entry>
58 <entry><replaceable class="parameter">expr</replaceable></entry>
59 <entry>return expression for first true WHEN clause</entry>
60 <entry>CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END</entry>
69 <title id="math-funcs">Mathematical Functions</title>
73 <title>Mathematical Functions</title>
77 <entry>Function</entry>
78 <entry>Returns</entry>
79 <entry>Description</entry>
80 <entry>Example</entry>
85 <entry>abs(float8)</entry>
87 <entry>absolute value</entry>
88 <entry>abs(-17.4)</entry>
91 <entry>degrees(float8)</entry>
93 <entry>radians to degrees</entry>
94 <entry>degrees(0.5)</entry>
97 <entry>exp(float8)</entry>
99 <entry>raise e to the specified exponent</entry>
100 <entry>exp(2.0)</entry>
103 <entry>ln(float8)</entry>
104 <entry>float8</entry>
105 <entry>natural logarithm</entry>
106 <entry>ln(2.0)</entry>
109 <entry>log(float8)</entry>
110 <entry>float8</entry>
111 <entry>base 10 logarithm</entry>
112 <entry>log(2.0)</entry>
116 <entry>float8</entry>
117 <entry>fundamental constant</entry>
121 <entry>pow(float8,float8)</entry>
122 <entry>float8</entry>
123 <entry>raise a number to the specified exponent</entry>
124 <entry>pow(2.0, 16.0)</entry>
127 <entry>radians(float8)</entry>
128 <entry>float8</entry>
129 <entry>degrees to radians</entry>
130 <entry>radians(45.0)</entry>
133 <entry>round(float8)</entry>
134 <entry>float8</entry>
135 <entry>round to nearest integer</entry>
136 <entry>round(42.4)</entry>
139 <entry>sqrt(float8)</entry>
140 <entry>float8</entry>
141 <entry>square root</entry>
142 <entry>sqrt(2.0)</entry>
145 <entry>cbrt(float8)</entry>
146 <entry>float8</entry>
147 <entry>cube root</entry>
148 <entry>cbrt(27.0)</entry>
151 <entry>trunc(float8)</entry>
152 <entry>float8</entry>
153 <entry>truncate (towards zero)</entry>
154 <entry>trunc(42.4)</entry>
157 <entry>float(int)</entry>
158 <entry>float8</entry>
159 <entry>convert integer to floating point</entry>
160 <entry>float(2)</entry>
163 <entry>float4(int)</entry>
164 <entry>float4</entry>
165 <entry>convert integer to floating point</entry>
166 <entry>float4(2)</entry>
169 <entry>integer(float)</entry>
171 <entry>convert floating point to integer</entry>
172 <entry>integer(2.0)</entry>
180 Most of the functions listed for FLOAT8 are also available for
186 <title>Transcendental Mathematical Functions</title>
190 <entry>Function</entry>
191 <entry>Returns</entry>
192 <entry>Description</entry>
193 <entry>Example</entry>
198 <entry>acos(float8)</entry>
199 <entry>float8</entry>
200 <entry>arccosine</entry>
201 <entry>acos(10.0)</entry>
204 <entry>asin(float8)</entry>
205 <entry>float8</entry>
206 <entry>arcsine</entry>
207 <entry>asin(10.0)</entry>
210 <entry>atan(float8)</entry>
211 <entry>float8</entry>
212 <entry>arctangent</entry>
213 <entry>atan(10.0)</entry>
216 <entry>atan2(float8,float8)</entry>
217 <entry>float8</entry>
218 <entry>arctangent</entry>
219 <entry>atan2(10.0,20.0)</entry>
222 <entry>cos(float8)</entry>
223 <entry>float8</entry>
224 <entry>cosine</entry>
225 <entry>cos(0.4)</entry>
228 <entry>cot(float8)</entry>
229 <entry>float8</entry>
230 <entry>cotangent</entry>
231 <entry>cot(20.0)</entry>
234 <entry>sin(float8)</entry>
235 <entry>float8</entry>
237 <entry>cos(0.4)</entry>
240 <entry>tan(float8)</entry>
241 <entry>float8</entry>
242 <entry>tangent</entry>
243 <entry>tan(0.4)</entry>
253 <title>String Functions</title>
256 SQL92 defines string functions with specific syntax. Some of these
257 are implemented using other <productname>Postgres</productname> functions.
258 The supported string types for <acronym>SQL92</acronym> are
259 <type>char</type>, <type>varchar</type>, and <type>text</type>.
264 <title><acronym>SQL92</acronym> String Functions</title>
268 <entry>Function</entry>
269 <entry>Returns</entry>
270 <entry>Description</entry>
271 <entry>Example</entry>
276 <entry>char_length(string)</entry>
278 <entry>length of string</entry>
279 <entry>char_length('jose')</entry>
282 <entry>character_length(string)</entry>
284 <entry>length of string</entry>
285 <entry>char_length('jose')</entry>
288 <entry>lower(string)</entry>
289 <entry>string</entry>
290 <entry>convert string to lower case</entry>
291 <entry>lower('TOM')</entry>
294 <entry>octet_length(string)</entry>
296 <entry>storage length of string</entry>
297 <entry>octet_length('jose')</entry>
300 <entry>position(string in string)</entry>
302 <entry>location of specified substring</entry>
303 <entry>position('o' in 'Tom')</entry>
306 <entry>substring(string [from int] [for int])</entry>
307 <entry>string</entry>
308 <entry>extract specified substring</entry>
309 <entry>substring('Tom' from 2 for 2)</entry>
312 <entry>trim([leading|trailing|both] [string] from string)</entry>
313 <entry>string</entry>
314 <entry>trim characters from string</entry>
315 <entry>trim(both 'x' from 'xTomx')</entry>
318 <entry>upper(text)</entry>
320 <entry>convert text to upper case</entry>
321 <entry>upper('tom')</entry>
329 Many additional string functions are available for text, varchar(), and char() types.
330 Some are used internally to implement the SQL92 string functions listed above.
335 <title>String Functions</title>
339 <entry>Function</entry>
340 <entry>Returns</entry>
341 <entry>Description</entry>
342 <entry>Example</entry>
347 <entry>char(text)</entry>
349 <entry>convert text to char type</entry>
350 <entry>char('text string')</entry>
353 <entry>char(varchar)</entry>
355 <entry>convert varchar to char type</entry>
356 <entry>char(varchar 'varchar string')</entry>
359 <entry>initcap(text)</entry>
361 <entry>first letter of each word to upper case</entry>
362 <entry>initcap('thomas')</entry>
365 <entry>lpad(text,int,text)</entry>
367 <entry>left pad string to specified length</entry>
368 <entry>lpad('hi',4,'??')</entry>
371 <entry>ltrim(text,text)</entry>
373 <entry>left trim characters from text</entry>
374 <entry>ltrim('xxxxtrim','x')</entry>
377 <entry>textpos(text,text)</entry>
379 <entry>locate specified substring</entry>
380 <entry>position('high','ig')</entry>
383 <entry>rpad(text,int,text)</entry>
385 <entry>right pad string to specified length</entry>
386 <entry>rpad('hi',4,'x')</entry>
389 <entry>rtrim(text,text)</entry>
391 <entry>right trim characters from text</entry>
392 <entry>rtrim('trimxxxx','x')</entry>
395 <entry>substr(text,int[,int])</entry>
397 <entry>extract specified substring</entry>
398 <entry>substr('hi there',3,5)</entry>
401 <entry>text(char)</entry>
403 <entry>convert char to text type</entry>
404 <entry>text('char string')</entry>
407 <entry>text(varchar)</entry>
409 <entry>convert varchar to text type</entry>
410 <entry>text(varchar 'varchar string')</entry>
413 <entry>translate(text,from,to)</entry>
415 <entry>convert character in string</entry>
416 <entry>translate('12345', '1', 'a')</entry>
419 <entry>varchar(char)</entry>
420 <entry>varchar</entry>
421 <entry>convert char to varchar type</entry>
422 <entry>varchar('char string')</entry>
425 <entry>varchar(text)</entry>
426 <entry>varchar</entry>
427 <entry>convert text to varchar type</entry>
428 <entry>varchar('text string')</entry>
436 Most functions explicitly defined for text will work for char() and varchar() arguments.
441 <title>Date/Time Functions</title>
444 The date/time functions provide a powerful set of tools
445 for manipulating various date/time types.
450 <title>Date/Time Functions</title>
454 <entry>Function</entry>
455 <entry>Returns</entry>
456 <entry>Description</entry>
457 <entry>Example</entry>
462 <entry>abstime(timestamp)</entry>
463 <entry>abstime</entry>
464 <entry>convert to abstime</entry>
465 <entry>abstime(timestamp 'now')</entry>
468 <entry>age(timestamp)</entry>
469 <entry>interval</entry>
470 <entry>preserve months and years</entry>
471 <entry>age(timestamp '1957-06-13')</entry>
474 <entry>age(timestamp,timestamp)</entry>
475 <entry>interval</entry>
476 <entry>preserve months and years</entry>
477 <entry>age('now', timestamp '1957-06-13')</entry>
480 <entry>date_part(text,timestamp)</entry>
481 <entry>float8</entry>
482 <entry>portion of date</entry>
483 <entry>date_part('dow',timestamp 'now')</entry>
486 <entry>date_part(text,interval)</entry>
487 <entry>float8</entry>
488 <entry>portion of time</entry>
489 <entry>date_part('hour',interval '4 hrs 3 mins')</entry>
492 <entry>date_trunc(text,timestamp)</entry>
493 <entry>timestamp</entry>
494 <entry>truncate date</entry>
495 <entry>date_trunc('month',abstime 'now')</entry>
498 <entry>interval(reltime)</entry>
499 <entry>interval</entry>
500 <entry>convert to interval</entry>
501 <entry>interval(reltime '4 hours')</entry>
504 <entry>isfinite(timestamp)</entry>
506 <entry>a finite time?</entry>
507 <entry>isfinite(timestamp 'now')</entry>
510 <entry>isfinite(interval)</entry>
512 <entry>a finite time?</entry>
513 <entry>isfinite(interval '4 hrs')</entry>
516 <entry>reltime(interval)</entry>
517 <entry>reltime</entry>
518 <entry>convert to reltime</entry>
519 <entry>reltime(interval '4 hrs')</entry>
522 <entry>timestamp(date)</entry>
523 <entry>timestamp</entry>
524 <entry>convert to timestamp</entry>
525 <entry>timestamp(date 'today')</entry>
528 <entry>timestamp(date,time)</entry>
529 <entry>timestamp</entry>
530 <entry>convert to timestamp</entry>
531 <entry>timestamp(timestamp '1998-02-24',time '23:07');</entry>
534 <entry>to_char(timestamp,text)</entry>
536 <entry>convert to string</entry>
537 <entry>to_char(timestamp '1998-02-24','DD');</entry>
546 <function>date_part</function> and <function>date_trunc</function>
547 functions, arguments can be
548 `<literal>year</literal>', `<literal>month</literal>',
549 `<literal>day</literal>', `<literal>hour</literal>',
550 `<literal>minute</literal>', and `<literal>second</literal>',
551 as well as the more specialized quantities
552 `<literal>decade</literal>', `<literal>century</literal>',
553 `<literal>millennium</literal>', `<literal>millisecond</literal>',
554 and `<literal>microsecond</literal>'.
555 <function>date_part</function> allows `<literal>dow</literal>'
556 to return day of week, '<literal>week</literal>' to return the
557 ISO-defined week of year, and `<literal>epoch</literal>' to return
558 seconds since 1970 (for <type>timestamp</type>)
559 or '<literal>epoch</literal>' to return total elapsed seconds
560 (for <type>interval</type>).
567 <title id="formatting-funcs">Formatting Functions</title>
570 <title>Author</title>
573 <ulink url="mailto:zakkr@zf.jcu.cz">Karel Zak</ulink>
578 The <productname>Postgres</productname>
579 formatting functions provide a powerful set of tools for converting
580 various datetypes (date/time, int, float, numeric) to formatted strings
581 and for converting from formatted strings to specific datetypes.
585 The second argument for all formatting functions is a template to
586 be used for the conversion.
593 <title>Formatting Functions</title>
597 <entry>Function</entry>
598 <entry>Returns</entry>
599 <entry>Description</entry>
600 <entry>Example</entry>
605 <entry>to_char(timestamp, text)</entry>
607 <entry>convert timestamp to string</entry>
608 <entry>to_char(timestamp 'now','HH12:MI:SS')</entry>
611 <entry>to_char(int, text)</entry>
613 <entry>convert int4/int8 to string</entry>
614 <entry>to_char(125, '999')</entry>
617 <entry>to_char(float, text)</entry>
619 <entry>convert float4/float8 to string</entry>
620 <entry>to_char(125.8, '999D9')</entry>
623 <entry>to_char(numeric, text)</entry>
625 <entry>convert numeric to string</entry>
626 <entry>to_char(numeric '-125.8', '999D99S')</entry>
629 <entry>to_date(text, text)</entry>
631 <entry>convert string to date</entry>
632 <entry>to_date('05 Dec 2000', 'DD Mon YYYY')</entry>
635 <entry>to_timestamp(text, text)</entry>
637 <entry>convert string to timestamp</entry>
638 <entry>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</entry>
641 <entry>to_number(text, text)</entry>
642 <entry>numeric</entry>
643 <entry>convert string to numeric</entry>
644 <entry>to_number('12,454.8-', '99G999D9S')</entry>
653 <title>Templates for date/time conversions</title>
657 <entry>Template</entry>
658 <entry>Description</entry>
664 <entry>hour of day (01-12)</entry>
668 <entry>hour of day (01-12)</entry>
672 <entry>hour of day (00-23)</entry>
676 <entry>minute (00-59)</entry>
680 <entry>second (00-59)</entry>
684 <entry>seconds past midnight (0-86399)</entry>
687 <entry>AM or A.M. or PM or P.M.</entry>
688 <entry>meridian indicator (upper case)</entry>
691 <entry>am or a.m. or pm or p.m.</entry>
692 <entry>meridian indicator (lower case)</entry>
696 <entry>year (4 and more digits) with comma</entry>
700 <entry>year (4 and more digits)</entry>
704 <entry>last 3 digits of year</entry>
708 <entry>last 2 digits of year</entry>
712 <entry>last digit of year</entry>
715 <entry>BC or B.C. or AD or A.D.</entry>
716 <entry>year indicator (upper case)</entry>
719 <entry>bc or b.c. or ad or a.d.</entry>
720 <entry>year indicator (lower case)</entry>
724 <entry>full upper case month name (9 chars)</entry>
728 <entry>full mixed case month name (9 chars)</entry>
732 <entry>full lower case month name (9 chars)</entry>
736 <entry>upper case abbreviated month name (3 chars)</entry>
740 <entry>abbreviated mixed case month name (3 chars)</entry>
744 <entry>abbreviated lower case month name (3 chars)</entry>
748 <entry>month (01-12)</entry>
752 <entry>full upper case day name (9 chars)</entry>
756 <entry>full mixed case day name (9 chars)</entry>
760 <entry>full lower case day name (9 chars)</entry>
764 <entry>abbreviated upper case day name (3 chars)</entry>
768 <entry>abbreviated mixed case day name (3 chars)</entry>
772 <entry>abbreviated lower case day name (3 chars)</entry>
776 <entry>day of year (001-366)</entry>
780 <entry>day of month (01-31)</entry>
784 <entry>day of week (1-7; SUN=1)</entry>
788 <entry>week of month (1-5) where first week start on the first day of the month</entry>
792 <entry>week number of year (1-53) where first week start on the first day of the year</entry>
796 <entry>century (2 digits)</entry>
800 <entry>Julian Day (days since January 1, 4712 BC)</entry>
804 <entry>quarter</entry>
808 <entry>month in Roman Numerals (I-XII; I=JAN) - upper case</entry>
812 <entry>month in Roman Numerals (I-XII; I=JAN) - lower case</entry>
816 <entry>timezone string - upper case (not supported in the to_timestamp())</entry>
820 <entry>timezone string - lower case (not supported in the to_timestamp())</entry>
828 All templates allow the use of prefix and suffix modifiers. Modifiers are
829 always valid for use in templates. The prefix
830 '<literal>FX</literal>' is a global modifier only.
835 <title>Suffixes for templates for date/time to_char()</title>
839 <entry>Suffix</entry>
840 <entry>Description</entry>
841 <entry>Example</entry>
847 <entry>fill mode prefix</entry>
848 <entry>FMMonth</entry>
852 <entry>upper ordinal number suffix</entry>
857 <entry>lower ordinal number suffix</entry>
862 <entry>FiXed format global option (see below)</entry>
863 <entry>FX Month DD Day</entry>
867 <entry>spell mode (not yet implemented)</entry>
881 <function>to_timestamp</function> and <function>to_date</function>
882 skip multiple blank space in converted string if the <literal>FX</literal> option
883 is not used. <literal>FX</literal> must be specified as the first item
884 in the template; for example
885 <literal>to_timestamp('2000 JUN','YYYY MON')</literal> is right, but
886 <literal>to_timestamp('2000 JUN','FXYYYY MON')</literal> returns error,
887 because to_timestamp() expects one blank space only.
893 Backslash ("<literal>\</literal>") must be specified with a double backslash
894 ("<literal>\\</literal>"); for example <literal>'\\HH\\MI\\SS'</literal>.
900 A double quote ('"') between quotation marks is skipped and is not parsed.
901 If you want to write a double quote to output you must preceed
902 it with a double backslash (<literal>'\\"</literal>), for
903 example <literal>'\\"YYYY Month\\"'</literal>.
909 <function>to_char</function> supports text without a leading
910 double quote ('"'), but any string
911 between a quotation marks is rapidly handled and you are
912 guaranteed that it will not be interpreted as a template
913 keyword (example: <literal>'"Hello Year: "YYYY'</literal>).
921 <title>Templates for to_char(<replaceable>numeric</replaceable>)</title>
925 <entry>Template</entry>
926 <entry>Description</entry>
932 <entry>value with the specified number of digits</entry>
936 <entry>value with leading zeros</entry>
939 <entry>. (period)</entry>
940 <entry>decimal point</entry>
943 <entry>, (comma)</entry>
944 <entry>group (thousand) separator</entry>
948 <entry>negative value in angle brackets</entry>
952 <entry>negative value with minus sign (use locales)</entry>
956 <entry>currency symbol (use locales)</entry>
960 <entry>decimal point (use locales)</entry>
964 <entry>group separator (use locales)</entry>
968 <entry>minus sign on specified position (if number < 0)</entry>
972 <entry>plus sign on specified position (if number > 0)</entry>
976 <entry>plus/minus sign on specified position</entry>
980 <entry>roman numeral (input between 1 and 3999)</entry>
983 <entry>TH or th</entry>
984 <entry>convert to ordinal number</entry>
988 <entry>Shift <replaceable>n</replaceable> digits (see
993 <entry>science numbers. Now not supported.</entry>
1006 A sign formatted using 'SG', 'PL' or 'MI' is not an anchor in
1007 the number; for example,
1008 to_char(-12, 'S9999') produces <literal>' -12'</literal>,
1009 but to_char(-12, 'MI9999') produces <literal>'- 12'</literal>.
1010 The Oracle implementation does not allow the use of
1011 <literal>MI</literal> ahead of <literal>9</literal>, but rather
1012 requires that <literal>9</literal> preceeds
1013 <literal>MI</literal>.
1019 <literal>PL</literal>, <literal>SG</literal>, and
1020 <literal>TH</literal> are <productname>Postgres</productname>
1027 <literal>9</literal> specifies a value with the same number of
1028 digits as there are <literal>9</literal>s. If a digit is
1029 not available use blank space.
1035 <literal>TH</literal> does not convert values less than zero
1036 and does not convert decimal numbers. <literal>TH</literal> is
1037 a <productname>Postgres</productname> extension.
1043 <literal>V</literal> effectively
1044 multiplies the input values by
1045 <literal>10^<replaceable>n</replaceable></literal>, where
1046 <replaceable>n</replaceable> is the number of digits following
1047 <literal>V</literal>.
1048 <function>to_char</function> does not support the use of
1049 <literal>V</literal> combined with a decimal point
1050 (e.g. "99.9V99" is not allowed).
1057 <table tocentry="1">
1058 <title><function>to_char</function> Examples</title>
1062 <entry>Input</entry>
1063 <entry>Output</entry>
1068 <entry>to_char(now(),'Day, HH12:MI:SS')</entry>
1069 <entry><literal>'Tuesday , 05:39:18'</literal></entry>
1072 <entry>to_char(now(),'FMDay, HH12:MI:SS')</entry>
1073 <entry><literal>'Tuesday, 05:39:18'</literal></entry>
1076 <entry>to_char(-0.1,'99.99')</entry>
1077 <entry><literal>' -.10'</literal></entry>
1080 <entry>to_char(-0.1,'FM9.99')</entry>
1081 <entry><literal>'-.1'</literal></entry>
1084 <entry>to_char(0.1,'0.9')</entry>
1085 <entry><literal>' 0.1'</literal></entry>
1088 <entry>to_char(12,'9990999.9')</entry>
1089 <entry><literal>' 0012.0'</literal></entry>
1092 <entry>to_char(12,'FM9990999.9')</entry>
1093 <entry><literal>'0012'</literal></entry>
1096 <entry>to_char(485,'999')</entry>
1097 <entry><literal>' 485'</literal></entry>
1100 <entry>to_char(-485,'999')</entry>
1101 <entry><literal>'-485'</literal></entry>
1104 <entry>to_char(485,'9 9 9')</entry>
1105 <entry><literal>' 4 8 5'</literal></entry>
1108 <entry>to_char(1485,'9,999')</entry>
1109 <entry><literal>' 1,485'</literal></entry>
1112 <entry>to_char(1485,'9G999')</entry>
1113 <entry><literal>' 1 485'</literal></entry>
1116 <entry>to_char(148.5,'999.999')</entry>
1117 <entry><literal>' 148.500'</literal></entry>
1120 <entry>to_char(148.5,'999D999')</entry>
1121 <entry><literal>' 148,500'</literal></entry>
1124 <entry>to_char(3148.5,'9G999D999')</entry>
1125 <entry><literal>' 3 148,500'</literal></entry>
1128 <entry>to_char(-485,'999S')</entry>
1129 <entry><literal>'485-'</literal></entry>
1132 <entry>to_char(-485,'999MI')</entry>
1133 <entry><literal>'485-'</literal></entry>
1136 <entry>to_char(485,'999MI')</entry>
1137 <entry><literal>'485'</literal></entry>
1140 <entry>to_char(485,'PL999')</entry>
1141 <entry><literal>'+485'</literal></entry>
1144 <entry>to_char(485,'SG999')</entry>
1145 <entry><literal>'+485'</literal></entry>
1148 <entry>to_char(-485,'SG999')</entry>
1149 <entry><literal>'-485'</literal></entry>
1152 <entry>to_char(-485,'9SG99')</entry>
1153 <entry><literal>'4-85'</literal></entry>
1156 <entry>to_char(-485,'999PR')</entry>
1157 <entry><literal>'<485>'</literal></entry>
1160 <entry>to_char(485,'L999')</entry>
1161 <entry><literal>'DM 485</literal></entry>
1164 <entry>to_char(485,'RN')</entry>
1165 <entry><literal>' CDLXXXV'</literal></entry>
1168 <entry>to_char(485,'FMRN')</entry>
1169 <entry><literal>'CDLXXXV'</literal></entry>
1172 <entry>to_char(5.2,'FMRN')</entry>
1173 <entry><literal>V</literal></entry>
1176 <entry>to_char(482,'999th')</entry>
1177 <entry><literal>' 482nd'</literal></entry>
1180 <entry>to_char(485, '"Good number:"999')</entry>
1181 <entry><literal>'Good number: 485'</literal></entry>
1184 <entry>to_char(485.8,'"Pre-decimal:"999" Post-decimal:" .999')</entry>
1185 <entry><literal>'Pre-decimal: 485 Post-decimal: .800'</literal></entry>
1188 <entry>to_char(12,'99V999')</entry>
1189 <entry><literal>' 12000'</literal></entry>
1192 <entry>to_char(12.4,'99V999')</entry>
1193 <entry><literal>' 12400'</literal></entry>
1196 <entry>to_char(12.45, '99V9')</entry>
1197 <entry><literal>' 125'</literal></entry>
1207 <title>Geometric Functions</title>
1210 The geometric types point, box, lseg, line, path, polygon, and
1211 circle have a large set of native support functions.
1215 <table tocentry="1">
1216 <title>Geometric Functions</title>
1220 <entry>Function</entry>
1221 <entry>Returns</entry>
1222 <entry>Description</entry>
1223 <entry>Example</entry>
1228 <entry>area(object)</entry>
1229 <entry>float8</entry>
1230 <entry>area of item</entry>
1231 <entry>area(box '((0,0),(1,1))')</entry>
1234 <entry>box(box,box)</entry>
1236 <entry>intersection box</entry>
1237 <entry>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</entry>
1240 <entry>center(object)</entry>
1241 <entry>point</entry>
1242 <entry>center of item</entry>
1243 <entry>center(box '((0,0),(1,2))')</entry>
1246 <entry>diameter(circle)</entry>
1247 <entry>float8</entry>
1248 <entry>diameter of circle</entry>
1249 <entry>diameter(circle '((0,0),2.0)')</entry>
1252 <entry>height(box)</entry>
1253 <entry>float8</entry>
1254 <entry>vertical size of box</entry>
1255 <entry>height(box '((0,0),(1,1))')</entry>
1258 <entry>isclosed(path)</entry>
1260 <entry>a closed path?</entry>
1261 <entry>isclosed(path '((0,0),(1,1),(2,0))')</entry>
1264 <entry>isopen(path)</entry>
1266 <entry>an open path?</entry>
1267 <entry>isopen(path '[(0,0),(1,1),(2,0)]')</entry>
1270 <entry>length(object)</entry>
1271 <entry>float8</entry>
1272 <entry>length of item</entry>
1273 <entry>length(path '((-1,0),(1,0))')</entry>
1276 <entry>pclose(path)</entry>
1278 <entry>convert path to closed</entry>
1279 <entry>popen(path '[(0,0),(1,1),(2,0)]')</entry>
1282 Not defined by this name. Implements the intersection operator '#'
1284 <entry>point(lseg,lseg)</entry>
1285 <entry>point</entry>
1286 <entry>intersection</entry>
1287 <entry>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</entry>
1291 <entry>npoint(path)</entry>
1293 <entry>number of points</entry>
1294 <entry>npoints(path '[(0,0),(1,1),(2,0)]')</entry>
1297 <entry>popen(path)</entry>
1299 <entry>convert path to open path</entry>
1300 <entry>popen(path '((0,0),(1,1),(2,0))')</entry>
1303 <entry>radius(circle)</entry>
1304 <entry>float8</entry>
1305 <entry>radius of circle</entry>
1306 <entry>radius(circle '((0,0),2.0)')</entry>
1309 <entry>width(box)</entry>
1310 <entry>float8</entry>
1311 <entry>horizontal size</entry>
1312 <entry>width(box '((0,0),(1,1))')</entry>
1320 <table tocentry="1">
1321 <title>Geometric Type Conversion Functions</title>
1325 <entry>Function</entry>
1326 <entry>Returns</entry>
1327 <entry>Description</entry>
1328 <entry>Example</entry>
1333 <entry>box(circle)</entry>
1335 <entry>circle to box</entry>
1336 <entry>box('((0,0),2.0)'::circle)</entry>
1339 <entry>box(point,point)</entry>
1341 <entry>points to box</entry>
1342 <entry>box('(0,0)'::point,'(1,1)'::point)</entry>
1345 <entry>box(polygon)</entry>
1347 <entry>polygon to box</entry>
1348 <entry>box('((0,0),(1,1),(2,0))'::polygon)</entry>
1351 <entry>circle(box)</entry>
1352 <entry>circle</entry>
1353 <entry>to circle</entry>
1354 <entry>circle('((0,0),(1,1))'::box)</entry>
1357 <entry>circle(point,float8)</entry>
1358 <entry>circle</entry>
1359 <entry>point to circle</entry>
1360 <entry>circle('(0,0)'::point,2.0)</entry>
1363 <entry>lseg(box)</entry>
1365 <entry>box diagonal to lseg</entry>
1366 <entry>lseg('((-1,0),(1,0))'::box)</entry>
1369 <entry>lseg(point,point)</entry>
1371 <entry>points to lseg</entry>
1372 <entry>lseg('(-1,0)'::point,'(1,0)'::point)</entry>
1375 <entry>path(polygon)</entry>
1376 <entry>point</entry>
1377 <entry>polygon to path</entry>
1378 <entry>path('((0,0),(1,1),(2,0))'::polygon)</entry>
1381 <entry>point(circle)</entry>
1382 <entry>point</entry>
1383 <entry>center</entry>
1384 <entry>point('((0,0),2.0)'::circle)</entry>
1387 <entry>point(lseg,lseg)</entry>
1388 <entry>point</entry>
1389 <entry>intersection</entry>
1390 <entry>point('((-1,0),(1,0))'::lseg, '((-2,-2),(2,2))'::lseg)</entry>
1393 <entry>point(polygon)</entry>
1394 <entry>point</entry>
1395 <entry>center</entry>
1396 <entry>point('((0,0),(1,1),(2,0))'::polygon)</entry>
1399 <entry>polygon(box)</entry>
1400 <entry>polygon</entry>
1401 <entry>12 point polygon</entry>
1402 <entry>polygon('((0,0),(1,1))'::box)</entry>
1405 <entry>polygon(circle)</entry>
1406 <entry>polygon</entry>
1407 <entry>12-point polygon</entry>
1408 <entry>polygon('((0,0),2.0)'::circle)</entry>
1411 <entry>polygon(<replaceable class="parameter">npts</replaceable>,circle)</entry>
1412 <entry>polygon</entry>
1413 <entry><replaceable class="parameter">npts</replaceable> polygon</entry>
1414 <entry>polygon(12,'((0,0),2.0)'::circle)</entry>
1417 <entry>polygon(path)</entry>
1418 <entry>polygon</entry>
1419 <entry>path to polygon</entry>
1420 <entry>polygon('((0,0),(1,1),(2,0))'::path)</entry>
1428 <table tocentry="1">
1429 <title>Geometric Upgrade Functions</title>
1433 <entry>Function</entry>
1434 <entry>Returns</entry>
1435 <entry>Description</entry>
1436 <entry>Example</entry>
1441 <entry>isoldpath(path)</entry>
1443 <entry>test path for pre-v6.1 form</entry>
1444 <entry>isoldpath('(1,3,0,0,1,1,2,0)'::path)</entry>
1447 <entry>revertpoly(polygon)</entry>
1448 <entry>polygon</entry>
1449 <entry>to pre-v6.1</entry>
1450 <entry>revertpoly('((0,0),(1,1),(2,0))'::polygon)</entry>
1453 <entry>upgradepath(path)</entry>
1455 <entry>to pre-v6.1</entry>
1456 <entry>upgradepath('(1,3,0,0,1,1,2,0)'::path)</entry>
1459 <entry>upgradepoly(polygon)</entry>
1460 <entry>polygon</entry>
1461 <entry>to pre-v6.1</entry>
1462 <entry>upgradepoly('(0,1,2,0,1,0)'::polygon)</entry>
1471 <title id="cidr-funcs">IP V4 Functions</title>
1474 <table tocentry="1">
1475 <title><productname>Postgres</productname>IP V4 Functions</title>
1479 <entry>Function</entry>
1480 <entry>Returns</entry>
1481 <entry>Description</entry>
1482 <entry>Example</entry>
1487 <entry>broadcast(cidr)</entry>
1489 <entry>construct broadcast address as text</entry>
1490 <entry>broadcast('192.168.1.5/24')</entry>
1493 <entry>broadcast(inet)</entry>
1495 <entry>construct broadcast address as text</entry>
1496 <entry>broadcast('192.168.1.5/24')</entry>
1499 <entry>host(inet)</entry>
1501 <entry>extract host address as text</entry>
1502 <entry>host('192.168.1.5/24')</entry>
1505 <entry>masklen(cidr)</entry>
1507 <entry>calculate netmask length</entry>
1508 <entry>masklen('192.168.1.5/24')</entry>
1511 <entry>masklen(inet)</entry>
1513 <entry>calculate netmask length</entry>
1514 <entry>masklen('192.168.1.5/24')</entry>
1517 <entry>netmask(inet)</entry>
1519 <entry>construct netmask as text</entry>
1520 <entry>netmask('192.168.1.5/24')</entry>
1530 <title id="aggregate-funcs">Aggregate Functions</title>
1533 <title>Author</title>
1535 Written by <ulink url="mailto:isaac@azartmedia.com">Isaac Wilcox</ulink>
1541 <firstterm>Aggregate functions</firstterm> allow the generation of simple
1542 statistics about the values of particular columns over the selected set
1545 See also <xref linkend="sql" endterm="aggregates-tutorial"> and
1546 <xref linkend="syntax" endterm="aggregates-syntax">.
1548 See also <xref linkend="syntax" endterm="aggregates-syntax">;
1550 the <citetitle>PostgreSQL Tutorial</citetitle> for additional
1551 introductory information.
1555 <table tocentry="1">
1556 <title>Aggregate Functions</title>
1560 <entry>Function</entry>
1561 <entry>Returns</entry>
1562 <entry>Description</entry>
1563 <entry>Example</entry>
1564 <entry>Notes</entry>
1569 <entry>COUNT(*)</entry>
1571 <entry>Counts the selected rows.</entry>
1572 <entry>COUNT(*)</entry>
1576 <entry>COUNT(<replaceable class="parameter">column-name</replaceable>)</entry>
1578 <entry>Counts the selected rows for which the value of <replaceable class="parameter">column-name</replaceable> is not NULL.</entry>
1579 <entry>COUNT(age)</entry>
1583 <entry>SUM(<replaceable class="parameter">column-name</replaceable>)</entry>
1584 <entry>Same as the data type of the column being summed.</entry>
1585 <entry>Finds the total obtained by adding the values of <replaceable class="parameter">column-name</replaceable> across all selected rows.</entry>
1586 <entry>SUM(hours)</entry>
1587 <entry>Summation is supported on the following data types: int8, int4, int2, float4, float8, money, interval, numeric</entry>
1590 <entry>MAX(<replaceable class="parameter">column-name</replaceable>)</entry>
1591 <entry>Same as the data type of the column whose maximum value is sought.</entry>
1592 <entry>The maximum value of <replaceable class="parameter">column-name</replaceable> across all selected rows.</entry>
1593 <entry>MAX(age)</entry>
1594 <entry>Finding the maximum value is supported on the following data types: int8, int4, int2, float4, float8, date, time, timetz, money, timestamp, interval, text, numeric.</entry>
1597 <entry>MIN(<replaceable class="parameter">column-name</replaceable>)</entry>
1598 <entry>same as the data type of the column whose minimum value is sought.</entry>
1599 <entry>The minimum value of <replaceable class="parameter">column-name</replaceable> across all selected rows.</entry>
1600 <entry>MIN(age)</entry>
1601 <entry>Finding the minimum value is supported on the following data types: int8, int4, int2, float4, float8, date, time, timetz, money, timestamp, interval, text, numeric.</entry>
1604 <entry>AVG(<replaceable class="parameter">column-name</replaceable>)</entry>
1605 <entry>Same as the data type of the column being averaged.</entry>
1606 <entry>The average (mean) of the values in the given column across all selected rows.</entry>
1607 <entry>AVG(age)</entry>
1608 <entry>Finding the mean value is supported on the following data types: int8, int4, int2, float4, float8, money, interval, numeric. Note that as the return type is the same as that of the data being averaged, using AVG() on discrete data will give a rounded result.</entry>
1617 <!-- Keep this comment at the end of the file
1622 sgml-minimize-attributes:nil
1623 sgml-always-quote-attributes:t
1626 sgml-parent-document:nil
1627 sgml-default-dtd-file:"./reference.ced"
1628 sgml-exposed-tags:nil
1629 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1630 sgml-local-ecat-files:nil