2 $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.85 2001/11/21 22:33:14 tgl Exp $
3 PostgreSQL documentation
6 <chapter id="functions">
7 <title>Functions and Operators</title>
9 <indexterm zone="functions">
10 <primary>functions</primary>
13 <indexterm zone="functions">
14 <primary>operators</primary>
18 <productname>PostgreSQL</productname> provides a large number of
19 functions and operators for the built-in data types. Users can also
20 define their own functions and operators, as described in the
21 <citetitle>Programmer's Guide</citetitle>. The
22 <application>psql</application> commands <command>\df</command> and
23 <command>\do</command> can be used to show the list of all actually
24 available functions and operators, respectively.
28 If you are concerned about portability then take note that most of
29 the functions and operators described in this chapter, with the
30 exception of the most trivial arithmetic and comparison operators
31 and some explicitly marked functions, are not specified by the <acronym>SQL</acronym>
32 standard. Some of this extended functionality is present in other
33 <acronym>RDBMS</acronym> products, and in many cases this
34 functionality is compatible and consistent between various products.
38 <sect1 id="functions-logical">
39 <title>Logical Operators</title>
41 <indexterm zone="functions-logical">
42 <primary>operators</primary>
43 <secondary>logical</secondary>
47 <primary>Boolean</primary>
48 <secondary>operators</secondary>
49 <see>operators, logical</see>
53 The usual logical operators are available:
56 <primary>and</primary>
57 <secondary>operator</secondary>
62 <secondary>operator</secondary>
66 <primary>not</primary>
67 <secondary>operator</secondary>
76 <acronym>SQL</acronym> uses a three-valued Boolean logic where NULL represents
77 <quote>unknown</quote>. Observe the following truth tables:
83 <entry><replaceable>a</replaceable></entry>
84 <entry><replaceable>b</replaceable></entry>
85 <entry><replaceable>a</replaceable> AND <replaceable>b</replaceable></entry>
86 <entry><replaceable>a</replaceable> OR <replaceable>b</replaceable></entry>
140 <entry><replaceable>a</replaceable></entry>
141 <entry>NOT <replaceable>a</replaceable></entry>
166 <sect1 id="functions-comparison">
167 <title>Comparison Operators</title>
169 <indexterm zone="functions-comparison">
170 <primary>comparison</primary>
171 <secondary>operators</secondary>
175 <title>Comparison Operators</TITLE>
179 <entry>Operator</entry>
180 <entry>Description</entry>
186 <entry> <literal><</literal> </entry>
187 <entry>less than</entry>
191 <entry> <literal>></literal> </entry>
192 <entry>greater than</entry>
196 <entry> <literal><=</literal> </entry>
197 <entry>less than or equal to</entry>
201 <entry> <literal>>=</literal> </entry>
202 <entry>greater than or equal to</entry>
206 <entry> <literal>=</literal> </entry>
211 <entry> <literal><></literal> or <literal>!=</literal> </entry>
212 <entry>not equal</entry>
220 The <literal>!=</literal> operator is converted to
221 <literal><></literal> in the parser stage. It is not
222 possible to implement <literal>!=</literal> and
223 <literal><></literal> operators that do different things.
228 Comparison operators are available for all data types where this
229 makes sense. All comparison operators are binary operators that
230 return values of type <type>boolean</type>; expressions like
231 <literal>1 < 2 < 3</literal> are not valid (because there is
232 no <literal><</literal> operator to compare a Boolean value with
233 <literal>3</literal>).
238 <primary>between</primary>
240 In addition to the comparison operators, the special
241 <token>BETWEEN</token> construct is available.
243 <replaceable>a</replaceable> BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
247 <replaceable>a</replaceable> >= <replaceable>x</replaceable> AND <replaceable>a</replaceable> <= <replaceable>y</replaceable>
251 <replaceable>a</replaceable> NOT BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
255 <replaceable>a</replaceable> < <replaceable>x</replaceable> OR <replaceable>a</replaceable> > <replaceable>y</replaceable>
257 There is no difference between the two respective forms apart from
258 the <acronym>CPU</acronym> cycles required to rewrite the first one
259 into the second one internally.
263 To check whether a value is or is not NULL, use the constructs
265 <replaceable>expression</replaceable> IS NULL
266 <replaceable>expression</replaceable> IS NOT NULL
268 Do <emphasis>not</emphasis> use
269 <literal><replaceable>expression</replaceable> = NULL</literal>
270 because NULL is not <quote>equal to</quote> NULL. (NULL represents
271 an unknown value, and it is not known whether two unknown values are
276 Some applications may (incorrectly) require that
277 <literal><replaceable>expression</replaceable> = NULL</literal>
278 returns true if <replaceable>expression</replaceable> evaluates to
279 the NULL value. To support these applications, the run-time option
280 <varname>transform_null_equals</varname> can be turned on (e.g.,
281 <literal>SET transform_null_equals TO ON;</literal>).
282 <productname>PostgreSQL</productname> would then convert <literal>x
283 = NULL</literal> clauses to <literal>x IS NULL</literal>. This was
284 the default behavior in releases 6.5 through 7.1.
288 Boolean values can also be tested using the constructs
290 <replaceable>expression</replaceable> IS TRUE
291 <replaceable>expression</replaceable> IS NOT TRUE
292 <replaceable>expression</replaceable> IS FALSE
293 <replaceable>expression</replaceable> IS NOT FALSE
294 <replaceable>expression</replaceable> IS UNKNOWN
295 <replaceable>expression</replaceable> IS NOT UNKNOWN
297 These are similar to <literal>IS NULL</literal> in that they will
298 always return TRUE or FALSE, never NULL, even when the operand is NULL.
299 A NULL input is treated as the logical value UNKNOWN.
304 <sect1 id="functions-math">
305 <title>Mathematical Functions and Operators</title>
308 Mathematical operators are provided for many
309 <productname>PostgreSQL</productname> types. For types without
310 common mathematical conventions for all possible permutations
311 (e.g. date/time types) we
312 describe the actual behavior in subsequent sections.
316 <title>Mathematical Operators</title>
322 <entry>Description</entry>
323 <entry>Example</entry>
324 <entry>Result</entry>
330 <entry> <literal>+</literal> </entry>
331 <entry>Addition</entry>
337 <entry> <literal>-</literal> </entry>
338 <entry>Subtraction</entry>
344 <entry> <literal>*</literal> </entry>
345 <entry>Multiplication</entry>
351 <entry> <literal>/</literal> </entry>
352 <entry>Division (integer division truncates results)</entry>
358 <entry> <literal>%</literal> </entry>
359 <entry>Modulo (remainder)</entry>
365 <entry> <literal>^</literal> </entry>
366 <entry>Exponentiation</entry>
367 <entry>2.0 ^ 3.0</entry>
372 <entry> <literal>|/</literal> </entry>
373 <entry>Square root</entry>
374 <entry>|/ 25.0</entry>
379 <entry> <literal>||/</literal> </entry>
380 <entry>Cube root</entry>
381 <entry>||/ 27.0</entry>
386 <entry> <literal>!</literal> </entry>
387 <entry>Factorial</entry>
393 <entry> <literal>!!</literal> </entry>
394 <entry>Factorial (prefix operator)</entry>
400 <entry> <literal>@</literal> </entry>
401 <entry>Absolute value</entry>
402 <entry>@ -5.0</entry>
407 <entry> <literal>&</literal> </entry>
408 <entry>Binary AND</entry>
409 <entry>91 & 15</entry>
414 <entry> <literal>|</literal> </entry>
415 <entry>Binary OR</entry>
416 <entry>32 | 3</entry>
421 <entry> <literal>#</literal> </entry>
422 <entry>Binary XOR</entry>
423 <entry>17 # 5</entry>
428 <entry> <literal>~</literal> </entry>
429 <entry>Binary NOT</entry>
435 <entry> << </entry>
436 <entry>Binary shift left</entry>
437 <entry>1 << 4</entry>
442 <entry> >> </entry>
443 <entry>Binary shift right</entry>
444 <entry>8 >> 2</entry>
453 The <quote>binary</quote> operators are also available for the bit
454 string types <type>BIT</type> and <type>BIT VARYING</type>.
457 <title>Bit String Binary Operators</title>
462 <entry>Example</entry>
463 <entry>Result</entry>
469 <entry>B'10001' & B'01101'</entry>
473 <entry>B'10001' | B'01101'</entry>
477 <entry>B'10001' # B'01101'</entry>
481 <entry>~ B'10001'</entry>
485 <entry>B'10001' << 3</entry>
489 <entry>B'10001' >> 2</entry>
496 Bit string arguments to <literal>&</literal>, <literal>|</literal>,
497 and <literal>#</literal> must be of equal length. When bit
498 shifting, the original length of the string is preserved, as shown
503 <title>Mathematical Functions</title>
507 <entry>Function</entry>
508 <entry>Return Type</entry>
509 <entry>Description</entry>
510 <entry>Example</entry>
511 <entry>Result</entry>
517 <entry><function>abs</function>(<replaceable>x</replaceable>)</entry>
518 <entry>(same as x)</entry>
519 <entry>absolute value</entry>
520 <entry><literal>abs(-17.4)</literal></entry>
525 <entry><function>cbrt</function>(<type>dp</type>)</entry>
526 <entry><type>dp</type></entry>
527 <entry>cube root</entry>
528 <entry><literal>cbrt(27.0)</literal></entry>
533 <entry><function>ceil</function>(<type>numeric</type>)</entry>
534 <entry><type>numeric</type></entry>
535 <entry>smallest integer not less than argument</entry>
536 <entry><literal>ceil(-42.8)</literal></entry>
541 <entry><function>degrees</function>(<type>dp</type>)</entry>
542 <entry><type>dp</type></entry>
543 <entry>radians to degrees</entry>
544 <entry><literal>degrees(0.5)</literal></entry>
545 <entry>28.6478897565412</entry>
549 <entry><function>exp</function>(<type>dp</type>)</entry>
550 <entry><type>dp</type></entry>
551 <entry>exponential</entry>
552 <entry><literal>exp(1.0)</literal></entry>
553 <entry>2.71828182845905</entry>
557 <entry><function>floor</function>(<type>numeric</type>)</entry>
558 <entry><type>numeric</type></entry>
559 <entry>largest integer not greater than argument</entry>
560 <entry><literal>floor(-42.8)</literal></entry>
565 <entry><function>ln</function>(<type>dp</type>)</entry>
566 <entry><type>dp</type></entry>
567 <entry>natural logarithm</entry>
568 <entry><literal>ln(2.0)</literal></entry>
569 <entry>0.693147180559945</entry>
573 <entry><function>log</function>(<type>dp</type>)</entry>
574 <entry><type>dp</type></entry>
575 <entry>base 10 logarithm</entry>
576 <entry><literal>log(100.0)</literal></entry>
581 <entry><function>log</function>(<parameter>b</parameter> <type>numeric</type>,
582 <parameter>x</parameter> <type>numeric</type>)</entry>
583 <entry><type>numeric</type></entry>
584 <entry>logarithm to base <parameter>b</parameter></entry>
585 <entry><literal>log(2.0, 64.0)</literal></entry>
586 <entry>6.0000000000</entry>
590 <entry><function>mod</function>(<parameter>y</parameter>, <parameter>x</parameter>)</entry>
591 <entry>(same as argument types)</entry>
592 <entry>remainder of <parameter>y</parameter>/<parameter>x</parameter></entry>
593 <entry><literal>mod(9,4)</literal></entry>
598 <entry><function>pi</function>()</entry>
599 <entry><type>dp</type></entry>
600 <entry><quote>Pi</quote> constant</entry>
601 <entry><literal>pi()</literal></entry>
602 <entry>3.14159265358979</entry>
606 <entry><function>pow</function>(<parameter>e</parameter> <type>dp</type>,
607 <parameter>n</parameter> <type>dp</type>)</entry>
608 <entry><type>dp</type></entry>
609 <entry>raise a number to exponent <parameter>e</parameter></entry>
610 <entry><literal>pow(9.0, 3.0)</literal></entry>
615 <entry><function>radians</function>(<type>dp</type>)</entry>
616 <entry><type>dp</type></entry>
617 <entry>degrees to radians</entry>
618 <entry><literal>radians(45.0)</literal></entry>
619 <entry>0.785398163397448</entry>
623 <entry><function>random</function>()</entry>
624 <entry><type>dp</type></entry>
625 <entry>value between 0.0 to 1.0</entry>
626 <entry><literal>random()</literal></entry>
631 <entry><function>round</function>(<type>dp</type>)</entry>
632 <entry><type>dp</type></entry>
633 <entry>round to nearest integer</entry>
634 <entry><literal>round(42.4)</literal></entry>
639 <entry><function>round</function>(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type>)</entry>
640 <entry><type>numeric</type></entry>
641 <entry>round to <parameter>s</parameter> decimal places</entry>
642 <entry><literal>round(42.4382, 2)</literal></entry>
647 <entry><function>setseed</function>(<replaceable>new-seed</replaceable>)</entry>
648 <entry>set seed for subsequent random() calls</entry>
649 <entry><literal>setseed(0.54823)</literal></entry>
654 <entry><function>sign</function>(<type>numeric</type>)</entry>
655 <entry><type>numeric</type></entry>
656 <entry>sign of the argument (-1, 0, +1)</entry>
657 <entry><literal>sign(-8.4)</literal></entry>
662 <entry><function>sqrt</function>(<type>dp</type>)</entry>
663 <entry><type>dp</type></entry>
664 <entry>square root</entry>
665 <entry><literal>sqrt(2.0)</literal></entry>
666 <entry>1.4142135623731</entry>
670 <entry><function>trunc</function>(<type>dp</type>)</entry>
671 <entry><type>dp</type></entry>
672 <entry>truncate toward zero</entry>
673 <entry><literal>trunc(42.8)</literal></entry>
678 <entry><function>trunc</function>(<type>numeric</type>, <parameter>s</parameter> <type>integer</type>)</entry>
679 <entry><type>numeric</type></entry>
680 <entry>truncate to <parameter>s</parameter> decimal places</entry>
681 <entry><literal>trunc(42.4382, 2)</literal></entry>
690 In the table above, <literal>dp</literal> indicates <type>double precision</type>.
691 The functions <function>exp</function>, <function>ln</function>,
692 <function>log</function>, <function>pow</function>,
693 <function>round</function> (1 argument), <function>sqrt</function>,
694 and <function>trunc</function> (1 argument) are also available for
695 the type <type>numeric</type> in place of <type>double
697 Functions returning a <type>numeric</type> result take
698 <type>numeric</type> input arguments, unless otherwise specified.
699 Many of these functions are implemented on top
700 of the host system's C library; accuracy and behavior in boundary cases
701 could therefore vary depending on the host system.
705 <title>Trigonometric Functions</title>
710 <entry>Function</entry>
711 <entry>Description</entry>
717 <entry><function>acos</function>(<replaceable>x</replaceable>)</entry>
718 <entry>inverse cosine</entry>
722 <entry><function>asin</function>(<replaceable>x</replaceable>)</entry>
723 <entry>inverse sine</entry>
727 <entry><function>atan</function>(<replaceable>x</replaceable>)</entry>
728 <entry>inverse tangent</entry>
732 <entry><function>atan2</function>(<replaceable>x</replaceable>, <replaceable>y</replaceable>)</entry>
733 <entry>inverse tangent of <replaceable>y</replaceable>/<replaceable>x</replaceable></entry>
737 <entry><function>cos</function>(<replaceable>x</replaceable>)</entry>
738 <entry>cosine</entry>
742 <entry><function>cot</function>(<replaceable>x</replaceable>)</entry>
743 <entry>cotangent</entry>
747 <entry><function>sin</function>(<replaceable>x</replaceable>)</entry>
752 <entry><function>tan</function>(<replaceable>x</replaceable>)</entry>
753 <entry>tangent</entry>
760 All trigonometric functions have arguments and return values of
761 type <type>double precision</type>.
767 <sect1 id="functions-string">
768 <title>String Functions and Operators</title>
771 This section describes functions and operators for examining and
772 manipulating string values. Strings in this context include values
773 of all the types <type>CHARACTER</type>, <type>CHARACTER
774 VARYING</type>, and <type>TEXT</type>. Unless otherwise noted, all
775 of the functions listed below work on all of these types, but be
776 wary of potential effects of the automatic padding when using the
777 <type>CHARACTER</type> type. Generally the functions described
778 here also work on data of non-string types by converting that data
779 to a string representation first. Some functions also exist
780 natively for bit string types.
784 <acronym>SQL</acronym> defines some string functions with a special syntax where
785 certain keywords rather than commas are used to separate the
786 arguments. Details are in <xref linkend="functions-string-sql">.
787 These functions are also implemented using the regular syntax for
788 function invocation. (See <xref linkend="functions-string-other">.)
791 <table id="functions-string-sql">
792 <title><acronym>SQL</acronym> String Functions and Operators</title>
796 <entry>Function</entry>
797 <entry>Return Type</entry>
798 <entry>Description</entry>
799 <entry>Example</entry>
800 <entry>Result</entry>
806 <entry> <parameter>string</parameter> <literal>||</literal> <parameter>string</parameter> </entry>
807 <entry> <type>text</type> </entry>
811 <primary>character strings</primary>
812 <secondary>concatenation</secondary>
815 <entry><literal>'Postgre' || 'SQL'</></entry>
816 <entry><literal>PostgreSQL</></entry>
820 <entry><function>bit_length</function>(<parameter>string</parameter>)</entry>
821 <entry><type>integer</type></entry>
822 <entry>number of bits in string</entry>
823 <entry><literal>bit_length('jose')</literal></entry>
824 <entry><literal>32</literal></entry>
828 <entry><function>char_length</function>(<parameter>string</parameter>) or <function>character_length</function>(<parameter>string</parameter>)</entry>
829 <entry><type>integer</type></entry>
831 number of characters in string
833 <primary>character strings</primary>
834 <secondary>length</secondary>
837 <primary>length</primary>
838 <secondary>character strings</secondary>
839 <see>character strings, length</see>
842 <entry><literal>char_length('jose')</></entry>
843 <entry><literal>4</></entry>
847 <entry><function>lower</function>(<parameter>string</parameter>)</entry>
848 <entry><type>text</type></entry>
849 <entry>Convert string to lower case.</entry>
850 <entry><literal>lower('TOM')</literal></entry>
851 <entry><literal>tom</literal></entry>
855 <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
856 <entry><type>integer</type></entry>
857 <entry>number of bytes in string</entry>
858 <entry><literal>octet_length('jose')</literal></entry>
859 <entry><literal>4</literal></entry>
863 <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
864 <entry><type>integer</type></entry>
865 <entry>location of specified substring</entry>
866 <entry><literal>position('om' in 'Thomas')</literal></entry>
867 <entry><literal>3</literal></entry>
871 <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
872 <entry><type>text</type></entry>
876 <primary>substring</primary>
879 <entry><literal>substring('Thomas' from 2 for 3)</literal></entry>
880 <entry><literal>hom</literal></entry>
885 <function>trim</function>(<optional>leading | trailing | both</optional>
886 <optional><parameter>characters</parameter></optional> from
887 <parameter>string</parameter>)
889 <entry><type>text</type></entry>
891 Removes the longest string containing only the
892 <parameter>characters</parameter> (a space by default) from the
893 beginning/end/both ends of the <parameter>string</parameter>.
895 <entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>
896 <entry><literal>Tom</literal></entry>
900 <entry><function>upper</function>(<parameter>string</parameter>)</entry>
901 <entry><type>text</type></entry>
902 <entry>Convert string to upper case.</entry>
903 <entry><literal>upper('tom')</literal></entry>
904 <entry><literal>TOM</literal></entry>
911 Additional string manipulation functions are available and are
912 listed below. Some of them are used internally to implement the
913 <acronym>SQL</acronym>-standard string functions listed above.
916 <table id="functions-string-other">
917 <title>Other String Functions</title>
921 <entry>Function</entry>
922 <entry>Return Type</entry>
923 <entry>Description</entry>
924 <entry>Example</entry>
925 <entry>Result</entry>
931 <entry><function>ascii</function>(<type>text</type>)</entry>
932 <entry>integer</entry>
933 <entry>Returns the <acronym>ASCII</acronym> code of the first character of the argument.</entry>
934 <entry><literal>ascii('x')</literal></entry>
935 <entry><literal>120</literal></entry>
939 <entry><function>btrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>trim</parameter> <type>text</type>)</entry>
940 <entry><type>text</type></entry>
942 Remove (trim) the longest string consisting only of characters
943 in <parameter>trim</parameter> from the start and end of
944 <parameter>string</parameter>.
946 <entry><literal>btrim('xyxtrimyyx','xy')</literal></entry>
947 <entry><literal>trim</literal></entry>
951 <entry><function>chr</function>(<type>integer</type>)</entry>
952 <entry><type>text</type></entry>
953 <entry>Returns the character with the given <acronym>ASCII</acronym> code.</entry>
954 <entry><literal>chr(65)</literal></entry>
955 <entry><literal>A</literal></entry>
960 <function>convert</function>(<parameter>string</parameter> <type>text</type>,
961 <optional><parameter>src_encoding</parameter> <type>name</type>,</optional>
962 <parameter>dest_encoding</parameter> <type>name</type>)
964 <entry><type>text</type></entry>
966 Converts string using <parameter>dest_encoding</parameter>.
967 The original encoding is specified by
968 <parameter>src_encoding</parameter>. If
969 <parameter>src_encoding</parameter> is omitted, database
972 <entry><literal>convert('text_in_unicode', 'UNICODE', 'LATIN1')</literal></entry>
973 <entry><literal>text_in_unicode</literal> represented in ISO 8859-1</entry>
977 <entry><function>initcap</function>(<type>text</type>)</entry>
978 <entry><type>text</type></entry>
979 <entry>Converts first letter of each word (whitespace separated) to upper case.</entry>
980 <entry><literal>initcap('hi thomas')</literal></entry>
981 <entry><literal>Hi Thomas</literal></entry>
985 <entry><function>length</function>(<parameter>string</parameter>)</entry>
986 <entry><type>integer</type></entry>
990 <primary>character strings</primary>
991 <secondary>length</secondary>
994 <primary>length</primary>
995 <secondary>character strings</secondary>
996 <see>character strings, length</see>
999 <entry><literal>length('jose')</></entry>
1000 <entry><literal>4</></entry>
1005 <function>lpad</function>(<parameter>string</parameter> <type>text</type>,
1006 <parameter>length</parameter> <type>integer</type>
1007 <optional>, <parameter>fill</parameter> <type>text</type></optional>)
1011 Fills up the <parameter>string</parameter> to length
1012 <parameter>length</parameter> by prepending the characters
1013 <parameter>fill</parameter> (a space by default). If the
1014 <parameter>string</parameter> is already longer than
1015 <parameter>length</parameter> then it is truncated (on the
1018 <entry><literal>lpad('hi', 5, 'xy')</literal></entry>
1019 <entry><literal>xyxhi</literal></entry>
1023 <entry><function>ltrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>trim</parameter> <type>text</type>)</entry>
1024 <entry><type>text</type></entry>
1026 Removes the longest string containing only characters from
1027 <parameter>trim</parameter> from the start of the string.
1029 <entry><literal>ltrim('zzzytrim','xyz')</literal></entry>
1030 <entry><literal>trim</literal></entry>
1034 <entry><function>pg_client_encoding</function>()</entry>
1035 <entry><type>name</type></entry>
1037 Returns current client encoding name.
1039 <entry><literal>pg_client_encoding()</literal></entry>
1040 <entry><literal>SQL_ASCII</literal></entry>
1044 <entry><function>repeat</function>(<type>text</type>, <type>integer</type>)</entry>
1045 <entry><type>text</type></entry>
1046 <entry>Repeat text a number of times.</entry>
1047 <entry><literal>repeat('Pg', 4)</literal></entry>
1048 <entry><literal>PgPgPgPg</literal></entry>
1053 <function>rpad</function>(<parameter>string</parameter> <type>text</type>,
1054 <parameter>length</parameter> <type>integer</type>
1055 <optional>, <parameter>fill</parameter> <type>text</type></optional>)
1057 <entry><type>text</type></entry>
1059 Fills up the <parameter>string</parameter> to length
1060 <parameter>length</parameter> by appending the characters
1061 <parameter>fill</parameter> (a space by default). If the
1062 <parameter>string</parameter> is already longer than
1063 <parameter>length</parameter> then it is truncated.
1065 <entry><literal>rpad('hi', 5, 'xy')</literal></entry>
1066 <entry><literal>hixyx</literal></entry>
1070 <entry><function>rtrim</function>(<parameter>string</parameter> text, <parameter>trim</parameter> text)</entry>
1071 <entry><type>text</type></entry>
1073 Removes the longest string containing only characters from
1074 <parameter>trim</parameter> from the end of the string.
1076 <entry><literal>rtrim('trimxxxx','x')</literal></entry>
1077 <entry><literal>trim</literal></entry>
1081 <entry><function>strpos</function>(<parameter>string</parameter>, <parameter>substring</parameter>)</entry>
1082 <entry><type>text</type></entry>
1084 Locates specified substring. (same as
1085 <literal>position(<parameter>substring</parameter> in
1086 <parameter>string</parameter>)</literal>, but note the reversed
1089 <entry><literal>strpos('high','ig')</literal></entry>
1090 <entry><literal>2</literal></entry>
1094 <entry><function>substr</function>(<parameter>string</parameter>, <parameter>from</parameter> <optional>, <parameter>count</parameter></optional>)</entry>
1095 <entry><type>text</type></entry>
1097 Extracts specified substring. (same as <literal>substring(<parameter>string</parameter> from <parameter>from</parameter> for <parameter>count</parameter>)</literal>)
1099 <entry><literal>substr('alphabet', 3, 2)</literal></entry>
1100 <entry><literal>ph</literal></entry>
1104 <entry><function>to_ascii</function>(<type>text</type> <optional>, <parameter>encoding</parameter></optional>)</entry>
1105 <entry><type>text</type></entry>
1106 <entry>Converts text from multibyte encoding to <acronym>ASCII</acronym>.</entry>
1107 <entry><literal>to_ascii('Karel')</literal></entry>
1108 <entry><literal>Karel</literal></entry>
1113 <function>translate</function>(<parameter>string</parameter> <type>text</type>,
1114 <parameter>from</parameter> <type>text</type>,
1115 <parameter>to</parameter> <type>text</type>)
1117 <entry><type>text</type></entry>
1119 Any character in <parameter>string</parameter> that matches a
1120 character in the <parameter>from</parameter> set is replaced by
1121 the corresponding character in the <parameter>to</parameter>
1124 <entry><literal>translate('12345', '14', 'ax')</literal></entry>
1125 <entry><literal>a23x5</literal></entry>
1130 <function>encode</function>(<parameter>data</parameter> <type>bytea</type>,
1131 <parameter>type</parameter> <type>text</type>)
1133 <entry><type>text</type></entry>
1135 Encodes binary data to <acronym>ASCII</acronym>-only representation. Supported
1136 types are: 'base64', 'hex', 'escape'.
1138 <entry><literal>encode('123\\000\\001', 'base64')</literal></entry>
1139 <entry><literal>MTIzAAE=</literal></entry>
1144 <function>decode</function>(<parameter>string</parameter> <type>text</type>,
1145 <parameter>type</parameter> <type>text</type>)
1147 <entry><type>bytea</type></entry>
1149 Decodes binary data from <parameter>string</parameter> previously
1150 encoded with encode(). Parameter type is same as in encode().
1152 <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
1153 <entry><literal>123\000\001</literal></entry>
1161 The <function>to_ascii</function> function supports conversion from
1162 LATIN1, LATIN2, WIN1250 (CP1250) only.
1167 <sect1 id="functions-binarystring">
1168 <title>Binary String Functions and Operators</title>
1171 This section describes functions and operators for examining and
1172 manipulating binary string values. Strings in this context include
1173 values of the type <type>BYTEA</type>.
1177 <acronym>SQL</acronym> defines some string functions with a special syntax where
1178 certain keywords rather than commas are used to separate the
1179 arguments. Details are in <xref linkend="functions-binarystring-sql">.
1180 Some functions are also implemented using the regular syntax for
1181 function invocation. (See <xref linkend="functions-binarystring-other">.)
1184 <table id="functions-binarystring-sql">
1185 <title><acronym>SQL</acronym> Binary String Functions and Operators</title>
1189 <entry>Function</entry>
1190 <entry>Return Type</entry>
1191 <entry>Description</entry>
1192 <entry>Example</entry>
1193 <entry>Result</entry>
1199 <entry> <parameter>string</parameter> <literal>||</literal> <parameter>string</parameter> </entry>
1200 <entry> <type>bytea</type> </entry>
1202 string concatenation
1204 <primary>binary strings</primary>
1205 <secondary>concatenation</secondary>
1208 <entry><literal>'\\\\Postgre'::bytea || '\\047SQL\\000'::bytea</></entry>
1209 <entry><literal>\\Postgre'SQL\000</></entry>
1213 <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
1214 <entry><type>integer</type></entry>
1215 <entry>number of bytes in binary string</entry>
1216 <entry><literal>octet_length('jo\\000se'::bytea)</literal></entry>
1217 <entry><literal>5</literal></entry>
1221 <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
1222 <entry><type>integer</type></entry>
1223 <entry>location of specified substring</entry>
1224 <entry><literal>position('\\000om'::bytea in 'Th\\000omas'::bytea)</literal></entry>
1225 <entry><literal>3</literal></entry>
1229 <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
1230 <entry><type>bytea</type></entry>
1234 <primary>substring</primary>
1237 <entry><literal>substring('Th\\000omas'::bytea from 2 for 3)</literal></entry>
1238 <entry><literal>h\000o</literal></entry>
1243 <function>trim</function>(<optional>both</optional>
1244 <parameter>characters</parameter> from
1245 <parameter>string</parameter>)
1247 <entry><type>bytea</type></entry>
1249 Removes the longest string containing only the
1250 <parameter>characters</parameter> from the
1251 beginning/end/both ends of the <parameter>string</parameter>.
1253 <entry><literal>trim('\\000'::bytea from '\\000Tom\\000'::bytea)</literal></entry>
1254 <entry><literal>Tom</literal></entry>
1262 Additional binary string manipulation functions are available and are
1263 listed below. Some of them are used internally to implement the
1264 <acronym>SQL</acronym>-standard string functions listed above.
1267 <table id="functions-binarystring-other">
1268 <title>Other Binary String Functions</title>
1272 <entry>Function</entry>
1273 <entry>Return Type</entry>
1274 <entry>Description</entry>
1275 <entry>Example</entry>
1276 <entry>Result</entry>
1282 <entry><function>btrim</function>(<parameter>string</parameter> <type>bytea</type>, <parameter>trim</parameter> <type>bytea</type>)</entry>
1283 <entry><type>bytea</type></entry>
1285 Remove (trim) the longest string consisting only of characters
1286 in <parameter>trim</parameter> from the start and end of
1287 <parameter>string</parameter>.
1289 <entry><literal>btrim('\\000trim\\000'::bytea,'\\000'::bytea)</literal></entry>
1290 <entry><literal>trim</literal></entry>
1294 <entry><function>length</function>(<parameter>string</parameter>)</entry>
1295 <entry><type>integer</type></entry>
1297 length of binary string
1299 <primary>binary strings</primary>
1300 <secondary>length</secondary>
1303 <primary>length</primary>
1304 <secondary>binary strings</secondary>
1305 <see>binary strings, length</see>
1308 <entry><literal>length('jo\\000se'::bytea)</></entry>
1309 <entry><literal>5</></entry>
1314 <function>encode</function>(<parameter>string</parameter> <type>bytea</type>,
1315 <parameter>type</parameter> <type>text</type>)
1317 <entry><type>text</type></entry>
1319 Encodes binary string to <acronym>ASCII</acronym>-only representation. Supported
1320 types are: 'base64', 'hex', 'escape'.
1322 <entry><literal>encode('123\\000456'::bytea, 'escape')</literal></entry>
1323 <entry><literal>123\000456</literal></entry>
1328 <function>decode</function>(<parameter>string</parameter> <type>text</type>,
1329 <parameter>type</parameter> <type>text</type>)
1331 <entry><type>bytea</type></entry>
1333 Decodes binary string from <parameter>string</parameter> previously
1334 encoded with encode(). Parameter type is same as in encode().
1336 <entry><literal>decode('123\\000456', 'escape')</literal></entry>
1337 <entry><literal>123\000456</literal></entry>
1347 <sect1 id="functions-matching">
1348 <title>Pattern Matching</title>
1351 There are two separate approaches to pattern matching provided by
1352 <productname>PostgreSQL</productname>: the <acronym>SQL</acronym>
1353 <function>LIKE</function> operator and
1354 <acronym>POSIX</acronym>-style regular expressions.
1359 If you have pattern matching needs that go beyond this, or want to
1360 make pattern-driven substitutions or translations, consider
1361 writing a user-defined function in Perl or Tcl.
1365 <sect2 id="functions-like">
1366 <title>Pattern Matching with <function>LIKE</function></title>
1369 <primary>like</primary>
1373 <replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional> ESCAPE <replaceable>escape-character</replaceable> </optional>
1374 <replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional> ESCAPE <replaceable>escape-character</replaceable> </optional>
1378 Every <replaceable>pattern</replaceable> defines a set of strings.
1379 The <function>LIKE</function> expression returns true if the
1380 <replaceable>string</replaceable> is contained in the set of
1381 strings represented by <replaceable>pattern</replaceable>. (As
1382 expected, the <function>NOT LIKE</function> expression returns
1383 false if <function>LIKE</function> returns true, and vice versa.
1384 An equivalent expression is <literal>NOT
1385 (<replaceable>string</replaceable> LIKE
1386 <replaceable>pattern</replaceable>)</literal>.)
1390 If <replaceable>pattern</replaceable> does not contain percent
1391 signs or underscore, then the pattern only represents the string
1392 itself; in that case <function>LIKE</function> acts like the
1393 equals operator. An underscore (<literal>_</literal>) in
1394 <replaceable>pattern</replaceable> stands for (matches) any single
1395 character; a percent sign (<literal>%</literal>) matches any string
1396 of zero or more characters.
1403 'abc' LIKE 'abc' <lineannotation>true</lineannotation>
1404 'abc' LIKE 'a%' <lineannotation>true</lineannotation>
1405 'abc' LIKE '_b_' <lineannotation>true</lineannotation>
1406 'abc' LIKE 'c' <lineannotation>false</lineannotation>
1412 <function>LIKE</function> pattern matches always cover the entire
1413 string. To match a pattern anywhere within a string, the
1414 pattern must therefore start and end with a percent sign.
1418 To match a literal underscore or percent sign without matching
1419 other characters, the respective character in
1420 <replaceable>pattern</replaceable> must be
1421 preceded by the escape character. The default escape
1422 character is the backslash but a different one may be selected by
1423 using the <literal>ESCAPE</literal> clause. To match the escape
1424 character itself, write two escape characters.
1428 Note that the backslash already has a special meaning in string
1429 literals, so to write a pattern constant that contains a backslash
1430 you must write two backslashes in the query. Thus, writing a pattern
1431 that actually matches a literal backslash means writing four backslashes
1432 in the query. You can avoid this by selecting a different escape
1433 character with <literal>ESCAPE</literal>; then backslash isn't special
1434 to <function>LIKE</> anymore. (But it's still special to the string
1435 literal parser, so you still need two of them.)
1439 It's also possible to select no escape character by writing
1440 <literal>ESCAPE ''</literal>. In this case there is no way to
1441 turn off the special meaning of underscore and percent signs in
1446 The keyword <token>ILIKE</token> can be used instead of
1447 <token>LIKE</token> to make the match case insensitive according
1448 to the active locale. This is not in the <acronym>SQL</acronym> standard but is a
1449 <productname>PostgreSQL</productname> extension.
1453 The operator <literal>~~</literal> is equivalent to
1454 <function>LIKE</function>, and <literal>~~*</literal> corresponds to
1455 <function>ILIKE</function>. There are also
1456 <literal>!~~</literal> and <literal>!~~*</literal> operators that
1457 represent <function>NOT LIKE</function> and <function>NOT
1458 ILIKE</function>. All of these operators are
1459 <productname>PostgreSQL</productname>-specific.
1464 <sect2 id="functions-regexp">
1465 <title><acronym>POSIX</acronym> Regular Expressions</title>
1467 <indexterm zone="functions-regexp">
1468 <primary>regular expressions</primary>
1469 <seealso>pattern matching</seealso>
1473 <title>Regular Expression Match Operators</title>
1478 <entry>Operator</entry>
1479 <entry>Description</entry>
1480 <entry>Example</entry>
1486 <ENTRY> <literal>~</literal> </ENTRY>
1487 <ENTRY>Matches regular expression, case sensitive</ENTRY>
1488 <ENTRY><literal>'thomas' ~ '.*thomas.*'</literal></ENTRY>
1491 <ENTRY> <literal>~*</literal> </ENTRY>
1492 <ENTRY>Matches regular expression, case insensitive</ENTRY>
1493 <ENTRY><literal>'thomas' ~* '.*Thomas.*'</literal></ENTRY>
1496 <ENTRY> <literal>!~</literal> </ENTRY>
1497 <ENTRY>Does not match regular expression, case sensitive</ENTRY>
1498 <ENTRY><literal>'thomas' !~ '.*Thomas.*'</literal></ENTRY>
1501 <ENTRY> <literal>!~*</literal> </ENTRY>
1502 <ENTRY>Does not match regular expression, case insensitive</ENTRY>
1503 <ENTRY><literal>'thomas' !~* '.*vadim.*'</literal></ENTRY>
1510 <acronym>POSIX</acronym> regular expressions provide a more powerful means for
1511 pattern matching than the <function>LIKE</function> function.
1512 Many Unix tools such as <command>egrep</command>,
1513 <command>sed</command>, or <command>awk</command> use a pattern
1514 matching language that is similar to the one described here.
1518 A regular expression is a character sequence that is an
1519 abbreviated definition of a set of strings (a <firstterm>regular
1520 set</firstterm>). A string is said to match a regular expression
1521 if it is a member of the regular set described by the regular
1522 expression. As with <function>LIKE</function>, pattern characters
1523 match string characters exactly unless they are special characters
1524 in the regular expression language --- but regular expressions use
1525 different special characters than <function>LIKE</function> does.
1526 Unlike <function>LIKE</function> patterns, a
1527 regular expression is allowed to match anywhere within a string, unless
1528 the regular expression is explicitly anchored to the beginning or
1533 <!-- derived from the re_format.7 man page -->
1535 Regular expressions (<quote>RE</quote>s), as defined in <acronym>POSIX</acronym>
1536 1003.2, come in two forms: modern REs (roughly those of
1537 <command>egrep</command>; 1003.2 calls these
1538 <quote>extended</quote> REs) and obsolete REs (roughly those of
1539 <command>ed</command>; 1003.2 <quote>basic</quote> REs).
1540 <productname>PostgreSQL</productname> implements the modern form.
1544 A (modern) RE is one or more non-empty
1545 <firstterm>branches</firstterm>, separated by
1546 <literal>|</literal>. It matches anything that matches one of the
1551 A branch is one or more <firstterm>pieces</firstterm>,
1552 concatenated. It matches a match for the first, followed by a
1553 match for the second, etc.
1557 A piece is an <firstterm>atom</firstterm> possibly followed by a
1558 single <literal>*</literal>, <literal>+</literal>,
1559 <literal>?</literal>, or <firstterm>bound</firstterm>. An atom
1560 followed by <literal>*</literal> matches a sequence of 0 or more
1561 matches of the atom. An atom followed by <literal>+</literal>
1562 matches a sequence of 1 or more matches of the atom. An atom
1563 followed by <literal>?</literal> matches a sequence of 0 or 1
1564 matches of the atom.
1568 A <firstterm>bound</firstterm> is <literal>{</literal> followed by
1569 an unsigned decimal integer, possibly followed by
1570 <literal>,</literal> possibly followed by another unsigned decimal
1571 integer, always followed by <literal>}</literal>. The integers
1572 must lie between 0 and <symbol>RE_DUP_MAX</symbol> (255)
1573 inclusive, and if there are two of them, the first may not exceed
1574 the second. An atom followed by a bound containing one integer
1575 <replaceable>i</replaceable> and no comma matches a sequence of
1576 exactly <replaceable>i</replaceable> matches of the atom. An atom
1577 followed by a bound containing one integer
1578 <replaceable>i</replaceable> and a comma matches a sequence of
1579 <replaceable>i</replaceable> or more matches of the atom. An atom
1580 followed by a bound containing two integers
1581 <replaceable>i</replaceable> and <replaceable>j</replaceable>
1582 matches a sequence of <replaceable>i</replaceable> through
1583 <replaceable>j</replaceable> (inclusive) matches of the atom.
1588 A repetition operator (<literal>?</literal>,
1589 <literal>*</literal>, <literal>+</literal>, or bounds) cannot
1590 follow another repetition operator. A repetition operator cannot
1591 begin an expression or subexpression or follow
1592 <literal>^</literal> or <literal>|</literal>.
1597 An <firstterm>atom</firstterm> is a regular expression enclosed in
1598 <literal>()</literal> (matching a match for the regular
1599 expression), an empty set of <literal>()</literal> (matching the
1600 null string), a <firstterm>bracket expression</firstterm> (see
1601 below), <literal>.</literal> (matching any single character),
1602 <literal>^</literal> (matching the null string at the beginning of the
1603 input string), <literal>$</literal> (matching the null string at the end
1604 of the input string), a <literal>\</literal> followed by one of the
1605 characters <literal>^.[$()|*+?{\</literal> (matching that
1606 character taken as an ordinary character), a <literal>\</literal>
1607 followed by any other character (matching that character taken as
1608 an ordinary character, as if the <literal>\</literal> had not been
1609 present), or a single character with no other significance
1610 (matching that character). A <literal>{</literal> followed by a
1611 character other than a digit is an ordinary character, not the
1612 beginning of a bound. It is illegal to end an RE with
1613 <literal>\</literal>.
1617 Note that the backslash (<literal>\</literal>) already has a special
1619 literals, so to write a pattern constant that contains a backslash
1620 you must write two backslashes in the query.
1624 A <firstterm>bracket expression</firstterm> is a list of
1625 characters enclosed in <literal>[]</literal>. It normally matches
1626 any single character from the list (but see below). If the list
1627 begins with <literal>^</literal>, it matches any single character
1628 (but see below) not from the rest of the list. If two characters
1629 in the list are separated by <literal>-</literal>, this is
1630 shorthand for the full range of characters between those two
1631 (inclusive) in the collating sequence,
1632 e.g. <literal>[0-9]</literal> in <acronym>ASCII</acronym> matches
1633 any decimal digit. It is illegal for two ranges to share an
1634 endpoint, e.g. <literal>a-c-e</literal>. Ranges are very
1635 collating-sequence-dependent, and portable programs should avoid
1640 To include a literal <literal>]</literal> in the list, make it the
1641 first character (following a possible <literal>^</literal>). To
1642 include a literal <literal>-</literal>, make it the first or last
1643 character, or the second endpoint of a range. To use a literal
1644 <literal>-</literal> as the first endpoint of a range, enclose it
1645 in <literal>[.</literal> and <literal>.]</literal> to make it a
1646 collating element (see below). With the exception of these and
1647 some combinations using <literal>[</literal> (see next
1648 paragraphs), all other special characters, including
1649 <literal>\</literal>, lose their special significance within a
1654 Within a bracket expression, a collating element (a character, a
1655 multiple-character sequence that collates as if it were a single
1656 character, or a collating-sequence name for either) enclosed in
1657 <literal>[.</literal> and <literal>.]</literal> stands for the
1658 sequence of characters of that collating element. The sequence is
1659 a single element of the bracket expression's list. A bracket
1660 expression containing a multiple-character collating element can thus
1661 match more than one character, e.g. if the collating sequence
1662 includes a <literal>ch</literal> collating element, then the RE
1663 <literal>[[.ch.]]*c</literal> matches the first five characters of
1664 <literal>chchcc</literal>.
1668 Within a bracket expression, a collating element enclosed in
1669 <literal>[=</literal> and <literal>=]</literal> is an equivalence
1670 class, standing for the sequences of characters of all collating
1671 elements equivalent to that one, including itself. (If there are
1672 no other equivalent collating elements, the treatment is as if the
1673 enclosing delimiters were <literal>[.</literal> and
1674 <literal>.]</literal>.) For example, if <literal>o</literal> and
1675 <literal>^</literal> are the members of an equivalence class, then
1676 <literal>[[=o=]]</literal>, <literal>[[=^=]]</literal>, and
1677 <literal>[o^]</literal> are all synonymous. An equivalence class
1678 may not be an endpoint of a range.
1682 Within a bracket expression, the name of a character class
1683 enclosed in <literal>[:</literal> and <literal>:]</literal> stands
1684 for the list of all characters belonging to that class. Standard
1685 character class names are: <literal>alnum</literal>,
1686 <literal>alpha</literal>, <literal>blank</literal>,
1687 <literal>cntrl</literal>, <literal>digit</literal>,
1688 <literal>graph</literal>, <literal>lower</literal>,
1689 <literal>print</literal>, <literal>punct</literal>,
1690 <literal>space</literal>, <literal>upper</literal>,
1691 <literal>xdigit</literal>. These stand for the character classes
1693 <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>.
1694 A locale may provide others. A character class may not be used as
1695 an endpoint of a range.
1699 There are two special cases of bracket expressions: the bracket
1700 expressions <literal>[[:<:]]</literal> and
1701 <literal>[[:>:]]</literal> match the null string at the beginning
1702 and end of a word respectively. A word is defined as a sequence
1703 of word characters which is neither preceded nor followed by word
1704 characters. A word character is an alnum character (as defined by
1705 <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>)
1706 or an underscore. This is an extension, compatible with but not
1707 specified by <acronym>POSIX</acronym> 1003.2, and should be used with caution in
1708 software intended to be portable to other systems.
1712 In the event that an RE could match more than one substring of a
1713 given string, the RE matches the one starting earliest in the
1714 string. If the RE could match more than one substring starting at
1715 that point, it matches the longest. Subexpressions also match the
1716 longest possible substrings, subject to the constraint that the
1717 whole match be as long as possible, with subexpressions starting
1718 earlier in the RE taking priority over ones starting later. Note
1719 that higher-level subexpressions thus take priority over their
1720 lower-level component subexpressions.
1724 Match lengths are measured in characters, not collating
1725 elements. A null string is considered longer than no match at
1726 all. For example, <literal>bb*</literal> matches the three middle
1727 characters of <literal>abbbc</literal>,
1728 <literal>(wee|week)(knights|nights)</literal> matches all ten
1729 characters of <literal>weeknights</literal>, when
1730 <literal>(.*).*</literal> is matched against
1731 <literal>abc</literal> the parenthesized subexpression matches all
1732 three characters, and when <literal>(a*)*</literal> is matched
1733 against <literal>bc</literal> both the whole RE and the
1734 parenthesized subexpression match the null string.
1738 If case-independent matching is specified, the effect is much as
1739 if all case distinctions had vanished from the alphabet. When an
1740 alphabetic that exists in multiple cases appears as an ordinary
1741 character outside a bracket expression, it is effectively
1742 transformed into a bracket expression containing both cases,
1743 e.g. <literal>x</literal> becomes <literal>[xX]</literal>. When
1744 it appears inside a bracket expression, all case counterparts of
1745 it are added to the bracket expression, so that (e.g.)
1746 <literal>[x]</literal> becomes <literal>[xX]</literal> and
1747 <literal>[^x]</literal> becomes <literal>[^xX]</literal>.
1751 There is no particular limit on the length of REs, except insofar
1752 as memory is limited. Memory usage is approximately linear in RE
1753 size, and largely insensitive to RE complexity, except for bounded
1754 repetitions. Bounded repetitions are implemented by macro
1755 expansion, which is costly in time and space if counts are large
1756 or bounded repetitions are nested. An RE like, say,
1757 <literal>((((a{1,100}){1,100}){1,100}){1,100}){1,100}</literal>
1758 will (eventually) run almost any existing machine out of swap
1762 This was written in 1994, mind you. The
1763 numbers have probably changed, but the problem
1768 <!-- end re_format.7 man page -->
1774 <sect1 id="functions-formatting">
1775 <title>Data Type Formatting Functions</title>
1777 <indexterm zone="functions-formatting">
1778 <primary>formatting</primary>
1782 <title>Author</title>
1784 Written by Karel Zak (<email>zakkr@zf.jcu.cz</email>) on 2000-01-24
1789 The <productname>PostgreSQL</productname> formatting functions
1790 provide a powerful set of tools for converting various data types
1791 (date/time, integer, floating point, numeric) to formatted strings
1792 and for converting from formatted strings to specific data types.
1793 These functions all follow a common calling convention: the first
1794 argument is the value to be formatted and the second argument is a
1795 template that defines the output or input format.
1799 <table tocentry="1">
1800 <title>Formatting Functions</title>
1804 <entry>Function</entry>
1805 <entry>Returns</entry>
1806 <entry>Description</entry>
1807 <entry>Example</entry>
1812 <entry><function>to_char</function>(<type>timestamp</type>, <type>text</type>)</entry>
1813 <entry><type>text</type></entry>
1814 <entry>convert time stamp to string</entry>
1815 <entry><literal>to_char(timestamp 'now','HH12:MI:SS')</literal></entry>
1818 <entry><function>to_char</function>(<type>interval</type>, <type>text</type>)</entry>
1819 <entry><type>text</type></entry>
1820 <entry>convert interval to string</entry>
1821 <entry><literal>to_char(interval '15h 2m 12s','HH24:MI:SS')</literal></entry>
1824 <entry><function>to_char</function>(<type>int</type>, <type>text</type>)</entry>
1825 <entry><type>text</type></entry>
1826 <entry>convert int4/int8 to string</entry>
1827 <entry><literal>to_char(125, '999')</literal></entry>
1830 <entry><function>to_char</function>(<type>double precision</type>, <type>text</type>)</entry>
1831 <entry><type>text</type></entry>
1832 <entry>convert real/double precision to string</entry>
1833 <entry><literal>to_char(125.8, '999D9')</literal></entry>
1836 <entry><function>to_char</function>(<type>numeric</type>, <type>text</type>)</entry>
1837 <entry><type>text</type></entry>
1838 <entry>convert numeric to string</entry>
1839 <entry><literal>to_char(numeric '-125.8', '999D99S')</literal></entry>
1842 <entry><function>to_date</function>(<type>text</type>, <type>text</type>)</entry>
1843 <entry><type>date</type></entry>
1844 <entry>convert string to date</entry>
1845 <entry><literal>to_date('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
1848 <entry><function>to_timestamp</function>(<type>text</type>, <type>text</type>)</entry>
1849 <entry><type>timestamp</type></entry>
1850 <entry>convert string to time stamp</entry>
1851 <entry><literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
1854 <entry><function>to_number</function>(<type>text</type>, <type>text</type>)</entry>
1855 <entry><type>numeric</type></entry>
1856 <entry>convert string to numeric</entry>
1857 <entry><literal>to_number('12,454.8-', '99G999D9S')</literal></entry>
1865 In an output template string, there are certain patterns that are
1866 recognized and replaced with appropriately-formatted data from the value
1867 to be formatted. Any text that is not a template pattern is simply
1868 copied verbatim. Similarly, in an input template string template patterns
1869 identify the parts of the input data string to be looked at and the
1870 values to be found there.
1874 <table tocentry="1">
1875 <title>Template patterns for date/time conversions</title>
1879 <entry>Pattern</entry>
1880 <entry>Description</entry>
1885 <entry><literal>HH</literal></entry>
1886 <entry>hour of day (01-12)</entry>
1889 <entry><literal>HH12</literal></entry>
1890 <entry>hour of day (01-12)</entry>
1893 <entry><literal>HH24</literal></entry>
1894 <entry>hour of day (00-23)</entry>
1897 <entry><literal>MI</literal></entry>
1898 <entry>minute (00-59)</entry>
1901 <entry><literal>SS</literal></entry>
1902 <entry>second (00-59)</entry>
1905 <entry><literal>MS</literal></entry>
1906 <entry>millisecond (000-999)</entry>
1909 <entry><literal>US</literal></entry>
1910 <entry>microsecond (000000-999999)</entry>
1913 <entry><literal>SSSS</literal></entry>
1914 <entry>seconds past midnight (0-86399)</entry>
1917 <entry><literal>AM</literal> or <literal>A.M.</literal> or <literal>PM</literal> or <literal>P.M.</literal></entry>
1918 <entry>meridian indicator (upper case)</entry>
1921 <entry><literal>am</literal> or <literal>a.m.</literal> or <literal>pm</literal> or <literal>p.m.</literal></entry>
1922 <entry>meridian indicator (lower case)</entry>
1925 <entry><literal>Y,YYY</literal></entry>
1926 <entry>year (4 and more digits) with comma</entry>
1929 <entry><literal>YYYY</literal></entry>
1930 <entry>year (4 and more digits)</entry>
1933 <entry><literal>YYY</literal></entry>
1934 <entry>last 3 digits of year</entry>
1937 <entry><literal>YY</literal></entry>
1938 <entry>last 2 digits of year</entry>
1941 <entry><literal>Y</literal></entry>
1942 <entry>last digit of year</entry>
1945 <entry><literal>BC</literal> or <literal>B.C.</literal> or <literal>AD</literal> or <literal>A.D.</literal></entry>
1946 <entry>era indicator (upper case)</entry>
1949 <entry><literal>bc</literal> or <literal>b.c.</literal> or <literal>ad</literal> or <literal>a.d.</literal></entry>
1950 <entry>era indicator (lower case)</entry>
1953 <entry><literal>MONTH</literal></entry>
1954 <entry>full upper case month name (blank-padded to 9 chars)</entry>
1957 <entry><literal>Month</literal></entry>
1958 <entry>full mixed case month name (blank-padded to 9 chars)</entry>
1961 <entry><literal>month</literal></entry>
1962 <entry>full lower case month name (blank-padded to 9 chars)</entry>
1965 <entry><literal>MON</literal></entry>
1966 <entry>abbreviated upper case month name (3 chars)</entry>
1969 <entry><literal>Mon</literal></entry>
1970 <entry>abbreviated mixed case month name (3 chars)</entry>
1973 <entry><literal>mon</literal></entry>
1974 <entry>abbreviated lower case month name (3 chars)</entry>
1977 <entry><literal>MM</literal></entry>
1978 <entry>month number (01-12)</entry>
1981 <entry><literal>DAY</literal></entry>
1982 <entry>full upper case day name (blank-padded to 9 chars)</entry>
1985 <entry><literal>Day</literal></entry>
1986 <entry>full mixed case day name (blank-padded to 9 chars)</entry>
1989 <entry><literal>day</literal></entry>
1990 <entry>full lower case day name (blank-padded to 9 chars)</entry>
1993 <entry><literal>DY</literal></entry>
1994 <entry>abbreviated upper case day name (3 chars)</entry>
1997 <entry><literal>Dy</literal></entry>
1998 <entry>abbreviated mixed case day name (3 chars)</entry>
2001 <entry><literal>dy</literal></entry>
2002 <entry>abbreviated lower case day name (3 chars)</entry>
2005 <entry><literal>DDD</literal></entry>
2006 <entry>day of year (001-366)</entry>
2009 <entry><literal>DD</literal></entry>
2010 <entry>day of month (01-31)</entry>
2013 <entry><literal>D</literal></entry>
2014 <entry>day of week (1-7; SUN=1)</entry>
2017 <entry><literal>W</literal></entry>
2018 <entry>week of month (1-5) where first week start on the first day of the month</entry>
2021 <entry><literal>WW</literal></entry>
2022 <entry>week number of year (1-53) where first week start on the first day of the year</entry>
2025 <entry><literal>IW</literal></entry>
2026 <entry>ISO week number of year (The first Thursday of the new year is in week 1.)</entry>
2029 <entry><literal>CC</literal></entry>
2030 <entry>century (2 digits)</entry>
2033 <entry><literal>J</literal></entry>
2034 <entry>Julian Day (days since January 1, 4712 BC)</entry>
2037 <entry><literal>Q</literal></entry>
2038 <entry>quarter</entry>
2041 <entry><literal>RM</literal></entry>
2042 <entry>month in Roman Numerals (I-XII; I=January) - upper case</entry>
2045 <entry><literal>rm</literal></entry>
2046 <entry>month in Roman Numerals (I-XII; I=January) - lower case</entry>
2049 <entry><literal>TZ</literal></entry>
2050 <entry>timezone name - upper case</entry>
2053 <entry><literal>tz</literal></entry>
2054 <entry>timezone name - lower case</entry>
2062 Certain modifiers may be applied to any template pattern to alter its
2063 behavior. For example, <quote><literal>FMMonth</literal></quote>
2064 is the <quote><literal>Month</literal></quote> pattern with the
2065 <quote><literal>FM</literal></quote> prefix.
2069 <table tocentry="1">
2070 <title>Template pattern modifiers for date/time conversions</title>
2074 <entry>Modifier</entry>
2075 <entry>Description</entry>
2076 <entry>Example</entry>
2081 <entry><literal>FM</literal> prefix</entry>
2082 <entry>fill mode (suppress padding blanks and zeroes)</entry>
2083 <entry><literal>FMMonth</literal></entry>
2086 <entry><literal>TH</literal> suffix</entry>
2087 <entry>add upper-case ordinal number suffix</entry>
2088 <entry><literal>DDTH</literal></entry>
2091 <entry><literal>th</literal> suffix</entry>
2092 <entry>add lower-case ordinal number suffix</entry>
2093 <entry><literal>DDth</literal></entry>
2096 <entry><literal>FX</literal> prefix</entry>
2097 <entry>Fixed format global option (see below)</entry>
2098 <entry><literal>FX Month DD Day</literal></entry>
2101 <entry><literal>SP</literal> suffix</entry>
2102 <entry>spell mode (not yet implemented)</entry>
2103 <entry><literal>DDSP</literal></entry>
2116 <literal>FM</literal> suppresses leading zeroes or trailing blanks
2117 that would otherwise be added to make the output of a pattern be
2124 <function>to_timestamp</function> and <function>to_date</function>
2125 skip multiple blank spaces in the input string if the <literal>FX</literal> option
2126 is not used. <literal>FX</literal> must be specified as the first item
2127 in the template; for example
2128 <literal>to_timestamp('2000 JUN','YYYY MON')</literal> is right, but
2129 <literal>to_timestamp('2000 JUN','FXYYYY MON')</literal> returns an error,
2130 because <function>to_timestamp</function> expects one blank space only.
2136 If a backslash (<quote><literal>\</literal></quote>) is desired
2137 in a string constant, a double backslash
2138 (<quote><literal>\\</literal></quote>) must be entered; for
2139 example <literal>'\\HH\\MI\\SS'</literal>. This is true for
2140 any string constant in <productname>PostgreSQL</productname>.
2146 Ordinary text is allowed in <function>to_char</function>
2147 templates and will be output literally. You can put a substring
2148 in double quotes to force it to be interpreted as literal text
2149 even if it contains pattern keywords. For example, in
2150 <literal>'"Hello Year: "YYYY'</literal>, the <literal>YYYY</literal>
2151 will be replaced by year data, but the single <literal>Y</literal>
2158 If you want to have a double quote in the output you must
2159 precede it with a backslash, for example <literal>'\\"YYYY
2160 Month\\"'</literal>. <!-- "" font-lock sanity :-) -->
2166 <literal>YYYY</literal> conversion from string to <type>timestamp</type> or
2167 <type>date</type> is restricted if you use a year with more than 4 digits. You must
2168 use some non-digit character or template after <literal>YYYY</literal>,
2169 otherwise the year is always interpreted as 4 digits. For example
2171 <literal>to_date('200001131', 'YYYYMMDD')</literal> will be
2172 interpreted as a 4-digit year; better is to use a non-digit
2173 separator after the year, like
2174 <literal>to_date('20000-1131', 'YYYY-MMDD')</literal> or
2175 <literal>to_date('20000Nov31', 'YYYYMonDD')</literal>.
2181 Millisecond <literal>MS</literal> and microsecond <literal>US</literal>
2182 values are in conversion from string to time stamp used as part of
2183 second after decimal point. For example
2184 <literal>to_timestamp('12:3', 'SS:MS')</literal> is not 3 milliseconds,
2185 but 300, because the conversion count it as <literal>12 + 0.3</literal>.
2186 It means for format 'SS:MS' is '12:3' or '12:30' or '12:300' same
2187 number of milliseconds. For the three milliseconds must be used
2188 '12:003' that the conversion count as
2189 <literal> 12 + 0.003 = 12.003 seconds </literal>. Here is a more
2191 <literal>to_timestamp('15:12:02.020.001230','HH:MI:SS.MS.US')</literal>
2192 is 15 hours, 12 minutes, 2.021230 seconds.
2199 <table tocentry="1">
2200 <title>Template patterns for numeric conversions</title>
2204 <entry>Pattern</entry>
2205 <entry>Description</entry>
2210 <entry><literal>9</literal></entry>
2211 <entry>value with the specified number of digits</entry>
2214 <entry><literal>0</literal></entry>
2215 <entry>value with leading zeros</entry>
2218 <entry><literal>.</literal> (period)</entry>
2219 <entry>decimal point</entry>
2222 <entry><literal>,</literal> (comma)</entry>
2223 <entry>group (thousand) separator</entry>
2226 <entry><literal>PR</literal></entry>
2227 <entry>negative value in angle brackets</entry>
2230 <entry><literal>S</literal></entry>
2231 <entry>negative value with minus sign (uses locale)</entry>
2234 <entry><literal>L</literal></entry>
2235 <entry>currency symbol (uses locale)</entry>
2238 <entry><literal>D</literal></entry>
2239 <entry>decimal point (uses locale)</entry>
2242 <entry><literal>G</literal></entry>
2243 <entry>group separator (uses locale)</entry>
2246 <entry><literal>MI</literal></entry>
2247 <entry>minus sign in specified position (if number < 0)</entry>
2250 <entry><literal>PL</literal></entry>
2251 <entry>plus sign in specified position (if number > 0)</entry>
2254 <entry><literal>SG</literal></entry>
2255 <entry>plus/minus sign in specified position</entry>
2258 <entry><literal>RN</literal></entry>
2259 <entry>roman numeral (input between 1 and 3999)</entry>
2262 <entry><literal>TH</literal> or <literal>th</literal></entry>
2263 <entry>convert to ordinal number</entry>
2266 <entry><literal>V</literal></entry>
2267 <entry>shift <replaceable>n</replaceable> digits (see
2271 <entry><literal>EEEE</literal></entry>
2272 <entry>scientific numbers (not supported yet)</entry>
2285 A sign formatted using 'SG', 'PL' or 'MI' is not an anchor in
2286 the number; for example,
2287 to_char(-12, 'S9999') produces <literal>' -12'</literal>,
2288 but to_char(-12, 'MI9999') produces <literal>'- 12'</literal>.
2289 The Oracle implementation does not allow the use of
2290 <literal>MI</literal> ahead of <literal>9</literal>, but rather
2291 requires that <literal>9</literal> precede
2292 <literal>MI</literal>.
2298 <literal>9</literal> specifies a value with the same number of
2299 digits as there are <literal>9</literal>s. If a digit is
2300 not available use blank space.
2306 <literal>TH</literal> does not convert values less than zero
2307 and does not convert decimal numbers.
2313 <literal>PL</literal>, <literal>SG</literal>, and
2314 <literal>TH</literal> are <productname>PostgreSQL</productname>
2321 <literal>V</literal> effectively
2322 multiplies the input values by
2323 <literal>10^<replaceable>n</replaceable></literal>, where
2324 <replaceable>n</replaceable> is the number of digits following
2325 <literal>V</literal>.
2326 <function>to_char</function> does not support the use of
2327 <literal>V</literal> combined with a decimal point.
2328 (E.g., <literal>99.9V99</literal> is not allowed.)
2335 <table tocentry="1">
2336 <title><function>to_char</function> Examples</title>
2340 <entry>Input</entry>
2341 <entry>Output</entry>
2346 <entry><literal>to_char(now(),'Day, DD HH12:MI:SS')</literal></entry>
2347 <entry><literal>'Tuesday , 06 05:39:18'</literal></entry>
2350 <entry><literal>to_char(now(),'FMDay, FMDD HH12:MI:SS')</literal></entry>
2351 <entry><literal>'Tuesday, 6 05:39:18'</literal></entry>
2354 <entry><literal>to_char(-0.1,'99.99')</literal></entry>
2355 <entry><literal>' -.10'</literal></entry>
2358 <entry><literal>to_char(-0.1,'FM9.99')</literal></entry>
2359 <entry><literal>'-.1'</literal></entry>
2362 <entry><literal>to_char(0.1,'0.9')</literal></entry>
2363 <entry><literal>' 0.1'</literal></entry>
2366 <entry><literal>to_char(12,'9990999.9')</literal></entry>
2367 <entry><literal>' 0012.0'</literal></entry>
2370 <entry><literal>to_char(12,'FM9990999.9')</literal></entry>
2371 <entry><literal>'0012'</literal></entry>
2374 <entry><literal>to_char(485,'999')</literal></entry>
2375 <entry><literal>' 485'</literal></entry>
2378 <entry><literal>to_char(-485,'999')</literal></entry>
2379 <entry><literal>'-485'</literal></entry>
2382 <entry><literal>to_char(485,'9 9 9')</literal></entry>
2383 <entry><literal>' 4 8 5'</literal></entry>
2386 <entry><literal>to_char(1485,'9,999')</literal></entry>
2387 <entry><literal>' 1,485'</literal></entry>
2390 <entry><literal>to_char(1485,'9G999')</literal></entry>
2391 <entry><literal>' 1 485'</literal></entry>
2394 <entry><literal>to_char(148.5,'999.999')</literal></entry>
2395 <entry><literal>' 148.500'</literal></entry>
2398 <entry><literal>to_char(148.5,'999D999')</literal></entry>
2399 <entry><literal>' 148,500'</literal></entry>
2402 <entry><literal>to_char(3148.5,'9G999D999')</literal></entry>
2403 <entry><literal>' 3 148,500'</literal></entry>
2406 <entry><literal>to_char(-485,'999S')</literal></entry>
2407 <entry><literal>'485-'</literal></entry>
2410 <entry><literal>to_char(-485,'999MI')</literal></entry>
2411 <entry><literal>'485-'</literal></entry>
2414 <entry><literal>to_char(485,'999MI')</literal></entry>
2415 <entry><literal>'485'</literal></entry>
2418 <entry><literal>to_char(485,'PL999')</literal></entry>
2419 <entry><literal>'+485'</literal></entry>
2422 <entry><literal>to_char(485,'SG999')</literal></entry>
2423 <entry><literal>'+485'</literal></entry>
2426 <entry><literal>to_char(-485,'SG999')</literal></entry>
2427 <entry><literal>'-485'</literal></entry>
2430 <entry><literal>to_char(-485,'9SG99')</literal></entry>
2431 <entry><literal>'4-85'</literal></entry>
2434 <entry><literal>to_char(-485,'999PR')</literal></entry>
2435 <entry><literal>'<485>'</literal></entry>
2438 <entry><literal>to_char(485,'L999')</literal></entry>
2439 <entry><literal>'DM 485</literal></entry>
2442 <entry><literal>to_char(485,'RN')</literal></entry>
2443 <entry><literal>' CDLXXXV'</literal></entry>
2446 <entry><literal>to_char(485,'FMRN')</literal></entry>
2447 <entry><literal>'CDLXXXV'</literal></entry>
2450 <entry><literal>to_char(5.2,'FMRN')</literal></entry>
2451 <entry><literal>V</literal></entry>
2454 <entry><literal>to_char(482,'999th')</literal></entry>
2455 <entry><literal>' 482nd'</literal></entry>
2458 <entry><literal>to_char(485, '"Good number:"999')</literal></entry>
2459 <entry><literal>'Good number: 485'</literal></entry>
2462 <entry><literal>to_char(485.8,'"Pre:"999" Post:" .999')</literal></entry>
2463 <entry><literal>'Pre: 485 Post: .800'</literal></entry>
2466 <entry><literal>to_char(12,'99V999')</literal></entry>
2467 <entry><literal>' 12000'</literal></entry>
2470 <entry><literal>to_char(12.4,'99V999')</literal></entry>
2471 <entry><literal>' 12400'</literal></entry>
2474 <entry><literal>to_char(12.45, '99V9')</literal></entry>
2475 <entry><literal>' 125'</literal></entry>
2484 <sect1 id="functions-datetime">
2485 <title>Date/Time Functions and Operators</title>
2488 <xref linkend="functions-datetime-table"> shows the available
2489 functions for date/time value processing.
2490 <xref linkend="operators-datetime-table"> illustrates the
2491 behaviors of the basic arithmetic
2492 operators (<literal>+</literal>, <literal>*</literal>, etc.).
2493 For formatting functions, refer to <xref
2494 linkend="functions-formatting">. You should be familiar with the
2495 background information on date/time data types (see <xref
2496 linkend="datatype-datetime">).
2500 The date/time operators described below behave similarly for types
2501 involving time zones as well as those without.
2503 <table id="operators-datetime-table">
2504 <title>Date/Time Operators</title>
2510 <entry>Example</entry>
2511 <entry>Result</entry>
2517 <entry> <literal>+</literal> </entry>
2518 <entry><type>timestamp</type> '2001-09-28 01:00' + <type>interval</type> '23 hours'</entry>
2519 <entry><type>timestamp</type> '2001-09-29 00:00'</entry>
2523 <entry> <literal>+</literal> </entry>
2524 <entry><type>date</type> '2001-09-28' + <type>interval</type> '1 hour'</entry>
2525 <entry><type>timestamp</type> '2001-09-28 01:00'</entry>
2529 <entry> <literal>+</literal> </entry>
2530 <entry><type>time</type> '01:00' + <type>interval</type> '3 hours'</entry>
2531 <entry><type>time</type> '04:00'</entry>
2535 <entry> <literal>-</literal> </entry>
2536 <entry><type>timestamp</type> '2001-09-28 23:00' - <type>interval</type> '23 hours'</entry>
2537 <entry><type>timestamp</type> '2001-09-28'</entry>
2541 <entry> <literal>-</literal> </entry>
2542 <entry><type>date</type> '2001-09-28' + <type>interval</type> '1 hour'</entry>
2543 <entry><type>timestamp</type> '2001-09-27 23:00'</entry>
2547 <entry> <literal>-</literal> </entry>
2548 <entry><type>time</type> '05:00' + <type>interval</type> '2 hours'</entry>
2549 <entry><type>time</type> '03:00'</entry>
2553 <entry> <literal>-</literal> </entry>
2554 <entry><type>interval</type> '2 hours' - <type>time</type> '05:00'</entry>
2555 <entry><type>time</type> '03:00:00'</entry>
2559 <entry> <literal>*</literal> </entry>
2560 <entry><type>interval</type> '1 hour' * <type>int</type> '3'</entry>
2561 <entry><type>interval</type> '03:00'</entry>
2565 <entry> <literal>/</literal> </entry>
2566 <entry><type>interval</type> '1 hour' / <type>int</type> '3'</entry>
2567 <entry><type>interval</type> '00:20'</entry>
2575 The date/time functions are summarized below, with additional
2576 details in subsequent sections.
2578 <table id="functions-datetime-table">
2579 <title>Date/Time Functions</title>
2584 <entry>Return Type</entry>
2585 <entry>Description</entry>
2586 <entry>Example</entry>
2587 <entry>Result</entry>
2593 <entry><function>age</function>(<type>timestamp</type>)</entry>
2594 <entry><type>interval</type></entry>
2595 <entry>Subtract from today</entry>
2596 <entry><literal>age(timestamp '1957-06-13')</literal></entry>
2597 <entry><literal>43 years 8 mons 3 days</literal></entry>
2601 <entry><function>age</function>(<type>timestamp</type>, <type>timestamp</type>)</entry>
2602 <entry><type>interval</type></entry>
2603 <entry>Subtract arguments</entry>
2604 <entry><literal>age('2001-04-10', timestamp '1957-06-13')</literal></entry>
2605 <entry><literal>43 years 9 mons 27 days</literal></entry>
2609 <entry><function>current_date</function></entry>
2610 <entry><type>date</type></entry>
2611 <entry>Today's date; see <link
2612 linkend="functions-datetime-current">below</link>
2619 <entry><function>current_time</function></entry>
2620 <entry><type>time</type></entry>
2621 <entry>Time of day; see <link
2622 linkend="functions-datetime-current">below</link>
2629 <entry><function>current_timestamp</function></entry>
2630 <entry><type>timestamp</type></entry>
2631 <entry>date and time; see also <link
2632 linkend="functions-datetime-current">below</link>
2639 <entry><function>date_part</function>(<type>text</type>, <type>timestamp</type>)</entry>
2640 <entry><type>double precision</type></entry>
2641 <entry>Get subfield (equivalent to
2642 <function>extract</function>); see also <link
2643 linkend="functions-datetime-datepart">below</link>
2645 <entry><literal>date_part('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
2646 <entry><literal>20</literal></entry>
2650 <entry><function>date_part</function>(<type>text</type>, <type>interval</type>)</entry>
2651 <entry><type>double precision</type></entry>
2652 <entry>Get subfield (equivalent to
2653 <function>extract</function>); see also <link
2654 linkend="functions-datetime-datepart">below</link>
2656 <entry><literal>date_part('month', interval '2 years 3 months')</literal></entry>
2657 <entry><literal>3</literal></entry>
2661 <entry><function>date_trunc</function>(<type>text</type>, <type>timestamp</type>)</entry>
2662 <entry><type>timestamp</type></entry>
2663 <entry>Truncate to specified precision; see also <link
2664 linkend="functions-datetime-trunc">below</link>
2666 <entry><literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
2667 <entry><literal>2001-02-16 20:00:00+00</literal></entry>
2671 <entry><function>extract</function>(<parameter>field</parameter> from <type>timestamp</type>)</entry>
2672 <entry><type>double precision</type></entry>
2673 <entry>Get subfield; see also <link
2674 linkend="functions-datetime-extract">below</link>
2676 <entry><literal>extract(hour from timestamp '2001-02-16 20:38:40')</literal></entry>
2677 <entry><literal>20</literal></entry>
2681 <entry><function>extract</function>(<parameter>field</parameter> from <type>interval</type>)</entry>
2682 <entry><type>double precision</type></entry>
2683 <entry>Get subfield; see also <link
2684 linkend="functions-datetime-extract">below</link>
2686 <entry><literal>extract(month from interval '2 years 3 months')</literal></entry>
2687 <entry><literal>3</literal></entry>
2691 <entry><function>isfinite</function>(<type>timestamp</type>)</entry>
2692 <entry><type>boolean</type></entry>
2693 <entry>Test for finite time stamp (neither invalid nor infinity)</entry>
2694 <entry><literal>isfinite(timestamp '2001-02-16 21:28:30')</literal></entry>
2695 <entry><literal>true</literal></entry>
2699 <entry><function>isfinite</function>(<type>interval</type>)</entry>
2700 <entry><type>boolean</type></entry>
2701 <entry>Test for finite interval</entry>
2702 <entry><literal>isfinite(interval '4 hours')</literal></entry>
2703 <entry><literal>true</literal></entry>
2707 <entry><function>now</function>()</entry>
2708 <entry><type>timestamp</type></entry>
2709 <entry>Current date and time (equivalent to
2710 <function>current_timestamp</function>); see also <link
2711 linkend="functions-datetime-current">below</link>
2718 <entry><function>timeofday()</function></entry>
2719 <entry><type>text</type></entry>
2720 <entry>High-precision date and time; see also <link
2721 linkend="functions-datetime-current">below</link>
2723 <entry><literal>timeofday()</literal></entry>
2724 <entry><literal>Wed Feb 21 17:01:13.000126 2001 EST</literal></entry>
2728 <entry><function>timestamp</function>(<type>date</type>)</entry>
2729 <entry><type>timestamp</type></entry>
2730 <entry><type>date</type> to <type>timestamp</type></entry>
2731 <entry><literal>timestamp(date '2000-12-25')</literal></entry>
2732 <entry><literal>2000-12-25 00:00:00</literal></entry>
2736 <entry><function>timestamp</function>(<type>date</type>, <type>time</type>)</entry>
2737 <entry><type>timestamp</type></entry>
2738 <entry><type>date</type> and <type>time</type> to <type>timestamp</type></entry>
2739 <entry><literal>timestamp(date '1998-02-24',time '23:07')</literal></entry>
2740 <entry><literal>1998-02-24 23:07:00</literal></entry>
2747 <sect2 id="functions-datetime-extract">
2748 <title><function>EXTRACT</function>, <function>date_part</function></title>
2751 EXTRACT (<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
2755 The <function>extract</function> function retrieves sub-fields
2756 from date/time values, such as year or hour.
2757 <replaceable>source</replaceable> is a value expression that
2758 evaluates to type <type>timestamp</type> or <type>interval</type>.
2759 (Expressions of type <type>date</type> or <type>time</type> will
2760 be cast to <type>timestamp</type> and can therefore be used as
2761 well.) <replaceable>field</replaceable> is an identifier or
2762 string that selects what field to extract from the source value.
2763 The <function>extract</function> function returns values of type
2764 <type>double precision</type>.
2765 The following are valid values:
2767 <!-- alphabetical -->
2770 <term><literal>century</literal></term>
2773 The year field divided by 100
2778 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
2779 <lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
2784 Note that the result for the century field is simply the year field
2785 divided by 100, and not the conventional definition which puts most
2786 years in the 1900's in the twentieth century.
2792 <term><literal>day</literal></term>
2795 The day (of the month) field (1 - 31)
2800 SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
2801 <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
2808 <term><literal>decade</literal></term>
2811 The year field divided by 10
2816 SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
2817 <lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>
2824 <term><literal>dow</literal></term>
2827 The day of the week (0 - 6; Sunday is 0) (for
2828 <type>timestamp</type> values only)
2833 SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
2834 <lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
2841 <term><literal>doy</literal></term>
2844 The day of the year (1 - 365/366) (for <type>timestamp</type> values only)
2848 SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
2849 <lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>
2856 <term><literal>epoch</literal></term>
2859 For <type>date</type> and <type>timestamp</type> values, the
2860 number of seconds since 1970-01-01 00:00:00-00 (Result may be
2861 negative.); for <type>interval</type> values, the total number
2862 of seconds in the interval
2867 SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
2868 <lineannotation>Result: </lineannotation><computeroutput>982352320</computeroutput>
2870 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
2871 <lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>
2878 <term><literal>hour</literal></term>
2881 The hour field (0 - 23)
2886 SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
2887 <lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
2894 <term><literal>microseconds</literal></term>
2897 The seconds field, including fractional parts, multiplied by 1
2898 000 000. Note that this includes full seconds.
2903 SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
2904 <lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>
2911 <term><literal>millennium</literal></term>
2914 The year field divided by 1000
2919 SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
2920 <lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
2925 Note that the result for the millennium field is simply the year field
2926 divided by 1000, and not the conventional definition which puts
2927 years in the 1900's in the second millennium.
2933 <term><literal>milliseconds</literal></term>
2936 The seconds field, including fractional parts, multiplied by
2937 1000. Note that this includes full seconds.
2942 SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
2943 <lineannotation>Result: </lineannotation><computeroutput>28500</computeroutput>
2950 <term><literal>minute</literal></term>
2953 The minutes field (0 - 59)
2958 SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
2959 <lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>
2966 <term><literal>month</literal></term>
2969 For <type>timestamp</type> values, the number of the month
2970 within the year (1 - 12) ; for <type>interval</type> values
2971 the number of months, modulo 12 (0 - 11)
2976 SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
2977 <lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
2979 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
2980 <lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
2982 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
2983 <lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
2990 <term><literal>quarter</literal></term>
2993 The quarter of the year (1 - 4) that the day is in (for
2994 <type>timestamp</type> values only)
2999 SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
3000 <lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
3007 <term><literal>second</literal></term>
3010 The seconds field, including fractional parts (0 -
3011 59<footnote><simpara>60 if leap seconds are
3012 implemented by the operating system</simpara></footnote>)
3017 SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
3018 <lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
3020 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
3021 <lineannotation>Result: </lineannotation><computeroutput>28.5</computeroutput>
3028 <term><literal>timezone</literal></term>
3031 The time zone offset. XXX But in what units?
3038 <term><literal>timezone_hour</literal></term>
3041 The hour component of the time zone offset.
3047 <term><literal>timezone_minute</literal></term>
3050 The minute component of the time zone offset.
3056 <term><literal>week</literal></term>
3059 From a <type>timestamp</type> value, calculate the number of
3060 the week of the year that the day is in. By definition
3061 (<acronym>ISO</acronym> 8601), the first week of a year
3062 contains January 4 of that year. (The <acronym>ISO</acronym>
3063 week starts on Monday.) In other words, the first Thursday of
3064 a year is in week 1 of that year.
3069 SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
3070 <lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
3077 <term><literal>year</literal></term>
3085 SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
3086 <lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>
3097 The <function>extract</function> function is primarily intended
3098 for computational processing. For formatting date/time values for
3099 display, see <xref linkend="functions-formatting">.
3102 <anchor id="functions-datetime-datepart">
3104 The <function>date_part</function> function is modeled on the traditional
3105 <productname>Ingres</productname> equivalent to the
3106 <acronym>SQL</acronym>-function <function>extract</function>:
3108 date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
3110 Note that here the <replaceable>field</replaceable> value needs to
3111 be a string. The valid field values for
3112 <function>date_part</function> are the same as for
3113 <function>extract</function>.
3118 SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
3119 <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
3121 SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
3122 <lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
3128 <sect2 id="functions-datetime-trunc">
3129 <title><function>date_trunc</function></title>
3132 The function <function>date_trunc</function> is conceptually
3133 similar to the <function>trunc</function> function for numbers.
3138 date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
3140 <replaceable>source</replaceable> is a value expression of type
3141 <type>timestamp</type> (values of type <type>date</type> and
3142 <type>time</type> are cast automatically).
3143 <replaceable>field</replaceable> selects to which precision to
3144 truncate the time stamp value. The return value is of type
3145 <type>timestamp</type> with all fields that are less than the
3146 selected one set to zero (or one, for day and month).
3150 Valid values for <replaceable>field</replaceable> are:
3152 <member>microseconds</member>
3153 <member>milliseconds</member>
3154 <member>second</member>
3155 <member>minute</member>
3156 <member>hour</member>
3157 <member>day</member>
3158 <member>month</member>
3159 <member>year</member>
3160 <member>decade</member>
3161 <member>century</member>
3162 <member>millennium</member>
3169 SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
3170 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00+00</computeroutput>
3172 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
3173 <lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00+00</computeroutput>
3179 <sect2 id="functions-datetime-current">
3180 <title>Current Date/Time</title>
3183 <primary>date</primary>
3184 <secondary>current</secondary>
3188 <primary>time</primary>
3189 <secondary>current</secondary>
3193 The following functions are available to obtain the current date and/or
3200 Note that because of the requirements of the
3201 <acronym>SQL</acronym> standard, these functions must not be
3202 called with trailing parentheses.
3207 SELECT CURRENT_TIME;
3208 <computeroutput>19:07:32</computeroutput>
3210 SELECT CURRENT_DATE;
3211 <computeroutput>2001-02-17</computeroutput>
3213 SELECT CURRENT_TIMESTAMP;
3214 <computeroutput>2001-02-17 19:07:32-05</computeroutput>
3219 The function <function>now()</function> is the traditional
3220 <productname>PostgreSQL</productname> equivalent to
3221 <function>CURRENT_TIMESTAMP</function>.
3225 There is also <function>timeofday()</function>, which returns current
3226 time to higher precision than the <function>CURRENT_TIMESTAMP</function>
3233 Sat Feb 17 19:07:32.000126 2001 EST
3238 <function>timeofday()</function> uses the operating system call
3239 <function>gettimeofday(2)</function>, which may have resolution as
3240 good as microseconds (depending on your platform); the other functions
3241 rely on <function>time(2)</function> which is restricted to one-second
3242 resolution. For historical reasons, <function>timeofday()</function>
3243 returns its result as a text string rather than a <type>timestamp</type> value.
3247 It is quite important to realize that
3248 <function>CURRENT_TIMESTAMP</function> and related functions all return
3249 the time as of the start of the current transaction; their values do not
3250 increment while a transaction is running. But
3251 <function>timeofday()</function> returns the actual current time.
3255 All the date/time data types also accept the special literal value
3256 <literal>now</> to specify the current date and time. Thus,
3257 the following three all return the same result:
3259 SELECT CURRENT_TIMESTAMP;
3261 SELECT TIMESTAMP 'now';
3265 You do not want to use the third form when specifying a DEFAULT
3266 value while creating a table. The system will convert <literal>now</>
3267 to a <type>timestamp</type> as soon as the constant is parsed, so that when
3268 the default value is needed,
3269 the time of the table creation would be used! The first two
3270 forms will not be evaluated until the default value is used,
3271 because they are function calls. Thus they will give the desired
3272 behavior of defaulting to the time of row insertion.
3280 <sect1 id="functions-geometry">
3281 <title>Geometric Functions and Operators</title>
3284 The geometric types <type>point</type>, <type>box</type>,
3285 <type>lseg</type>, <type>line</type>, <type>path</type>,
3286 <type>polygon</type>, and <type>circle</type> have a large set of
3287 native support functions and operators.
3291 <TITLE>Geometric Operators</TITLE>
3295 <ENTRY>Operator</ENTRY>
3296 <ENTRY>Description</ENTRY>
3297 <ENTRY>Usage</ENTRY>
3303 <ENTRY>Translation</ENTRY>
3304 <ENTRY><literal>box '((0,0),(1,1))' + point '(2.0,0)'</literal></ENTRY>
3308 <ENTRY>Translation</ENTRY>
3309 <ENTRY><literal>box '((0,0),(1,1))' - point '(2.0,0)'</literal></ENTRY>
3313 <ENTRY>Scaling/rotation</ENTRY>
3314 <ENTRY><literal>box '((0,0),(1,1))' * point '(2.0,0)'</literal></ENTRY>
3318 <ENTRY>Scaling/rotation</ENTRY>
3319 <ENTRY><literal>box '((0,0),(2,2))' / point '(2.0,0)'</literal></ENTRY>
3323 <ENTRY>Intersection</ENTRY>
3324 <ENTRY><literal>'((1,-1),(-1,1))' # '((1,1),(-1,-1))'</literal></ENTRY>
3328 <ENTRY>Number of points in polygon</ENTRY>
3329 <ENTRY><literal># '((1,0),(0,1),(-1,0))'</literal></ENTRY>
3333 <ENTRY>Point of closest proximity</ENTRY>
3334 <ENTRY><literal>point '(0,0)' ## lseg '((2,0),(0,2))'</literal></ENTRY>
3337 <ENTRY> && </ENTRY>
3338 <ENTRY>Overlaps?</ENTRY>
3339 <ENTRY><literal>box '((0,0),(1,1))' && box '((0,0),(2,2))'</literal></ENTRY>
3342 <ENTRY> &< </ENTRY>
3343 <ENTRY>Overlaps to left?</ENTRY>
3344 <ENTRY><literal>box '((0,0),(1,1))' &< box '((0,0),(2,2))'</literal></ENTRY>
3347 <ENTRY> &> </ENTRY>
3348 <ENTRY>Overlaps to right?</ENTRY>
3349 <ENTRY><literal>box '((0,0),(3,3))' &> box '((0,0),(2,2))'</literal></ENTRY>
3352 <ENTRY> <-> </ENTRY>
3353 <ENTRY>Distance between</ENTRY>
3354 <ENTRY><literal>circle '((0,0),1)' <-> circle '((5,0),1)'</literal></ENTRY>
3357 <ENTRY> << </ENTRY>
3358 <ENTRY>Left of?</ENTRY>
3359 <ENTRY><literal>circle '((0,0),1)' << circle '((5,0),1)'</literal></ENTRY>
3362 <ENTRY> <^ </ENTRY>
3363 <ENTRY>Is below?</ENTRY>
3364 <ENTRY><literal>circle '((0,0),1)' <^ circle '((0,5),1)'</literal></ENTRY>
3367 <ENTRY> >> </ENTRY>
3368 <ENTRY>Is right of?</ENTRY>
3369 <ENTRY><literal>circle '((5,0),1)' >> circle '((0,0),1)'</literal></ENTRY>
3372 <ENTRY> >^ </ENTRY>
3373 <ENTRY>Is above?</ENTRY>
3374 <ENTRY><literal>circle '((0,5),1)' >^ circle '((0,0),1)'</literal></ENTRY>
3378 <ENTRY>Intersects or overlaps</ENTRY>
3379 <ENTRY><literal>lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'</literal></ENTRY>
3383 <ENTRY>Is horizontal?</ENTRY>
3384 <ENTRY><literal>point '(1,0)' ?- point '(0,0)'</literal></ENTRY>
3387 <ENTRY> ?-| </ENTRY>
3388 <ENTRY>Is perpendicular?</ENTRY>
3389 <ENTRY><literal>lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'</literal></ENTRY>
3392 <ENTRY> @-@ </ENTRY>
3393 <ENTRY>Length or circumference</ENTRY>
3394 <ENTRY><literal>@-@ path '((0,0),(1,0))'</literal></ENTRY>
3398 <ENTRY>Is vertical?</ENTRY>
3399 <ENTRY><literal>point '(0,1)' ?| point '(0,0)'</literal></ENTRY>
3402 <ENTRY> ?|| </ENTRY>
3403 <ENTRY>Is parallel?</ENTRY>
3404 <ENTRY><literal>lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'</literal></ENTRY>
3408 <ENTRY>Contained or on</ENTRY>
3409 <ENTRY><literal>point '(1,1)' @ circle '((0,0),2)'</literal></ENTRY>
3413 <ENTRY>Center of</ENTRY>
3414 <ENTRY><literal>@@ circle '((0,0),10)'</literal></ENTRY>
3418 <ENTRY>Same as</ENTRY>
3419 <ENTRY><literal>polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'</literal></ENTRY>
3426 <title>Geometric Functions</title>
3430 <entry>Function</entry>
3431 <entry>Returns</entry>
3432 <entry>Description</entry>
3433 <entry>Example</entry>
3438 <entry><function>area</function>(object)</entry>
3439 <entry><type>double precision</type></entry>
3440 <entry>area of item</entry>
3441 <entry><literal>area(box '((0,0),(1,1))')</literal></entry>
3444 <entry><function>box</function>(box, box)</entry>
3445 <entry><type>box</type></entry>
3446 <entry>intersection box</entry>
3447 <entry><literal>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</literal></entry>
3450 <entry><function>center</function>(object)</entry>
3451 <entry><type>point</type></entry>
3452 <entry>center of item</entry>
3453 <entry><literal>center(box '((0,0),(1,2))')</literal></entry>
3456 <entry><function>diameter</function>(circle)</entry>
3457 <entry><type>double precision</type></entry>
3458 <entry>diameter of circle</entry>
3459 <entry><literal>diameter(circle '((0,0),2.0)')</literal></entry>
3462 <entry><function>height</function>(box)</entry>
3463 <entry><type>double precision</type></entry>
3464 <entry>vertical size of box</entry>
3465 <entry><literal>height(box '((0,0),(1,1))')</literal></entry>
3468 <entry><function>isclosed</function>(path)</entry>
3469 <entry><type>boolean</type></entry>
3470 <entry>a closed path?</entry>
3471 <entry><literal>isclosed(path '((0,0),(1,1),(2,0))')</literal></entry>
3474 <entry><function>isopen</function>(path)</entry>
3475 <entry><type>boolean</type></entry>
3476 <entry>an open path?</entry>
3477 <entry><literal>isopen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3480 <entry><function>length</function>(object)</entry>
3481 <entry><type>double precision</type></entry>
3482 <entry>length of item</entry>
3483 <entry><literal>length(path '((-1,0),(1,0))')</literal></entry>
3486 <entry><function>pclose</function>(path)</entry>
3487 <entry><type>path</type></entry>
3488 <entry>convert path to closed</entry>
3489 <entry><literal>popen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3492 <!-- Not defined by this name. Implements the intersection operator '#' -->
3494 <entry><function>point</function>(lseg,lseg)</entry>
3495 <entry><type>point</type></entry>
3496 <entry>intersection</entry>
3497 <entry><literal>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</literal></entry>
3501 <entry><function>npoint</function>(path)</entry>
3502 <entry><type>integer</type></entry>
3503 <entry>number of points</entry>
3504 <entry><literal>npoints(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3507 <entry><function>popen</function>(path)</entry>
3508 <entry><type>path</type></entry>
3509 <entry>convert path to open path</entry>
3510 <entry><literal>popen(path '((0,0),(1,1),(2,0))')</literal></entry>
3513 <entry><function>radius</function>(circle)</entry>
3514 <entry><type>double precision</type></entry>
3515 <entry>radius of circle</entry>
3516 <entry><literal>radius(circle '((0,0),2.0)')</literal></entry>
3519 <entry><function>width</function>(box)</entry>
3520 <entry><type>double precision</type></entry>
3521 <entry>horizontal size</entry>
3522 <entry><literal>width(box '((0,0),(1,1))')</literal></entry>
3530 <title>Geometric Type Conversion Functions</title>
3534 <entry>Function</entry>
3535 <entry>Returns</entry>
3536 <entry>Description</entry>
3537 <entry>Example</entry>
3542 <entry><function>box</function>(<type>circle</type>)</entry>
3543 <entry><type>box</type></entry>
3544 <entry>circle to box</entry>
3545 <entry><literal>box(circle '((0,0),2.0)')</literal></entry>
3548 <entry><function>box</function>(<type>point</type>, <type>point</type>)</entry>
3549 <entry><type>box</type></entry>
3550 <entry>points to box</entry>
3551 <entry><literal>box(point '(0,0)', point '(1,1)')</literal></entry>
3554 <entry><function>box</function>(<type>polygon</type>)</entry>
3555 <entry><type>box</type></entry>
3556 <entry>polygon to box</entry>
3557 <entry><literal>box(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3560 <entry><function>circle</function>(<type>box</type>)</entry>
3561 <entry><type>circle</type></entry>
3562 <entry>to circle</entry>
3563 <entry><literal>circle(box '((0,0),(1,1))')</literal></entry>
3566 <entry><function>circle</function>(<type>point</type>, <type>double precision</type>)</entry>
3567 <entry><type>circle</type></entry>
3568 <entry>point to circle</entry>
3569 <entry><literal>circle(point '(0,0)', 2.0)</literal></entry>
3572 <entry><function>lseg</function>(<type>box</type>)</entry>
3573 <entry><type>lseg</type></entry>
3574 <entry>box diagonal to lseg</entry>
3575 <entry><literal>lseg(box '((-1,0),(1,0))')</literal></entry>
3578 <entry><function>lseg</function>(<type>point</type>, <type>point</type>)</entry>
3579 <entry><type>lseg</type></entry>
3580 <entry>points to lseg</entry>
3581 <entry><literal>lseg(point '(-1,0)', point '(1,0)')</literal></entry>
3584 <entry><function>path</function>(<type>polygon</type>)</entry>
3585 <entry><type>point</type></entry>
3586 <entry>polygon to path</entry>
3587 <entry><literal>path(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3590 <entry><function>point</function>(<type>circle</type>)</entry>
3591 <entry><type>point</type></entry>
3592 <entry>center</entry>
3593 <entry><literal>point(circle '((0,0),2.0)')</literal></entry>
3596 <entry><function>point</function>(<type>lseg</type>, <type>lseg</type>)</entry>
3597 <entry><type>point</type></entry>
3598 <entry>intersection</entry>
3599 <entry><literal>point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')</literal></entry>
3602 <entry><function>point</function>(<type>polygon</type>)</entry>
3603 <entry><type>point</type></entry>
3604 <entry>center</entry>
3605 <entry><literal>point(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3608 <entry><function>polygon</function>(<type>box</type>)</entry>
3609 <entry><type>polygon</type></entry>
3610 <entry>12 point polygon</entry>
3611 <entry><literal>polygon(box '((0,0),(1,1))')</literal></entry>
3614 <entry><function>polygon</function>(<type>circle</type>)</entry>
3615 <entry><type>polygon</type></entry>
3616 <entry>12-point polygon</entry>
3617 <entry><literal>polygon(circle '((0,0),2.0)')</literal></entry>
3620 <entry><function>polygon</function>(<replaceable class="parameter">npts</replaceable>, <type>circle</type>)</entry>
3621 <entry><type>polygon</type></entry>
3622 <entry><replaceable class="parameter">npts</replaceable> polygon</entry>
3623 <entry><literal>polygon(12, circle '((0,0),2.0)')</literal></entry>
3626 <entry><function>polygon</function>(<type>path</type>)</entry>
3627 <entry><type>polygon</type></entry>
3628 <entry>path to polygon</entry>
3629 <entry><literal>polygon(path '((0,0),(1,1),(2,0))')</literal></entry>
3638 <sect1 id="functions-net">
3639 <title>Network Address Type Functions</title>
3642 <table tocentry="1" id="cidr-inet-operators-table">
3643 <title><type>cidr</> and <type>inet</> Operators</title>
3647 <ENTRY>Operator</ENTRY>
3648 <ENTRY>Description</ENTRY>
3649 <ENTRY>Usage</ENTRY>
3654 <ENTRY> < </ENTRY>
3655 <ENTRY>Less than</ENTRY>
3656 <ENTRY><literal>inet '192.168.1.5' < inet '192.168.1.6'</literal></ENTRY>
3659 <ENTRY> <= </ENTRY>
3660 <ENTRY>Less than or equal</ENTRY>
3661 <ENTRY><literal>inet '192.168.1.5' <= inet '192.168.1.5'</literal></ENTRY>
3665 <ENTRY>Equals</ENTRY>
3666 <ENTRY><literal>inet '192.168.1.5' = inet '192.168.1.5'</literal></ENTRY>
3669 <ENTRY> >= </ENTRY>
3670 <ENTRY>Greater or equal</ENTRY>
3671 <ENTRY><literal>inet '192.168.1.5' >= inet '192.168.1.5'</literal></ENTRY>
3674 <ENTRY> > </ENTRY>
3675 <ENTRY>Greater</ENTRY>
3676 <ENTRY><literal>inet '192.168.1.5' > inet '192.168.1.4'</literal></ENTRY>
3679 <ENTRY> <> </ENTRY>
3680 <ENTRY>Not equal</ENTRY>
3681 <ENTRY><literal>inet '192.168.1.5' <> inet '192.168.1.4'</literal></ENTRY>
3684 <ENTRY> << </ENTRY>
3685 <ENTRY>is contained within</ENTRY>
3686 <ENTRY><literal>inet '192.168.1.5' << inet '192.168.1/24'</literal></ENTRY>
3689 <ENTRY> <<= </ENTRY>
3690 <ENTRY>is contained within or equals</ENTRY>
3691 <ENTRY><literal>inet '192.168.1/24' <<= inet '192.168.1/24'</literal></ENTRY>
3694 <ENTRY> >> </ENTRY>
3695 <ENTRY>contains</ENTRY>
3696 <ENTRY><literal>inet'192.168.1/24' >> inet '192.168.1.5'</literal></ENTRY>
3699 <ENTRY> >>= </ENTRY>
3700 <ENTRY>contains or equals</ENTRY>
3701 <ENTRY><literal>inet '192.168.1/24' >>= inet '192.168.1/24'</literal></ENTRY>
3708 All of the operators for <type>inet</type> can be applied to
3709 <type>cidr</type> values as well. The operators
3710 <literal><<</>, <literal><<=</>,
3711 <literal>>></>, <literal>>>=</>
3712 test for subnet inclusion: they consider only the network parts
3713 of the two addresses, ignoring any host part, and determine whether
3714 one network part is identical to or a subnet of the other.
3718 <table tocentry="1" id="cidr-inet-functions">
3719 <title><type>cidr</> and <type>inet</> Functions</title>
3723 <entry>Function</entry>
3724 <entry>Returns</entry>
3725 <entry>Description</entry>
3726 <entry>Example</entry>
3727 <entry>Result</entry>
3732 <entry><function>broadcast</function>(<type>inet</type>)</entry>
3733 <entry><type>inet</type></entry>
3734 <entry>broadcast address for network</entry>
3735 <entry><literal>broadcast('192.168.1.5/24')</literal></entry>
3736 <entry><literal>192.168.1.255/24</literal></entry>
3739 <entry><function>host</function>(<type>inet</type>)</entry>
3740 <entry><type>text</type></entry>
3741 <entry>extract IP address as text</entry>
3742 <entry><literal>host('192.168.1.5/24')</literal></entry>
3743 <entry><literal>192.168.1.5</literal></entry>
3746 <entry><function>masklen</function>(<type>inet</type>)</entry>
3747 <entry><type>integer</type></entry>
3748 <entry>extract netmask length</entry>
3749 <entry><literal>masklen('192.168.1.5/24')</literal></entry>
3750 <entry><literal>24</literal></entry>
3753 <entry><function>set_masklen</function>(<type>inet</type>,<type>integer</type>)</entry>
3754 <entry><type>inet</type></entry>
3755 <entry>set netmask length for inet value</entry>
3756 <entry><literal>set_masklen('192.168.1.5/24',16)</literal></entry>
3757 <entry><literal>192.168.1.5/16</literal></entry>
3760 <entry><function>netmask</function>(<type>inet</type>)</entry>
3761 <entry><type>inet</type></entry>
3762 <entry>construct netmask for network</entry>
3763 <entry><literal>netmask('192.168.1.5/24')</literal></entry>
3764 <entry><literal>255.255.255.0</literal></entry>
3767 <entry><function>network</function>(<type>inet</type>)</entry>
3768 <entry><type>cidr</type></entry>
3769 <entry>extract network part of address</entry>
3770 <entry><literal>network('192.168.1.5/24')</literal></entry>
3771 <entry><literal>192.168.1.0/24</literal></entry>
3774 <entry><function>text</function>(<type>inet</type>)</entry>
3775 <entry><type>text</type></entry>
3776 <entry>extract IP address and masklen as text</entry>
3777 <entry><literal>text(inet '192.168.1.5')</literal></entry>
3778 <entry><literal>192.168.1.5/32</literal></entry>
3781 <entry><function>abbrev</function>(<type>inet</type>)</entry>
3782 <entry><type>text</type></entry>
3783 <entry>extract abbreviated display as text</entry>
3784 <entry><literal>abbrev(cidr '10.1.0.0/16')</literal></entry>
3785 <entry><literal>10.1/16</literal></entry>
3792 All of the functions for <type>inet</type> can be applied to
3793 <type>cidr</type> values as well. The <function>host</>(),
3794 <function>text</>(), and <function>abbrev</>() functions are primarily
3795 intended to offer alternative display formats. You can cast a text
3796 field to inet using normal casting syntax: <literal>inet(expression)</literal> or
3797 <literal>colname::inet</literal>.
3801 <table tocentry="1" id="macaddr-functions">
3802 <title><type>macaddr</> Functions</title>
3806 <entry>Function</entry>
3807 <entry>Returns</entry>
3808 <entry>Description</entry>
3809 <entry>Example</entry>
3810 <entry>Result</entry>
3815 <entry><function>trunc</function>(<type>macaddr</type>)</entry>
3816 <entry><type>macaddr</type></entry>
3817 <entry>set last 3 bytes to zero</entry>
3818 <entry><literal>trunc(macaddr '12:34:56:78:90:ab')</literal></entry>
3819 <entry><literal>12:34:56:00:00:00</literal></entry>
3827 The function <function>trunc</>(<type>macaddr</>) returns a MAC
3828 address with the last 3 bytes set to 0. This can be used to
3829 associate the remaining prefix with a manufacturer. The directory
3830 <filename>contrib/mac</> in the source distribution contains some
3831 utilities to create and maintain such an association table.
3835 The <type>macaddr</> type also supports the standard relational
3836 operators (<literal>></>, <literal><=</>, etc.) for
3837 lexicographical ordering.
3843 <sect1 id="functions-sequence">
3844 <title>Sequence-Manipulation Functions</title>
3847 <primary>sequences</primary>
3850 <primary>nextval</primary>
3853 <primary>currval</primary>
3856 <primary>setval</primary>
3860 <title>Sequence Functions</>
3863 <row><entry>Function</> <entry>Returns</> <entry>Description</></row>
3868 <entry><function>nextval</function>(<type>text</type>)</entry>
3869 <entry><type>bigint</type></entry>
3870 <entry>Advance sequence and return new value</>
3873 <entry><function>currval</function>(<type>text</type>)</entry>
3874 <entry><type>bigint</type></entry>
3875 <entry>Return value most recently obtained with <function>nextval</></entry>
3878 <entry><function>setval</function>(<type>text</type>,<type>bigint</type>)</entry>
3879 <entry><type>bigint</type></entry>
3880 <entry>Set sequence's current value</>
3883 <entry><function>setval</function>(<type>text</type>,<type>bigint</type>,<type>boolean</>)</entry>
3884 <entry><type>bigint</type></entry>
3885 <entry>Set sequence's current value and <literal>is_called</> flag</entry>
3892 This section describes <productname>PostgreSQL</productname>'s functions
3893 for operating on <firstterm>sequence objects</>.
3894 Sequence objects (also called sequence generators or
3895 just sequences) are special single-row tables created with
3896 <command>CREATE SEQUENCE</>. A sequence object is usually used to
3897 generate unique identifiers for rows of a table. The sequence functions
3898 provide simple, multi-user-safe methods for obtaining successive
3899 sequence values from sequence objects.
3903 For largely historical reasons, the sequence to be operated on by
3904 a sequence-function call is specified by a text-string argument.
3905 To achieve some compatibility with the handling of ordinary SQL
3906 names, the sequence functions convert their argument to lower case
3907 unless the string is double-quoted. Thus
3909 nextval('foo') <lineannotation>operates on sequence </><literal>foo</>
3910 nextval('FOO') <lineannotation>operates on sequence </><literal>foo</>
3911 nextval('"Foo"') <lineannotation>operates on sequence </><literal>Foo</>
3913 Of course, the text argument can be the result of an expression,
3914 not only a simple literal, which is occasionally useful.
3918 The available sequence functions are:
3922 <term><function>nextval</></term>
3925 Advance the sequence object to its next value and return that
3926 value. This is done atomically: even if multiple server processes
3927 execute <function>nextval</> concurrently, each will safely receive
3928 a distinct sequence value.
3934 <term><function>currval</></term>
3937 Return the value most recently obtained by <function>nextval</>
3938 for this sequence in the current server process. (An error is
3939 reported if <function>nextval</> has never been called for this
3940 sequence in this process.) Notice that because this is returning
3941 a process-local value, it gives a predictable answer even if other
3942 server processes are executing <function>nextval</> meanwhile.
3948 <term><function>setval</></term>
3951 Reset the sequence object's counter value. The two-parameter
3952 form sets the sequence's <literal>last_value</> field to the specified
3953 value and sets its <literal>is_called</> field to <literal>true</>,
3954 meaning that the next <function>nextval</> will advance the sequence
3955 before returning a value. In the three-parameter form,
3956 <literal>is_called</> may be set either <literal>true</> or
3957 <literal>false</>. If it's set to <literal>false</>,
3958 the next <function>nextval</> will return exactly the specified
3959 value, and sequence advancement commences with the following
3960 <function>nextval</>. For example,
3965 SELECT setval('foo', 42); <lineannotation>Next nextval() will return 43</>
3966 SELECT setval('foo', 42, true); <lineannotation>Same as above</>
3967 SELECT setval('foo', 42, false); <lineannotation>Next nextval() will return 42</>
3972 The result returned by <function>setval</> is just the value of its
3982 To avoid blocking of concurrent transactions that obtain numbers from the
3983 same sequence, a <function>nextval</> operation is never rolled back;
3984 that is, once a value has been fetched it is considered used, even if the
3985 transaction that did the <function>nextval</> later aborts. This means
3986 that aborted transactions may leave unused <quote>holes</quote> in the
3987 sequence of assigned values. <function>setval</> operations are never
3988 rolled back, either.
3993 If a sequence object has been created with default parameters,
3994 <function>nextval()</> calls on it will return successive values
3995 beginning with one. Other behaviors can be obtained by using
3996 special parameters in the <command>CREATE SEQUENCE</> command;
3997 see its command reference page for more information.
4003 <sect1 id="functions-conditional">
4004 <title>Conditional Expressions</title>
4007 <primary>case</primary>
4011 <primary>conditionals</primary>
4015 This section describes the <acronym>SQL</acronym>-compliant conditional expressions
4016 available in <productname>PostgreSQL</productname>.
4021 If your needs go beyond the capabilities of these conditional
4022 expressions you might want to consider writing a stored procedure
4023 in a more expressive programming language.
4027 <bridgehead renderas="sect2">CASE</bridgehead>
4030 CASE WHEN <replaceable>condition</replaceable> THEN <replaceable>result</replaceable>
4031 <optional>WHEN ...</optional>
4032 <optional>ELSE <replaceable>result</replaceable></optional>
4037 The <acronym>SQL</acronym> <token>CASE</token> expression is a
4038 generic conditional expression, similar to if/else statements in
4039 other languages. <token>CASE</token> clauses can be used wherever
4040 an expression is valid. <replaceable>condition</replaceable> is an
4041 expression that returns a <type>boolean</type> result. If the result is true
4042 then the value of the <token>CASE</token> expression is
4043 <replaceable>result</replaceable>. If the result is false any
4044 subsequent <token>WHEN</token> clauses are searched in the same
4045 manner. If no <token>WHEN</token>
4046 <replaceable>condition</replaceable> is true then the value of the
4047 case expression is the <replaceable>result</replaceable> in the
4048 <token>ELSE</token> clause. If the <token>ELSE</token> clause is
4049 omitted and no condition matches, the result is NULL.
4056 <prompt>=></prompt> <userinput>SELECT * FROM test;</userinput>
4065 <prompt>=></prompt> <userinput>SELECT a,
4066 CASE WHEN a=1 THEN 'one'
4070 FROM test;</userinput>
4083 The data types of all the <replaceable>result</replaceable>
4084 expressions must be coercible to a single output type.
4085 See <xref linkend="typeconv-union-case"> for more detail.
4089 CASE <replaceable>expression</replaceable>
4090 WHEN <replaceable>value</replaceable> THEN <replaceable>result</replaceable>
4091 <optional>WHEN ...</optional>
4092 <optional>ELSE <replaceable>result</replaceable></optional>
4097 This <quote>simple</quote> <token>CASE</token> expression is a
4098 specialized variant of the general form above. The
4099 <replaceable>expression</replaceable> is computed and compared to
4100 all the <replaceable>value</replaceable>s in the
4101 <token>WHEN</token> clauses until one is found that is equal. If
4102 no match is found, the <replaceable>result</replaceable> in the
4103 <token>ELSE</token> clause (or NULL) is returned. This is similar
4104 to the <function>switch</function> statement in C.
4109 The example above can be written using the simple
4110 <token>CASE</token> syntax:
4112 <prompt>=></prompt> <userinput>SELECT a,
4113 CASE a WHEN 1 THEN 'one'
4117 FROM test;</userinput>
4129 <bridgehead renderas="sect2">COALESCE</bridgehead>
4132 <function>COALESCE</function>(<replaceable>value</replaceable><optional>, ...</optional>)
4136 The <function>COALESCE</function> function returns the first of its
4137 arguments that is not NULL. This is often useful to substitute a
4138 default value for NULL values when data is retrieved for display,
4141 SELECT COALESCE(description, short_description, '(none)') ...
4145 <bridgehead renderas="sect2">NULLIF</bridgehead>
4148 <primary>nullif</primary>
4152 <function>NULLIF</function>(<replaceable>value1</replaceable>, <replaceable>value2</replaceable>)
4156 The <function>NULLIF</function> function returns NULL if and only
4157 if <replaceable>value1</replaceable> and
4158 <replaceable>value2</replaceable> are equal. Otherwise it returns
4159 <replaceable>value1</replaceable>. This can be used to perform the
4160 inverse operation of the <function>COALESCE</function> example
4163 SELECT NULLIF(value, '(none)') ...
4169 <function>COALESCE</function> and <function>NULLIF</function> are
4170 just shorthand for <token>CASE</token> expressions. They are actually
4171 converted into <token>CASE</token> expressions at a very early stage
4172 of processing, and subsequent processing thinks it is dealing with
4173 <token>CASE</token>. Thus an incorrect <function>COALESCE</function> or
4174 <function>NULLIF</function> usage may draw an error message that
4175 refers to <token>CASE</token>.
4182 <sect1 id="functions-misc">
4183 <title>Miscellaneous Functions</>
4186 <title>Session Information Functions</>
4189 <row><entry>Name</> <entry>Return Type</> <entry>Description</></row>
4194 <entry><function>current_user</></entry>
4195 <entry><type>name</></entry>
4196 <entry>user name of current execution context</>
4199 <entry><function>session_user</></entry>
4200 <entry><type>name</></entry>
4201 <entry>session user name</>
4204 <entry><function>user</></entry>
4205 <entry><type>name</></entry>
4206 <entry>equivalent to <function>current_user</></>
4212 <indexterm zone="functions-misc">
4213 <primary>user</primary>
4214 <secondary>current</secondary>
4218 The <function>session_user</> is the user that initiated a database
4219 connection; it is fixed for the duration of that connection. The
4220 <function>current_user</> is the user identifier that is applicable
4221 for permission checking. Currently it is always equal to the session
4222 user, but in the future there might be <quote>setuid</> functions and
4223 other facilities to allow the current user to change temporarily.
4224 In Unix parlance, the session user is the <quote>real user</>
4225 and the current user is the <quote>effective user</>.
4229 Note that these functions have special syntactic status in <acronym>SQL</>:
4230 they must be called without trailing parentheses.
4234 <title>Deprecated</>
4236 The function <function>getpgusername()</> is an obsolete equivalent
4237 of <function>current_user</>.
4242 <title>System Information Functions</>
4245 <row><entry>Name</> <entry>Return Type</> <entry>Description</></row>
4250 <entry><function>version</></entry>
4251 <entry><type>text</></entry>
4252 <entry>PostgreSQL version information</>
4258 <indexterm zone="functions-misc">
4259 <primary>version</primary>
4263 <function>version()</> returns a string describing the PostgreSQL
4268 <title>Access Privilege Inquiry Functions</>
4271 <row><entry>Name</> <entry>Return Type</> <entry>Description</></row>
4276 <entry><function>has_table_privilege</function>(<parameter>user</parameter>,
4277 <parameter>table</parameter>,
4278 <parameter>access</parameter>)
4280 <entry><type>boolean</type></>
4281 <entry>does user have access to table</>
4284 <entry><function>has_table_privilege</function>(<parameter>table</parameter>,
4285 <parameter>access</parameter>)
4287 <entry><type>boolean</type></>
4288 <entry>does current user have access to table</>
4294 <indexterm zone="functions-misc">
4295 <primary>has_table_privilege</primary>
4299 <function>has_table_privilege</> determines whether a user
4300 can access a table in a particular way. The user can be
4301 specified by name or by ID
4302 (<classname>pg_user</>.<structfield>usesysid</>), or if the argument is
4304 <function>current_user</> is assumed. The table can be specified
4305 by name or by OID. (Thus, there are actually six variants of
4306 <function>has_table_privilege</>, which can be distinguished by
4307 the number and types of their arguments.) The desired access type
4308 is specified by a text string, which must evaluate to one of the
4309 values <literal>SELECT</>, <literal>INSERT</>, <literal>UPDATE</>,
4310 <literal>DELETE</>, <literal>RULE</>, <literal>REFERENCES</>, or
4311 <literal>TRIGGER</>. (Case of the string is not significant, however.)
4315 <title>Catalog Information Functions</>
4318 <row><entry>Name</> <entry>Return Type</> <entry>Description</></row>
4323 <entry><function>pg_get_viewdef</>(<parameter>viewname</parameter>)</entry>
4324 <entry><type>text</></entry>
4325 <entry>Get CREATE VIEW command for view</>
4328 <entry><function>pg_get_ruledef</>(<parameter>rulename</parameter>)</entry>
4329 <entry><type>text</></entry>
4330 <entry>Get CREATE RULE command for rule</>
4333 <entry><function>pg_get_indexdef</>(<parameter>indexOID</parameter>)</entry>
4334 <entry><type>text</></entry>
4335 <entry>Get CREATE INDEX command for index</>
4338 <entry><function>pg_get_userbyid</>(<parameter>userid</parameter>)</entry>
4339 <entry><type>name</></entry>
4340 <entry>Get user name given sysid</>
4346 <indexterm zone="functions-misc">
4347 <primary>pg_get_viewdef</primary>
4350 <indexterm zone="functions-misc">
4351 <primary>pg_get_ruledef</primary>
4354 <indexterm zone="functions-misc">
4355 <primary>pg_get_indexdef</primary>
4358 <indexterm zone="functions-misc">
4359 <primary>pg_get_userbyid</primary>
4363 These functions extract information from the system catalogs.
4364 <function>pg_get_viewdef()</>, <function>pg_get_ruledef()</>, and
4365 <function>pg_get_indexdef()</> respectively reconstruct the creating
4366 command for a view, rule, or index. (Note that this is a decompiled
4367 reconstruction, not the verbatim text of the command.)
4368 <function>pg_get_userbyid()</> extracts a user's name given a
4369 <structfield>usesysid</> value.
4375 <sect1 id="functions-aggregate">
4376 <title>Aggregate Functions</title>
4379 <title>Author</title>
4381 Written by Isaac Wilcox <email>isaac@azartmedia.com</email> on 2000-06-16
4386 <firstterm>Aggregate functions</firstterm> compute a single result
4387 value from a set of input values. The special syntax
4388 considerations for aggregate functions are explained in <xref
4389 linkend="syntax-aggregates">. Consult the <citetitle>PostgreSQL
4390 Tutorial</citetitle> for additional introductory information.
4393 <table tocentry="1">
4394 <title>Aggregate Functions</title>
4399 <entry>Function</entry>
4400 <entry>Description</entry>
4401 <entry>Notes</entry>
4407 <entry>AVG(<replaceable class="parameter">expression</replaceable>)</entry>
4408 <entry>the average (arithmetic mean) of all input values</entry>
4411 <primary>average</primary>
4412 <secondary>function</secondary>
4414 Finding the average value is available on the following data
4415 types: <type>smallint</type>, <type>integer</type>,
4416 <type>bigint</type>, <type>real</type>, <type>double
4417 precision</type>, <type>numeric</type>, <type>interval</type>.
4418 The result is of type <type>numeric</type> for any integer type
4419 input, <type>double precision</type> for floating point input,
4420 otherwise the same as the input data type.
4425 <entry><function>count</function>(*)</entry>
4426 <entry>number of input values</entry>
4427 <entry>The return value is of type <type>bigint</type>.</entry>
4431 <entry><function>count</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4433 Counts the input values for which the value of <replaceable
4434 class="parameter">expression</replaceable> is not NULL.
4436 <entry>The return value is of type <type>bigint</type>.</entry>
4440 <entry><function>max</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4441 <entry>the maximum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4443 Available for all numeric, string, and date/time types. The
4444 result has the same type as the input expression.
4449 <entry><function>min</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4450 <entry>the minimum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4452 Available for all numeric, string, and date/time types. The
4453 result has the same type as the input expression.
4458 <entry><function>stddev</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4459 <entry>the sample standard deviation of the input values</entry>
4462 <primary>standard deviation</primary>
4464 Finding the standard deviation is available on the following
4465 data types: <type>smallint</type>, <type>integer</type>,
4466 <type>bigint</type>, <type>real</type>, <type>double
4467 precision</type>, <type>numeric</type>. The result is of type
4468 <type>double precision</type> for floating point input,
4469 otherwise <type>numeric</type>.
4474 <entry><function>sum</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4475 <entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4477 Summation is available on the following data types:
4478 <type>smallint</type>, <type>integer</type>,
4479 <type>bigint</type>, <type>real</type>, <type>double
4480 precision</type>, <type>numeric</type>, <type>interval</type>.
4481 The result is of type <type>bigint</type> for <type>smallint</type>
4482 or <type>integer</type> input, <type>numeric</type> for
4484 input, <type>double precision</type> for floating point input,
4485 otherwise the same as the input data type.
4490 <entry><function>variance</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4491 <entry>the sample variance of the input values</entry>
4494 <primary>variance</primary>
4496 The variance is the square of the standard deviation. The
4497 supported data types and result types are the same as for
4507 It should be noted that except for <function>COUNT</function>,
4508 these functions return NULL when no rows are selected. In
4509 particular, <function>SUM</function> of no rows returns NULL, not
4510 zero as one might expect. <function>COALESCE</function> may be
4511 used to substitute zero for NULL when necessary.
4518 <!-- Keep this comment at the end of the file
4523 sgml-minimize-attributes:nil
4524 sgml-always-quote-attributes:t
4527 sgml-parent-document:nil
4528 sgml-default-dtd-file:"./reference.ced"
4529 sgml-exposed-tags:nil
4530 sgml-local-catalogs:("/usr/lib/sgml/catalog")
4531 sgml-local-ecat-files:nil