2 $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.102 2002/06/15 02:59:55 thomas 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
32 <acronym>SQL</acronym>
33 standard. Some of this extended functionality is present in other
34 <acronym>RDBMS</acronym> products, and in many cases this
35 functionality is compatible and consistent between various products.
39 <sect1 id="functions-logical">
40 <title>Logical Operators</title>
42 <indexterm zone="functions-logical">
43 <primary>operators</primary>
44 <secondary>logical</secondary>
48 <primary>Boolean</primary>
49 <secondary>operators</secondary>
50 <see>operators, logical</see>
54 The usual logical operators are available:
57 <primary>and</primary>
58 <secondary>operator</secondary>
63 <secondary>operator</secondary>
67 <primary>not</primary>
68 <secondary>operator</secondary>
77 <acronym>SQL</acronym> uses a three-valued Boolean logic where NULL represents
78 <quote>unknown</quote>. Observe the following truth tables:
84 <entry><replaceable>a</replaceable></entry>
85 <entry><replaceable>b</replaceable></entry>
86 <entry><replaceable>a</replaceable> AND <replaceable>b</replaceable></entry>
87 <entry><replaceable>a</replaceable> OR <replaceable>b</replaceable></entry>
141 <entry><replaceable>a</replaceable></entry>
142 <entry>NOT <replaceable>a</replaceable></entry>
167 <sect1 id="functions-comparison">
168 <title>Comparison Operators</title>
170 <indexterm zone="functions-comparison">
171 <primary>comparison</primary>
172 <secondary>operators</secondary>
176 <title>Comparison Operators</title>
180 <entry>Operator</entry>
181 <entry>Description</entry>
187 <entry> <literal><</literal> </entry>
188 <entry>less than</entry>
192 <entry> <literal>></literal> </entry>
193 <entry>greater than</entry>
197 <entry> <literal><=</literal> </entry>
198 <entry>less than or equal to</entry>
202 <entry> <literal>>=</literal> </entry>
203 <entry>greater than or equal to</entry>
207 <entry> <literal>=</literal> </entry>
212 <entry> <literal><></literal> or <literal>!=</literal> </entry>
213 <entry>not equal</entry>
221 The <literal>!=</literal> operator is converted to
222 <literal><></literal> in the parser stage. It is not
223 possible to implement <literal>!=</literal> and
224 <literal><></literal> operators that do different things.
229 Comparison operators are available for all data types where this
230 makes sense. All comparison operators are binary operators that
231 return values of type <type>boolean</type>; expressions like
232 <literal>1 < 2 < 3</literal> are not valid (because there is
233 no <literal><</literal> operator to compare a Boolean value with
234 <literal>3</literal>).
239 <primary>between</primary>
241 In addition to the comparison operators, the special
242 <token>BETWEEN</token> construct is available.
244 <replaceable>a</replaceable> BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
248 <replaceable>a</replaceable> >= <replaceable>x</replaceable> AND <replaceable>a</replaceable>able> <= <replaceable>y</replaceable>
252 <replaceable>a</replaceable> NOT BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
256 <replaceable>a</replaceable> < <replaceable>x</replaceable> OR <replaceable>a</replaceable>able> > <replaceable>y</replaceable>
258 There is no difference between the two respective forms apart from
259 the <acronym>CPU</acronym> cycles required to rewrite the first one
260 into the second one internally.
264 To check whether a value is or is not NULL, use the constructs
266 <replaceable>expression</replaceable> IS NULL
267 <replaceable>expression</replaceable> IS NOT NULL
269 or the equivalent, but less standard, constructs
271 <replaceable>expression</replaceable> ISNULL
272 <replaceable>expression</replaceable> NOTNULL
277 Do <emphasis>not</emphasis> write
278 <literal><replaceable>expression</replaceable> = NULL</literal>
279 because NULL is not <quote>equal to</quote> NULL. (NULL represents
280 an unknown value, and it is not known whether two unknown values are
285 Some applications may (incorrectly) require that
286 <literal><replaceable>expression</replaceable> = NULL</literal>
287 returns true if <replaceable>expression</replaceable> evaluates to
288 the NULL value. To support these applications, the run-time option
289 <varname>transform_null_equals</varname> can be turned on (e.g.,
290 <literal>SET transform_null_equals TO ON;</literal>).
291 <productname>PostgreSQL</productname> will then convert
292 <literal>x = NULL</literal> clauses to
293 <literal>x IS NULL</literal>. This was
294 the default behavior in releases 6.5 through 7.1.
298 Boolean values can also be tested using the constructs
300 <replaceable>expression</replaceable> IS TRUE
301 <replaceable>expression</replaceable> IS NOT TRUE
302 <replaceable>expression</replaceable> IS FALSE
303 <replaceable>expression</replaceable> IS NOT FALSE
304 <replaceable>expression</replaceable> IS UNKNOWN
305 <replaceable>expression</replaceable> IS NOT UNKNOWN
307 These are similar to <literal>IS NULL</literal> in that they will
308 always return TRUE or FALSE, never NULL, even when the operand is NULL.
309 A NULL input is treated as the logical value UNKNOWN.
313 <sect1 id="functions-math">
314 <title>Mathematical Functions and Operators</title>
317 Mathematical operators are provided for many
318 <productname>PostgreSQL</productname> types. For types without
319 common mathematical conventions for all possible permutations
320 (e.g. date/time types) we
321 describe the actual behavior in subsequent sections.
325 <title>Mathematical Operators</title>
331 <entry>Description</entry>
332 <entry>Example</entry>
333 <entry>Result</entry>
339 <entry> <literal>+</literal> </entry>
340 <entry>Addition</entry>
346 <entry> <literal>-</literal> </entry>
347 <entry>Subtraction</entry>
353 <entry> <literal>*</literal> </entry>
354 <entry>Multiplication</entry>
360 <entry> <literal>/</literal> </entry>
361 <entry>Division (integer division truncates results)</entry>
367 <entry> <literal>%</literal> </entry>
368 <entry>Modulo (remainder)</entry>
374 <entry> <literal>^</literal> </entry>
375 <entry>Exponentiation</entry>
376 <entry>2.0 ^ 3.0</entry>
381 <entry> <literal>|/</literal> </entry>
382 <entry>Square root</entry>
383 <entry>|/ 25.0</entry>
388 <entry> <literal>||/</literal> </entry>
389 <entry>Cube root</entry>
390 <entry>||/ 27.0</entry>
395 <entry> <literal>!</literal> </entry>
396 <entry>Factorial</entry>
402 <entry> <literal>!!</literal> </entry>
403 <entry>Factorial (prefix operator)</entry>
409 <entry> <literal>@</literal> </entry>
410 <entry>Absolute value</entry>
411 <entry>@ -5.0</entry>
416 <entry> <literal>&</literal> </entry>
417 <entry>Binary AND</entry>
418 <entry>91 & 15</entry>
423 <entry> <literal>|</literal> </entry>
424 <entry>Binary OR</entry>
425 <entry>32 | 3</entry>
430 <entry> <literal>#</literal> </entry>
431 <entry>Binary XOR</entry>
432 <entry>17 # 5</entry>
437 <entry> <literal>~</literal> </entry>
438 <entry>Binary NOT</entry>
444 <entry> << </entry>
445 <entry>Binary shift left</entry>
446 <entry>1 << 4</entry>
451 <entry> >> </entry>
452 <entry>Binary shift right</entry>
453 <entry>8 >> 2</entry>
462 The <quote>binary</quote> operators are also available for the bit
463 string types <type>BIT</type> and <type>BIT VARYING</type>.
466 <title>Bit String Binary Operators</title>
471 <entry>Example</entry>
472 <entry>Result</entry>
478 <entry>B'10001' & B'01101'</entry>
482 <entry>B'10001' | B'01101'</entry>
486 <entry>B'10001' # B'01101'</entry>
490 <entry>~ B'10001'</entry>
494 <entry>B'10001' << 3</entry>
498 <entry>B'10001' >> 2</entry>
505 Bit string arguments to <literal>&</literal>, <literal>|</literal>,
506 and <literal>#</literal> must be of equal length. When bit
507 shifting, the original length of the string is preserved, as shown
512 <title>Mathematical Functions</title>
516 <entry>Function</entry>
517 <entry>Return Type</entry>
518 <entry>Description</entry>
519 <entry>Example</entry>
520 <entry>Result</entry>
526 <entry><function>abs</function>(<replaceable>x</replaceable>)</entry>
527 <entry>(same as x)</entry>
528 <entry>absolute value</entry>
529 <entry><literal>abs(-17.4)</literal></entry>
534 <entry><function>cbrt</function>(<type>dp</type>)</entry>
535 <entry><type>dp</type></entry>
536 <entry>cube root</entry>
537 <entry><literal>cbrt(27.0)</literal></entry>
542 <entry><function>ceil</function>(<type>numeric</type>)</entry>
543 <entry><type>numeric</type></entry>
544 <entry>smallest integer not less than argument</entry>
545 <entry><literal>ceil(-42.8)</literal></entry>
550 <entry><function>degrees</function>(<type>dp</type>)</entry>
551 <entry><type>dp</type></entry>
552 <entry>radians to degrees</entry>
553 <entry><literal>degrees(0.5)</literal></entry>
554 <entry>28.6478897565412</entry>
558 <entry><function>exp</function>(<type>dp</type>)</entry>
559 <entry><type>dp</type></entry>
560 <entry>exponential</entry>
561 <entry><literal>exp(1.0)</literal></entry>
562 <entry>2.71828182845905</entry>
566 <entry><function>floor</function>(<type>numeric</type>)</entry>
567 <entry><type>numeric</type></entry>
568 <entry>largest integer not greater than argument</entry>
569 <entry><literal>floor(-42.8)</literal></entry>
574 <entry><function>ln</function>(<type>dp</type>)</entry>
575 <entry><type>dp</type></entry>
576 <entry>natural logarithm</entry>
577 <entry><literal>ln(2.0)</literal></entry>
578 <entry>0.693147180559945</entry>
582 <entry><function>log</function>(<type>dp</type>)</entry>
583 <entry><type>dp</type></entry>
584 <entry>base 10 logarithm</entry>
585 <entry><literal>log(100.0)</literal></entry>
590 <entry><function>log</function>(<parameter>b</parameter> <type>numeric</type>,
591 <parameter>x</parameter> <type>numeric</type>)</entry>
592 <entry><type>numeric</type></entry>
593 <entry>logarithm to base <parameter>b</parameter></entry>
594 <entry><literal>log(2.0, 64.0)</literal></entry>
595 <entry>6.0000000000</entry>
599 <entry><function>mod</function>(<parameter>y</parameter>,
600 <parameter>x</parameter>)</entry>
601 <entry>(same as argument types)</entry>
602 <entry>remainder of <parameter>y</parameter>/<parameter>x</parameter></entry>
603 <entry><literal>mod(9,4)</literal></entry>
608 <entry><function>pi</function>()</entry>
609 <entry><type>dp</type></entry>
610 <entry><quote>Pi</quote> constant</entry>
611 <entry><literal>pi()</literal></entry>
612 <entry>3.14159265358979</entry>
616 <entry><function>pow</function>(<parameter>e</parameter> <type>dp</type>,
617 <parameter>n</parameter> <type>dp</type>)</entry>
618 <entry><type>dp</type></entry>
619 <entry>raise a number to exponent <parameter>e</parameter></entry>
620 <entry><literal>pow(9.0, 3.0)</literal></entry>
625 <entry><function>radians</function>(<type>dp</type>)</entry>
626 <entry><type>dp</type></entry>
627 <entry>degrees to radians</entry>
628 <entry><literal>radians(45.0)</literal></entry>
629 <entry>0.785398163397448</entry>
633 <entry><function>random</function>()</entry>
634 <entry><type>dp</type></entry>
635 <entry>value between 0.0 to 1.0</entry>
636 <entry><literal>random()</literal></entry>
641 <entry><function>round</function>(<type>dp</type>)</entry>
642 <entry><type>dp</type></entry>
643 <entry>round to nearest integer</entry>
644 <entry><literal>round(42.4)</literal></entry>
649 <entry><function>round</function>(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type>)</entry>
650 <entry><type>numeric</type></entry>
651 <entry>round to <parameter>s</parameter> decimal places</entry>
652 <entry><literal>round(42.4382, 2)</literal></entry>
657 <entry><function>setseed</function>(<replaceable>new-seed</replaceable>)</entry>
658 <entry>set seed for subsequent random() calls</entry>
659 <entry><literal>setseed(0.54823)</literal></entry>
664 <entry><function>sign</function>(<type>numeric</type>)</entry>
665 <entry><type>numeric</type></entry>
666 <entry>sign of the argument (-1, 0, +1)</entry>
667 <entry><literal>sign(-8.4)</literal></entry>
672 <entry><function>sqrt</function>(<type>dp</type>)</entry>
673 <entry><type>dp</type></entry>
674 <entry>square root</entry>
675 <entry><literal>sqrt(2.0)</literal></entry>
676 <entry>1.4142135623731</entry>
680 <entry><function>trunc</function>(<type>dp</type>)</entry>
681 <entry><type>dp</type></entry>
682 <entry>truncate toward zero</entry>
683 <entry><literal>trunc(42.8)</literal></entry>
688 <entry><function>trunc</function>(<type>numeric</type>,
689 <parameter>r</parameter>rameter> <type>integer</type>)</entry>
690 <entry><type>numeric</type></entry>
691 <entry>truncate to <parameter>s</parameter> decimal places</entry>
692 <entry><literal>trunc(42.4382, 2)</literal></entry>
701 In the table above, <literal>dp</literal> indicates <type>double precision</type>.
702 The functions <function>exp</function>, <function>ln</function>,
703 <function>log</function>, <function>pow</function>,
704 <function>round</function> (1 argument), <function>sqrt</function>,
705 and <function>trunc</function> (1 argument) are also available for
706 the type <type>numeric</type> in place of
707 <type>double precision</type>.
708 Functions returning a <type>numeric</type> result take
709 <type>numeric</type> input arguments, unless otherwise specified.
710 Many of these functions are implemented on top
711 of the host system's C library; accuracy and behavior in boundary cases
712 could therefore vary depending on the host system.
716 <title>Trigonometric Functions</title>
721 <entry>Function</entry>
722 <entry>Description</entry>
728 <entry><function>acos</function>(<replaceable>x</replaceable>)</entry>
729 <entry>inverse cosine</entry>
733 <entry><function>asin</function>(<replaceable>x</replaceable>)</entry>
734 <entry>inverse sine</entry>
738 <entry><function>atan</function>(<replaceable>x</replaceable>)</entry>
739 <entry>inverse tangent</entry>
743 <entry><function>atan2</function>(<replaceable>x</replaceable>,
744 <replaceable>y</replaceable>)</entry>
745 <entry>inverse tangent of
746 <replaceable>a</replaceable>aceable>/<replaceable>x</replaceable></entry>
750 <entry><function>cos</function>(<replaceable>x</replaceable>)</entry>
751 <entry>cosine</entry>
755 <entry><function>cot</function>(<replaceable>x</replaceable>)</entry>
756 <entry>cotangent</entry>
760 <entry><function>sin</function>(<replaceable>x</replaceable>)</entry>
765 <entry><function>tan</function>(<replaceable>x</replaceable>)</entry>
766 <entry>tangent</entry>
773 All trigonometric functions have arguments and return values of
774 type <type>double precision</type>.
780 <sect1 id="functions-string">
781 <title>String Functions and Operators</title>
784 This section describes functions and operators for examining and
785 manipulating string values. Strings in this context include values
786 of all the types <type>CHARACTER</type>, <type>CHARACTER
787 VARYING</type>, and <type>TEXT</type>. Unless otherwise noted, all
788 of the functions listed below work on all of these types, but be
789 wary of potential effects of the automatic padding when using the
790 <type>CHARACTER</type> type. Generally, the functions described
791 here also work on data of non-string types by converting that data
792 to a string representation first. Some functions also exist
793 natively for bit-string types.
797 <acronym>SQL</acronym> defines some string functions with a special syntax where
798 certain keywords rather than commas are used to separate the
799 arguments. Details are in <xref linkend="functions-string-sql">.
800 These functions are also implemented using the regular syntax for
801 function invocation. (See <xref linkend="functions-string-other">.)
804 <table id="functions-string-sql">
805 <title><acronym>SQL</acronym> String Functions and Operators</title>
809 <entry>Function</entry>
810 <entry>Return Type</entry>
811 <entry>Description</entry>
812 <entry>Example</entry>
813 <entry>Result</entry>
819 <entry> <parameter>string</parameter> <literal>||</literal>
820 <parameter>string</parameter> </entry>
821 <entry> <type>text</type> </entry>
825 <primary>character strings</primary>
826 <secondary>concatenation</secondary>
829 <entry><literal>'Postgre' || 'SQL'</literal></entry>
830 <entry><literal>PostgreSQL</literal></entry>
834 <entry><function>bit_length</function>(<parameter>string</parameter>)</entry>
835 <entry><type>integer</type></entry>
836 <entry>number of bits in string</entry>
837 <entry><literal>bit_length('jose')</literal></entry>
838 <entry><literal>32</literal></entry>
842 <entry><function>char_length</function>(<parameter>string</parameter>) or <function>character_length</function>(<parameter>string</parameter>)</entry>
843 <entry><type>integer</type></entry>
845 number of characters in string
847 <primary>character strings</primary>
848 <secondary>length</secondary>
851 <primary>length</primary>
852 <secondary>character strings</secondary>
853 <see>character strings, length</see>
856 <entry><literal>char_length('jose')</literal></entry>
857 <entry><literal>4</literal></entry>
861 <entry><function>lower</function>(<parameter>string</parameter>)</entry>
862 <entry><type>text</type></entry>
863 <entry>Convert string to lower case.</entry>
864 <entry><literal>lower('TOM')</literal></entry>
865 <entry><literal>tom</literal></entry>
869 <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
870 <entry><type>integer</type></entry>
871 <entry>number of bytes in string</entry>
872 <entry><literal>octet_length('jose')</literal></entry>
873 <entry><literal>4</literal></entry>
877 <entry><function>overlay</function>(<parameter>string</parameter> placing <parameter>string</parameter> from <type>integer</type> <optional>for <type>integer</type></optional>)</entry>
878 <entry><type>text</type></entry>
882 <primary>overlay</primary>
885 <entry><literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal></entry>
886 <entry><literal>Thomas</literal></entry>
890 <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
891 <entry><type>integer</type></entry>
892 <entry>location of specified substring</entry>
893 <entry><literal>position('om' in 'Thomas')</literal></entry>
894 <entry><literal>3</literal></entry>
898 <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
899 <entry><type>text</type></entry>
903 <primary>substring</primary>
906 <entry><literal>substring('Thomas' from 2 for 3)</literal></entry>
907 <entry><literal>hom</literal></entry>
911 <entry><function>substring</function>(<parameter>string</parameter> <optional>from <replaceable>pattern</replaceable></optional> <optional>for <replaceable>escape</replaceable></optional>)</entry>
912 <entry><type>text</type></entry>
914 extract regular expression
916 <primary>substring</primary>
919 <entry><literal>substring('Thomas' from 'mas$' for <optional>escape '\\'</optional>)</literal></entry>
920 <entry><literal>mas</literal></entry>
925 <function>trim</function>(<optional>leading | trailing | both</optional>
926 <optional><parameter>characters</parameter></optional> from
927 <parameter>string</parameter>)
929 <entry><type>text</type></entry>
931 Removes the longest string containing only the
932 <parameter>characters</parameter> (a space by default) from the
933 beginning/end/both ends of the <parameter>string</parameter>.
935 <entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>
936 <entry><literal>Tom</literal></entry>
940 <entry><function>upper</function>(<parameter>string</parameter>)</entry>
941 <entry><type>text</type></entry>
942 <entry>Convert string to upper case.</entry>
943 <entry><literal>upper('tom')</literal></entry>
944 <entry><literal>TOM</literal></entry>
951 Additional string manipulation functions are available and are
952 listed below. Some of them are used internally to implement the
953 <acronym>SQL</acronym>-standard string functions listed above.
956 <table id="functions-string-other">
957 <title>Other String Functions</title>
961 <entry>Function</entry>
962 <entry>Return Type</entry>
963 <entry>Description</entry>
964 <entry>Example</entry>
965 <entry>Result</entry>
971 <entry><function>ascii</function>(<type>text</type>)</entry>
972 <entry>integer</entry>
973 <entry>Returns the <acronym>ASCII</acronym> code of the first character of the argument.</entry>
974 <entry><literal>ascii('x')</literal></entry>
975 <entry><literal>120</literal></entry>
979 <entry><function>btrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>trim</parameter> <type>text</type>)</entry>
980 <entry><type>text</type></entry>
982 Remove (trim) the longest string consisting only of characters
983 in <parameter>trim</parameter> from the start and end of
984 <parameter>string</parameter>.
986 <entry><literal>btrim('xyxtrimyyx','xy')</literal></entry>
987 <entry><literal>trim</literal></entry>
991 <entry><function>chr</function>(<type>integer</type>)</entry>
992 <entry><type>text</type></entry>
993 <entry>Returns the character with the given <acronym>ASCII</acronym> code.</entry>
994 <entry><literal>chr(65)</literal></entry>
995 <entry><literal>A</literal></entry>
1000 <function>convert</function>(<parameter>string</parameter>
1002 <optional><parameter>src_encoding</parameter> <type>name</type>,</optional>
1003 <parameter>dest_encoding</parameter> <type>name</type>)
1005 <entry><type>text</type></entry>
1007 Converts string using <parameter>dest_encoding</parameter>.
1008 The original encoding is specified by
1009 <parameter>src_encoding</parameter>. If
1010 <parameter>src_encoding</parameter> is omitted, database
1011 encoding is assumed.
1013 <entry><literal>convert('text_in_unicode', 'UNICODE', 'LATIN1')</literal></entry>
1014 <entry><literal>text_in_unicode</literal> represented in ISO 8859-1</entry>
1018 <entry><function>initcap</function>(<type>text</type>)</entry>
1019 <entry><type>text</type></entry>
1020 <entry>Converts first letter of each word (whitespace separated) to upper case.</entry>
1021 <entry><literal>initcap('hi thomas')</literal></entry>
1022 <entry><literal>Hi Thomas</literal></entry>
1026 <entry><function>length</function>(<parameter>string</parameter>)</entry>
1027 <entry><type>integer</type></entry>
1031 <primary>character strings</primary>
1032 <secondary>length</secondary>
1035 <primary>length</primary>
1036 <secondary>character strings</secondary>
1037 <see>character strings, length</see>
1040 <entry><literal>length('jose')</literal></entry>
1041 <entry><literal>4</literal></entry>
1046 <function>lpad</function>(<parameter>string</parameter> <type>text</type>,
1047 <parameter>length</parameter> <type>integer</type>
1048 <optional>, <parameter>fill</parameter> <type>text</type></optional>)
1052 Fills up the <parameter>string</parameter> to length
1053 <parameter>length</parameter> by prepending the characters
1054 <parameter>fill</parameter> (a space by default). If the
1055 <parameter>string</parameter> is already longer than
1056 <parameter>length</parameter> then it is truncated (on the
1059 <entry><literal>lpad('hi', 5, 'xy')</literal></entry>
1060 <entry><literal>xyxhi</literal></entry>
1064 <entry><function>ltrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>text</parameter> <type>text</type>)</entry>
1065 <entry><type>text</type></entry>
1067 Removes the longest string containing only characters from
1068 <parameter>trim</parameter> from the start of the string.
1070 <entry><literal>ltrim('zzzytrim','xyz')</literal></entry>
1071 <entry><literal>trim</literal></entry>
1075 <entry><function>pg_client_encoding</function>()</entry>
1076 <entry><type>name</type></entry>
1078 Returns current client encoding name.
1080 <entry><literal>pg_client_encoding()</literal></entry>
1081 <entry><literal>SQL_ASCII</literal></entry>
1085 <entry><function>repeat</function>(<type>text</type>, <type>integer</type>)</entry>
1086 <entry><type>text</type></entry>
1087 <entry>Repeat text a number of times.</entry>
1088 <entry><literal>repeat('Pg', 4)</literal></entry>
1089 <entry><literal>PgPgPgPg</literal></entry>
1094 <function>rpad</function>(<parameter>string</parameter> <type>text</type>,
1095 <parameter>length</parameter> <type>integer</type>
1096 <optional>, <parameter>fill</parameter> <type>text</type></optional>)
1098 <entry><type>text</type></entry>
1100 Fills up the <parameter>string</parameter> to length
1101 <parameter>length</parameter> by appending the characters
1102 <parameter>fill</parameter> (a space by default). If the
1103 <parameter>string</parameter> is already longer than
1104 <parameter>length</parameter> then it is truncated.
1106 <entry><literal>rpad('hi', 5, 'xy')</literal></entry>
1107 <entry><literal>hixyx</literal></entry>
1111 <entry><function>rtrim</function>(<parameter>string</parameter>
1112 text, <parameter>trim</parameter> text)</entry>
1113 <entry><type>text</type></entry>
1115 Removes the longest string containing only characters from
1116 <parameter>trim</parameter> from the end of the string.
1118 <entry><literal>rtrim('trimxxxx','x')</literal></entry>
1119 <entry><literal>trim</literal></entry>
1123 <entry><function>strpos</function>(<parameter>string</parameter>, <parameter>substring</parameter>)</entry>
1124 <entry><type>text</type></entry>
1126 Locates specified substring. (same as
1127 <literal>position(<parameter>substring</parameter> in
1128 <parameter>string</parameter>)</literal>, but note the reversed
1131 <entry><literal>strpos('high','ig')</literal></entry>
1132 <entry><literal>2</literal></entry>
1136 <entry><function>substr</function>(<parameter>string</parameter>, <parameter>from</parameter> <optional>, <parameter>count</parameter></optional>)</entry>
1137 <entry><type>text</type></entry>
1139 Extracts specified substring. (same as
1140 <literal>substring(<parameter>string</parameter> from <parameter>from</parameter> for <parameter>count</parameter>)</literal>)
1142 <entry><literal>substr('alphabet', 3, 2)</literal></entry>
1143 <entry><literal>ph</literal></entry>
1147 <entry><function>to_ascii</function>(<type>text</type>
1148 <optional>ptional>, <parameter>encoding</parameter></optional>)</entry>
1149 <entry><type>text</type></entry>
1150 <entry>Converts text from multibyte encoding to <acronym>ASCII</acronym>.</entry>
1151 <entry><literal>to_ascii('Karel')</literal></entry>
1152 <entry><literal>Karel</literal></entry>
1157 <function>translate</function>(<parameter>string</parameter>
1159 <parameter>from</parameter> <type>text</type>,
1160 <parameter>to</parameter> <type>text</type>)
1162 <entry><type>text</type></entry>
1164 Any character in <parameter>string</parameter> that matches a
1165 character in the <parameter>from</parameter> set is replaced by
1166 the corresponding character in the <parameter>to</parameter>
1169 <entry><literal>translate('12345', '14', 'ax')</literal></entry>
1170 <entry><literal>a23x5</literal></entry>
1175 <function>encode</function>(<parameter>data</parameter> <type>bytea</type>,
1176 <parameter>type</parameter> <type>text</type>)
1178 <entry><type>text</type></entry>
1180 Encodes binary data to <acronym>ASCII</acronym>-only representation. Supported
1181 types are: 'base64', 'hex', 'escape'.
1183 <entry><literal>encode('123\\000\\001', 'base64')</literal></entry>
1184 <entry><literal>MTIzAAE=</literal></entry>
1189 <function>decode</function>(<parameter>string</parameter> <type>text</type>,
1190 <parameter>type</parameter> <type>text</type>)
1192 <entry><type>bytea</type></entry>
1194 Decodes binary data from <parameter>string</parameter> previously
1195 encoded with encode(). Parameter type is same as in encode().
1197 <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
1198 <entry><literal>123\000\001</literal></entry>
1206 The <function>to_ascii</function> function supports conversion from
1207 LATIN1, LATIN2, WIN1250 (CP1250) only.
1212 <sect1 id="functions-binarystring">
1213 <title>Binary String Functions and Operators</title>
1216 This section describes functions and operators for examining and
1217 manipulating binary string values. Strings in this context include
1218 values of the type <type>BYTEA</type>.
1222 <acronym>SQL</acronym> defines some string functions with a
1223 special syntax where
1224 certain keywords rather than commas are used to separate the
1225 arguments. Details are in
1226 <xref linkend="functions-binarystring-sql">.
1227 Some functions are also implemented using the regular syntax for
1228 function invocation.
1229 (See <xref linkend="functions-binarystring-other">.)
1232 <table id="functions-binarystring-sql">
1233 <title><acronym>SQL</acronym> Binary String Functions and Operators</title>
1237 <entry>Function</entry>
1238 <entry>Return Type</entry>
1239 <entry>Description</entry>
1240 <entry>Example</entry>
1241 <entry>Result</entry>
1247 <entry> <parameter>string</parameter> <literal>||</literal>
1248 <parameter>string</parameter> </entry>
1249 <entry> <type>bytea</type> </entry>
1251 string concatenation
1253 <primary>binary strings</primary>
1254 <secondary>concatenation</secondary>
1257 <entry><literal>'\\\\Postgre'::bytea || '\\047SQL\\000'::bytea</literal></entry>
1258 <entry><literal>\\Postgre'SQL\000</literal></entry>
1262 <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
1263 <entry><type>integer</type></entry>
1264 <entry>number of bytes in binary string</entry>
1265 <entry><literal>octet_length('jo\\000se'::bytea)</literal></entry>
1266 <entry><literal>5</literal></entry>
1270 <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
1271 <entry><type>integer</type></entry>
1272 <entry>location of specified substring</entry>
1273 <entry><literal>position('\\000om'::bytea in 'Th\\000omas'::bytea)</literal></entry>
1274 <entry><literal>3</literal></entry>
1278 <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
1279 <entry><type>bytea</type></entry>
1283 <primary>substring</primary>
1286 <entry><literal>substring('Th\\000omas'::bytea from 2 for 3)</literal></entry>
1287 <entry><literal>h\000o</literal></entry>
1292 <function>trim</function>(<optional>both</optional>
1293 <parameter>characters</parameter> from
1294 <parameter>string</parameter>)
1296 <entry><type>bytea</type></entry>
1298 Removes the longest string containing only the
1299 <parameter>characters</parameter> from the
1300 beginning/end/both ends of the <parameter>string</parameter>.
1302 <entry><literal>trim('\\000'::bytea from '\\000Tom\\000'::bytea)</literal></entry>
1303 <entry><literal>Tom</literal></entry>
1310 Additional binary string manipulation functions are available and are
1311 listed below. Some of them are used internally to implement the
1312 <acronym>SQL</acronym>-standard string functions listed above.
1315 <table id="functions-binarystring-other">
1316 <title>Other Binary String Functions</title>
1320 <entry>Function</entry>
1321 <entry>Return Type</entry>
1322 <entry>Description</entry>
1323 <entry>Example</entry>
1324 <entry>Result</entry>
1330 <entry><function>btrim</function>(<parameter>string</parameter>
1331 <type>bytea</type> <parameter>trim</parameter> <type>bytea</type>)</entry>
1332 <entry><type>bytea</type></entry>
1334 Remove (trim) the longest string consisting only of characters
1335 in <parameter>trim</parameter> from the start and end of
1336 <parameter>string</parameter>.
1338 <entry><literal>btrim('\\000trim\\000'::bytea,'\\000'::bytea)</literal></entry>
1339 <entry><literal>trim</literal></entry>
1343 <entry><function>length</function>(<parameter>string</parameter>)</entry>
1344 <entry><type>integer</type></entry>
1346 length of binary string
1348 <primary>binary strings</primary>
1349 <secondary>length</secondary>
1352 <primary>length</primary>
1353 <secondary>binary strings</secondary>
1354 <see>binary strings, length</see>
1357 <entry><literal>length('jo\\000se'::bytea)</literal></entry>
1358 <entry><literal>5</literal></entry>
1363 <function>encode</function>(<parameter>string</parameter> <type>bytea</type>,
1364 <parameter>type</parameter> <type>text</type>)
1366 <entry><type>text</type></entry>
1368 Encodes binary string to <acronym>ASCII</acronym>-only representation. Supported
1369 types are: 'base64', 'hex', 'escape'.
1371 <entry><literal>encode('123\\000456'::bytea, 'escape')</literal></entry>
1372 <entry><literal>123\000456</literal></entry>
1377 <function>decode</function>(<parameter>string</parameter> <type>text</type>,
1378 <parameter>type</parameter> <type>text</type>)
1380 <entry><type>bytea</type></entry>
1382 Decodes binary string from <parameter>string</parameter> previously
1383 encoded with encode(). Parameter type is same as in encode().
1385 <entry><literal>decode('123\\000456', 'escape')</literal></entry>
1386 <entry><literal>123\000456</literal></entry>
1396 <sect1 id="functions-matching">
1397 <title>Pattern Matching</title>
1400 There are two separate approaches to pattern matching provided by
1401 <productname>PostgreSQL</productname>: the traditional
1402 <acronym>SQL</acronym>
1403 <function>LIKE</function> operator and the more recent
1404 <acronym>SQL99</acronym>
1405 <function>SIMILAR TO</function> operator implementing
1406 <acronym>POSIX</acronym>-style regular expressions.
1407 Additionally, a pattern matching function,
1408 <function>SUBSTRING</function>, is available, as defined in
1409 <acronym>SQL99</acronym>.
1414 If you have pattern matching needs that go beyond this,
1415 consider writing a user-defined function in Perl or Tcl.
1420 Both <function>LIKE</function> and <function>SIMILAR TO</function>
1421 are SQL-standard operators which are also available in alternate
1422 forms as <productname>PostgreSQL</productname> operators; look at
1423 <literal>~</literal> and <literal>~~</literal> for examples.
1426 <sect2 id="functions-like">
1427 <title><function>LIKE</function></title>
1430 <primary>like</primary>
1434 <replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
1435 <replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
1439 Every <replaceable>pattern</replaceable> defines a set of strings.
1440 The <function>LIKE</function> expression returns true if the
1441 <replaceable>string</replaceable> is contained in the set of
1442 strings represented by <replaceable>pattern</replaceable>. (As
1443 expected, the <function>NOT LIKE</function> expression returns
1444 false if <function>LIKE</function> returns true, and vice versa.
1445 An equivalent expression is
1446 <literal>NOT (<replaceable>string</replaceable> LIKE
1447 <replaceable>pattern</replaceable>)</literal>.)
1451 If <replaceable>pattern</replaceable> does not contain percent
1452 signs or underscore, then the pattern only represents the string
1453 itself; in that case <function>LIKE</function> acts like the
1454 equals operator. An underscore (<literal>_</literal>) in
1455 <replaceable>pattern</replaceable> stands for (matches) any single
1456 character; a percent sign (<literal>%</literal>) matches any string
1457 of zero or more characters.
1464 'abc' LIKE 'abc' <lineannotation>true</lineannotation>
1465 'abc' LIKE 'a%' <lineannotation>true</lineannotation>
1466 'abc' LIKE '_b_' <lineannotation>true</lineannotation>
1467 'abc' LIKE 'c' <lineannotation>false</lineannotation>
1473 <function>LIKE</function> pattern matches always cover the entire
1474 string. To match a pattern anywhere within a string, the
1475 pattern must therefore start and end with a percent sign.
1479 To match a literal underscore or percent sign without matching
1480 other characters, the respective character in
1481 <replaceable>pattern</replaceable> must be
1482 preceded by the escape character. The default escape
1483 character is the backslash but a different one may be selected by
1484 using the <literal>ESCAPE</literal> clause. To match the escape
1485 character itself, write two escape characters.
1489 Note that the backslash already has a special meaning in string
1490 literals, so to write a pattern constant that contains a backslash
1491 you must write two backslashes in the query. Thus, writing a pattern
1492 that actually matches a literal backslash means writing four backslashes
1493 in the query. You can avoid this by selecting a different escape
1494 character with <literal>ESCAPE</literal>; then backslash is not special
1495 to <function>LIKE</function> anymore. (But it is still special to the string
1496 literal parser, so you still need two of them.)
1500 It's also possible to select no escape character by writing
1501 <literal>ESCAPE ''</literal>. This effectively disables the
1502 escape mechanism and
1503 turns off the special meaning of underscore and percent signs in
1508 The keyword <token>ILIKE</token> can be used instead of
1509 <token>LIKE</token> to make the match case insensitive according
1510 to the active locale. This is not in the <acronym>SQL</acronym> standard but is a
1511 <productname>PostgreSQL</productname> extension.
1515 The operator <literal>~~</literal> is equivalent to
1516 <function>LIKE</function>, and <literal>~~*</literal> corresponds to
1517 <function>ILIKE</function>. There are also
1518 <literal>!~~</literal> and <literal>!~~*</literal> operators that
1519 represent <function>NOT LIKE</function> and <function>NOT
1520 ILIKE</function>. All of these operators are
1521 <productname>PostgreSQL</productname>-specific.
1526 <sect2 id="functions-regexp">
1527 <title><function>SIMILAR TO</function> and <acronym>POSIX</acronym>
1528 Regular Expressions</title>
1530 <indexterm zone="functions-regexp">
1531 <primary>regular expressions</primary>
1532 <seealso>pattern matching</seealso>
1536 <title>Regular Expression Match Operators</title>
1541 <entry>Operator</entry>
1542 <entry>Description</entry>
1543 <entry>Example</entry>
1549 <entry> <literal>~</literal> </entry>
1550 <entry>Matches regular expression, case sensitive</entry>
1551 <entry><literal>'thomas' ~ '.*thomas.*'</literal></entry>
1555 <entry> <literal>~*</literal> </entry>
1556 <entry>Matches regular expression, case insensitive</entry>
1557 <entry><literal>'thomas' ~* '.*Thomas.*'</literal></entry>
1561 <entry> <literal>!~</literal> </entry>
1562 <entry>Does not match regular expression, case sensitive</entry>
1563 <entry><literal>'thomas' !~ '.*Thomas.*'</literal></entry>
1567 <entry> <literal>!~*</literal> </entry>
1568 <entry>Does not match regular expression, case insensitive</entry>
1569 <entry><literal>'thomas' !~* '.*vadim.*'</literal></entry>
1573 <entry> <literal>SIMILAR TO</literal> </entry>
1574 <entry>Matches regular expression, case sensitive</entry>
1575 <entry><literal>'thomas' SIMILAR TO '.*thomas.*'</literal></entry>
1582 <acronym>POSIX</acronym> regular expressions provide a more
1584 pattern matching than the <function>LIKE</function> function.
1585 Many Unix tools such as <command>egrep</command>,
1586 <command>sed</command>, or <command>awk</command> use a pattern
1587 matching language that is similar to the one described here.
1591 A regular expression is a character sequence that is an
1592 abbreviated definition of a set of strings (a <firstterm>regular
1593 set</firstterm>). A string is said to match a regular expression
1594 if it is a member of the regular set described by the regular
1595 expression. As with <function>LIKE</function>, pattern characters
1596 match string characters exactly unless they are special characters
1597 in the regular expression language --- but regular expressions use
1598 different special characters than <function>LIKE</function> does.
1599 Unlike <function>LIKE</function> patterns, a
1600 regular expression is allowed to match anywhere within a string, unless
1601 the regular expression is explicitly anchored to the beginning or
1609 'abc' SIMILAR TO 'abc' <lineannotation>true</lineannotation>
1610 'abc' SIMILAR TO '^a' <lineannotation>true</lineannotation>
1611 'abc' SIMILAR TO '(b|d)' <lineannotation>true</lineannotation>
1612 'abc' SIMILAR TO '^(b|c)' <lineannotation>false</lineannotation>
1617 <!-- derived from the re_format.7 man page -->
1619 Regular expressions (<quote>RE</quote>s), as defined in
1620 <acronym>POSIX</acronym>
1621 1003.2, come in two forms: modern REs (roughly those of
1622 <command>egrep</command>; 1003.2 calls these
1623 <quote>extended</quote> REs) and obsolete REs (roughly those of
1624 <command>ed</command>; 1003.2 <quote>basic</quote> REs).
1625 <productname>PostgreSQL</productname> implements the modern form.
1629 A (modern) RE is one or more non-empty
1630 <firstterm>branches</firstterm>, separated by
1631 <literal>|</literal>. It matches anything that matches one of the
1636 A branch is one or more <firstterm>pieces</firstterm>,
1637 concatenated. It matches a match for the first, followed by a
1638 match for the second, etc.
1642 A piece is an <firstterm>atom</firstterm> possibly followed by a
1643 single <literal>*</literal>, <literal>+</literal>,
1644 <literal>?</literal>, or <firstterm>bound</firstterm>. An atom
1645 followed by <literal>*</literal> matches a sequence of 0 or more
1646 matches of the atom. An atom followed by <literal>+</literal>
1647 matches a sequence of 1 or more matches of the atom. An atom
1648 followed by <literal>?</literal> matches a sequence of 0 or 1
1649 matches of the atom.
1653 A <firstterm>bound</firstterm> is <literal>{</literal> followed by
1654 an unsigned decimal integer, possibly followed by
1655 <literal>,</literal> possibly followed by another unsigned decimal
1656 integer, always followed by <literal>}</literal>. The integers
1657 must lie between 0 and <symbol>RE_DUP_MAX</symbol> (255)
1658 inclusive, and if there are two of them, the first may not exceed
1659 the second. An atom followed by a bound containing one integer
1660 <replaceable>i</replaceable> and no comma matches a sequence of
1661 exactly <replaceable>i</replaceable> matches of the atom. An atom
1662 followed by a bound containing one integer
1663 <replaceable>i</replaceable> and a comma matches a sequence of
1664 <replaceable>i</replaceable> or more matches of the atom. An atom
1665 followed by a bound containing two integers
1666 <replaceable>i</replaceable> and <replaceable>j</replaceable>
1667 matches a sequence of <replaceable>i</replaceable> through
1668 <replaceable>j</replaceable> (inclusive) matches of the atom.
1673 A repetition operator (<literal>?</literal>,
1674 <literal>*</literal>, <literal>+</literal>, or bounds) cannot
1675 follow another repetition operator. A repetition operator cannot
1676 begin an expression or subexpression or follow
1677 <literal>^</literal> or <literal>|</literal>.
1682 An <firstterm>atom</firstterm> is a regular expression enclosed in
1683 <literal>()</literal> (matching a match for the regular
1684 expression), an empty set of <literal>()</literal> (matching the
1685 null string), a <firstterm>bracket expression</firstterm> (see
1686 below), <literal>.</literal> (matching any single character),
1687 <literal>^</literal> (matching the null string at the beginning of the
1688 input string), <literal>$</literal> (matching the null string at the end
1689 of the input string), a <literal>\</literal> followed by one of the
1690 characters <literal>^.[$()|*+?{\</literal> (matching that
1691 character taken as an ordinary character), a <literal>\</literal>
1692 followed by any other character (matching that character taken as
1693 an ordinary character, as if the <literal>\</literal> had not been
1694 present), or a single character with no other significance
1695 (matching that character). A <literal>{</literal> followed by a
1696 character other than a digit is an ordinary character, not the
1697 beginning of a bound. It is illegal to end an RE with
1698 <literal>\</literal>.
1702 Note that the backslash (<literal>\</literal>) already has a special
1704 literals, so to write a pattern constant that contains a backslash
1705 you must write two backslashes in the query.
1709 A <firstterm>bracket expression</firstterm> is a list of
1710 characters enclosed in <literal>[]</literal>. It normally matches
1711 any single character from the list (but see below). If the list
1712 begins with <literal>^</literal>, it matches any single character
1713 (but see below) not from the rest of the list. If two characters
1714 in the list are separated by <literal>-</literal>, this is
1715 shorthand for the full range of characters between those two
1716 (inclusive) in the collating sequence,
1717 e.g. <literal>[0-9]</literal> in <acronym>ASCII</acronym> matches
1718 any decimal digit. It is illegal for two ranges to share an
1719 endpoint, e.g. <literal>a-c-e</literal>. Ranges are very
1720 collating-sequence-dependent, and portable programs should avoid
1725 To include a literal <literal>]</literal> in the list, make it the
1726 first character (following a possible <literal>^</literal>). To
1727 include a literal <literal>-</literal>, make it the first or last
1728 character, or the second endpoint of a range. To use a literal
1729 <literal>-</literal> as the first endpoint of a range, enclose it
1730 in <literal>[.</literal> and <literal>.]</literal> to make it a
1731 collating element (see below). With the exception of these and
1732 some combinations using <literal>[</literal> (see next
1733 paragraphs), all other special characters, including
1734 <literal>\</literal>, lose their special significance within a
1739 Within a bracket expression, a collating element (a character, a
1740 multiple-character sequence that collates as if it were a single
1741 character, or a collating-sequence name for either) enclosed in
1742 <literal>[.</literal> and <literal>.]</literal> stands for the
1743 sequence of characters of that collating element. The sequence is
1744 a single element of the bracket expression's list. A bracket
1745 expression containing a multiple-character collating element can thus
1746 match more than one character, e.g. if the collating sequence
1747 includes a <literal>ch</literal> collating element, then the RE
1748 <literal>[[.ch.]]*c</literal> matches the first five characters of
1749 <literal>chchcc</literal>.
1753 Within a bracket expression, a collating element enclosed in
1754 <literal>[=</literal> and <literal>=]</literal> is an equivalence
1755 class, standing for the sequences of characters of all collating
1756 elements equivalent to that one, including itself. (If there are
1757 no other equivalent collating elements, the treatment is as if the
1758 enclosing delimiters were <literal>[.</literal> and
1759 <literal>.]</literal>.) For example, if <literal>o</literal> and
1760 <literal>^</literal> are the members of an equivalence class, then
1761 <literal>[[=o=]]</literal>, <literal>[[=^=]]</literal>, and
1762 <literal>[o^]</literal> are all synonymous. An equivalence class
1763 may not be an endpoint of a range.
1767 Within a bracket expression, the name of a character class
1768 enclosed in <literal>[:</literal> and <literal>:]</literal> stands
1769 for the list of all characters belonging to that class. Standard
1770 character class names are: <literal>alnum</literal>,
1771 <literal>alpha</literal>, <literal>blank</literal>,
1772 <literal>cntrl</literal>, <literal>digit</literal>,
1773 <literal>graph</literal>, <literal>lower</literal>,
1774 <literal>print</literal>, <literal>punct</literal>,
1775 <literal>space</literal>, <literal>upper</literal>,
1776 <literal>xdigit</literal>. These stand for the character classes
1778 <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>.
1779 A locale may provide others. A character class may not be used as
1780 an endpoint of a range.
1784 There are two special cases of bracket expressions: the bracket
1785 expressions <literal>[[:<:]]</literal> and
1786 <literal>[[:>:]]</literal> match the null string at the beginning
1787 and end of a word respectively. A word is defined as a sequence
1788 of word characters which is neither preceded nor followed by word
1789 characters. A word character is an alnum character (as defined by
1790 <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>)
1791 or an underscore. This is an extension, compatible with but not
1792 specified by <acronym>POSIX</acronym> 1003.2, and should be used with caution in
1793 software intended to be portable to other systems.
1797 In the event that an RE could match more than one substring of a
1798 given string, the RE matches the one starting earliest in the
1799 string. If the RE could match more than one substring starting at
1800 that point, it matches the longest. Subexpressions also match the
1801 longest possible substrings, subject to the constraint that the
1802 whole match be as long as possible, with subexpressions starting
1803 earlier in the RE taking priority over ones starting later. Note
1804 that higher-level subexpressions thus take priority over their
1805 lower-level component subexpressions.
1809 Match lengths are measured in characters, not collating
1810 elements. A null string is considered longer than no match at
1811 all. For example, <literal>bb*</literal> matches the three middle
1812 characters of <literal>abbbc</literal>,
1813 <literal>(wee|week)(knights|nights)</literal> matches all ten
1814 characters of <literal>weeknights</literal>, when
1815 <literal>(.*).*</literal> is matched against
1816 <literal>abc</literal> the parenthesized subexpression matches all
1817 three characters, and when <literal>(a*)*</literal> is matched
1818 against <literal>bc</literal> both the whole RE and the
1819 parenthesized subexpression match the null string.
1823 If case-independent matching is specified, the effect is much as
1824 if all case distinctions had vanished from the alphabet. When an
1825 alphabetic that exists in multiple cases appears as an ordinary
1826 character outside a bracket expression, it is effectively
1827 transformed into a bracket expression containing both cases,
1828 e.g. <literal>x</literal> becomes <literal>[xX]</literal>. When
1829 it appears inside a bracket expression, all case counterparts of
1830 it are added to the bracket expression, so that (e.g.)
1831 <literal>[x]</literal> becomes <literal>[xX]</literal> and
1832 <literal>[^x]</literal> becomes <literal>[^xX]</literal>.
1836 There is no particular limit on the length of REs, except insofar
1837 as memory is limited. Memory usage is approximately linear in RE
1838 size, and largely insensitive to RE complexity, except for bounded
1839 repetitions. Bounded repetitions are implemented by macro
1840 expansion, which is costly in time and space if counts are large
1841 or bounded repetitions are nested. An RE like, say,
1842 <literal>((((a{1,100}){1,100}){1,100}){1,100}){1,100}</literal>
1843 will (eventually) run almost any existing machine out of swap
1847 This was written in 1994, mind you. The
1848 numbers have probably changed, but the problem
1853 <!-- end re_format.7 man page -->
1859 <sect1 id="functions-formatting">
1860 <title>Data Type Formatting Functions</title>
1862 <indexterm zone="functions-formatting">
1863 <primary>formatting</primary>
1867 <title>Author</title>
1869 Written by Karel Zak (<email>zakkr@zf.jcu.cz</email>) on 2000-01-24
1874 The <productname>PostgreSQL</productname> formatting functions
1875 provide a powerful set of tools for converting various data types
1876 (date/time, integer, floating point, numeric) to formatted strings
1877 and for converting from formatted strings to specific data types.
1878 These functions all follow a common calling convention: the first
1879 argument is the value to be formatted and the second argument is a
1880 template that defines the output or input format.
1883 <table tocentry="1">
1884 <title>Formatting Functions</title>
1888 <entry>Function</entry>
1889 <entry>Returns</entry>
1890 <entry>Description</entry>
1891 <entry>Example</entry>
1896 <entry><function>to_char</function>(<type>timestamp</type>, <type>text</type>)</entry>
1897 <entry><type>text</type></entry>
1898 <entry>convert time stamp to string</entry>
1899 <entry><literal>to_char(timestamp 'now','HH12:MI:SS')</literal></entry>
1902 <entry><function>to_char</function>(<type>interval</type>, <type>text</type>)</entry>
1903 <entry><type>text</type></entry>
1904 <entry>convert interval to string</entry>
1905 <entry><literal>to_char(interval '15h 2m 12s','HH24:MI:SS')</literal></entry>
1908 <entry><function>to_char</function>(<type>int</type>, <type>text</type>)</entry>
1909 <entry><type>text</type></entry>
1910 <entry>convert int4/int8 to string</entry>
1911 <entry><literal>to_char(125, '999')</literal></entry>
1914 <entry><function>to_char</function>(<type>double precision</type>,
1915 <type>text</type>)</entry>
1916 <entry><type>text</type></entry>
1917 <entry>convert real/double precision to string</entry>
1918 <entry><literal>to_char(125.8, '999D9')</literal></entry>
1921 <entry><function>to_char</function>(<type>numeric</type>, <type>text</type>)</entry>
1922 <entry><type>text</type></entry>
1923 <entry>convert numeric to string</entry>
1924 <entry><literal>to_char(numeric '-125.8', '999D99S')</literal></entry>
1927 <entry><function>to_date</function>(<type>text</type>, <type>text</type>)</entry>
1928 <entry><type>date</type></entry>
1929 <entry>convert string to date</entry>
1930 <entry><literal>to_date('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
1933 <entry><function>to_timestamp</function>(<type>text</type>, <type>text</type>)</entry>
1934 <entry><type>timestamp</type></entry>
1935 <entry>convert string to time stamp</entry>
1936 <entry><literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
1939 <entry><function>to_number</function>(<type>text</type>, <type>text</type>)</entry>
1940 <entry><type>numeric</type></entry>
1941 <entry>convert string to numeric</entry>
1942 <entry><literal>to_number('12,454.8-', '99G999D9S')</literal></entry>
1949 In an output template string, there are certain patterns that are
1950 recognized and replaced with appropriately-formatted data from the value
1951 to be formatted. Any text that is not a template pattern is simply
1952 copied verbatim. Similarly, in an input template string, template patterns
1953 identify the parts of the input data string to be looked at and the
1954 values to be found there.
1957 <table tocentry="1">
1958 <title>Template patterns for date/time conversions</title>
1962 <entry>Pattern</entry>
1963 <entry>Description</entry>
1968 <entry><literal>HH</literal></entry>
1969 <entry>hour of day (01-12)</entry>
1972 <entry><literal>HH12</literal></entry>
1973 <entry>hour of day (01-12)</entry>
1976 <entry><literal>HH24</literal></entry>
1977 <entry>hour of day (00-23)</entry>
1980 <entry><literal>MI</literal></entry>
1981 <entry>minute (00-59)</entry>
1984 <entry><literal>SS</literal></entry>
1985 <entry>second (00-59)</entry>
1988 <entry><literal>MS</literal></entry>
1989 <entry>millisecond (000-999)</entry>
1992 <entry><literal>US</literal></entry>
1993 <entry>microsecond (000000-999999)</entry>
1996 <entry><literal>SSSS</literal></entry>
1997 <entry>seconds past midnight (0-86399)</entry>
2000 <entry><literal>AM</literal> or <literal>A.M.</literal> or
2001 <literal>li</literal>literal> or <literal>P.M.</literal></entry>
2002 <entry>meridian indicator (upper case)</entry>
2005 <entry><literal>am</literal> or <literal>a.m.</literal> or
2006 <literal>li</literal>literal> or <literal>p.m.</literal></entry>
2007 <entry>meridian indicator (lower case)</entry>
2010 <entry><literal>Y,YYY</literal></entry>
2011 <entry>year (4 and more digits) with comma</entry>
2014 <entry><literal>YYYY</literal></entry>
2015 <entry>year (4 and more digits)</entry>
2018 <entry><literal>YYY</literal></entry>
2019 <entry>last 3 digits of year</entry>
2022 <entry><literal>YY</literal></entry>
2023 <entry>last 2 digits of year</entry>
2026 <entry><literal>Y</literal></entry>
2027 <entry>last digit of year</entry>
2030 <entry><literal>BC</literal> or <literal>B.C.</literal> or
2031 <literal>li</literal>literal> or <literal>A.D.</literal></entry>
2032 <entry>era indicator (upper case)</entry>
2035 <entry><literal>bc</literal> or <literal>b.c.</literal> or
2036 <literal>li</literal>literal> or <literal>a.d.</literal></entry>
2037 <entry>era indicator (lower case)</entry>
2040 <entry><literal>MONTH</literal></entry>
2041 <entry>full upper case month name (blank-padded to 9 chars)</entry>
2044 <entry><literal>Month</literal></entry>
2045 <entry>full mixed case month name (blank-padded to 9 chars)</entry>
2048 <entry><literal>month</literal></entry>
2049 <entry>full lower case month name (blank-padded to 9 chars)</entry>
2052 <entry><literal>MON</literal></entry>
2053 <entry>abbreviated upper case month name (3 chars)</entry>
2056 <entry><literal>Mon</literal></entry>
2057 <entry>abbreviated mixed case month name (3 chars)</entry>
2060 <entry><literal>mon</literal></entry>
2061 <entry>abbreviated lower case month name (3 chars)</entry>
2064 <entry><literal>MM</literal></entry>
2065 <entry>month number (01-12)</entry>
2068 <entry><literal>DAY</literal></entry>
2069 <entry>full upper case day name (blank-padded to 9 chars)</entry>
2072 <entry><literal>Day</literal></entry>
2073 <entry>full mixed case day name (blank-padded to 9 chars)</entry>
2076 <entry><literal>day</literal></entry>
2077 <entry>full lower case day name (blank-padded to 9 chars)</entry>
2080 <entry><literal>DY</literal></entry>
2081 <entry>abbreviated upper case day name (3 chars)</entry>
2084 <entry><literal>Dy</literal></entry>
2085 <entry>abbreviated mixed case day name (3 chars)</entry>
2088 <entry><literal>dy</literal></entry>
2089 <entry>abbreviated lower case day name (3 chars)</entry>
2092 <entry><literal>DDD</literal></entry>
2093 <entry>day of year (001-366)</entry>
2096 <entry><literal>DD</literal></entry>
2097 <entry>day of month (01-31)</entry>
2100 <entry><literal>D</literal></entry>
2101 <entry>day of week (1-7; SUN=1)</entry>
2104 <entry><literal>W</literal></entry>
2105 <entry>week of month (1-5) where first week start on the first day of the month</entry>
2108 <entry><literal>WW</literal></entry>
2109 <entry>week number of year (1-53) where first week start on the first day of the year</entry>
2112 <entry><literal>IW</literal></entry>
2113 <entry>ISO week number of year (The first Thursday of the new year is in week 1.)</entry>
2116 <entry><literal>CC</literal></entry>
2117 <entry>century (2 digits)</entry>
2120 <entry><literal>J</literal></entry>
2121 <entry>Julian Day (days since January 1, 4712 BC)</entry>
2124 <entry><literal>Q</literal></entry>
2125 <entry>quarter</entry>
2128 <entry><literal>RM</literal></entry>
2129 <entry>month in Roman Numerals (I-XII; I=January) - upper case</entry>
2132 <entry><literal>rm</literal></entry>
2133 <entry>month in Roman Numerals (I-XII; I=January) - lower case</entry>
2136 <entry><literal>TZ</literal></entry>
2137 <entry>timezone name - upper case</entry>
2140 <entry><literal>tz</literal></entry>
2141 <entry>timezone name - lower case</entry>
2148 Certain modifiers may be applied to any template pattern to alter its
2149 behavior. For example, <quote><literal>FMMonth</literal></quote>
2150 is the <quote><literal>Month</literal></quote> pattern with the
2151 <quote><literal>FM</literal></quote> prefix.
2154 <table tocentry="1">
2155 <title>Template pattern modifiers for date/time conversions</title>
2159 <entry>Modifier</entry>
2160 <entry>Description</entry>
2161 <entry>Example</entry>
2166 <entry><literal>FM</literal> prefix</entry>
2167 <entry>fill mode (suppress padding blanks and zeroes)</entry>
2168 <entry><literal>FMMonth</literal></entry>
2171 <entry><literal>TH</literal> suffix</entry>
2172 <entry>add upper-case ordinal number suffix</entry>
2173 <entry><literal>DDTH</literal></entry>
2176 <entry><literal>th</literal> suffix</entry>
2177 <entry>add lower-case ordinal number suffix</entry>
2178 <entry><literal>DDth</literal></entry>
2181 <entry><literal>FX</literal> prefix</entry>
2182 <entry>Fixed format global option (see below)</entry>
2183 <entry><literal>FX Month DD Day</literal></entry>
2186 <entry><literal>SP</literal> suffix</entry>
2187 <entry>spell mode (not yet implemented)</entry>
2188 <entry><literal>DDSP</literal></entry>
2200 <literal>FM</literal> suppresses leading zeroes or trailing blanks
2201 that would otherwise be added to make the output of a pattern be
2208 <function>to_timestamp</function> and <function>to_date</function>
2209 skip multiple blank spaces in the input string if the <literal>FX</literal> option
2210 is not used. <literal>FX</literal> must be specified as the first item
2211 in the template; for example
2212 <literal>to_timestamp('2000 JUN','YYYY MON')</literal> is right, but
2213 <literal>to_timestamp('2000 JUN','FXYYYY MON')</literal> returns an error,
2214 because <function>to_timestamp</function> expects one blank space only.
2220 If a backslash (<quote><literal>\</literal></quote>) is desired
2221 in a string constant, a double backslash
2222 (<quote><literal>\\</literal></quote>) must be entered; for
2223 example <literal>'\\HH\\MI\\SS'</literal>. This is true for
2224 any string constant in <productname>PostgreSQL</productname>.
2230 Ordinary text is allowed in <function>to_char</function>
2231 templates and will be output literally. You can put a substring
2232 in double quotes to force it to be interpreted as literal text
2233 even if it contains pattern keywords. For example, in
2234 <literal>'"Hello Year: "YYYY'</literal>, the <literal>YYYY</literal>
2235 will be replaced by year data, but the single <literal>Y</literal>
2242 If you want to have a double quote in the output you must
2243 precede it with a backslash, for example <literal>'\\"YYYY
2244 Month\\"'</literal>. <!-- "" font-lock sanity :-) -->
2250 <literal>YYYY</literal> conversion from string to <type>timestamp</type> or
2251 <type>date</type> is restricted if you use a year with more than 4 digits. You must
2252 use some non-digit character or template after <literal>YYYY</literal>,
2253 otherwise the year is always interpreted as 4 digits. For example
2255 <literal>to_date('200001131', 'YYYYMMDD')</literal> will be
2256 interpreted as a 4-digit year; better is to use a non-digit
2257 separator after the year, like
2258 <literal>to_date('20000-1131', 'YYYY-MMDD')</literal> or
2259 <literal>to_date('20000Nov31', 'YYYYMonDD')</literal>.
2265 Millisecond <literal>MS</literal> and microsecond <literal>US</literal>
2266 values in a conversion from string to time stamp are used as part of the
2267 seconds after the decimal point. For example
2268 <literal>to_timestamp('12:3', 'SS:MS')</literal> is not 3 milliseconds,
2269 but 300, because the conversion counts it as 12 + 0.3.
2270 This means for the format <literal>SS:MS</literal>, the input values
2271 <literal>12:3</literal>, <literal>12:30</literal>, and <literal>12:300</literal> specify the
2272 same number of milliseconds. To get three milliseconds, one must use
2273 <literal>12:003</literal>, which the conversion counts as
2274 12 + 0.003 = 12.003 seconds.
2280 <literal>to_timestamp('15:12:02.020.001230','HH:MI:SS.MS.US')</literal>
2281 is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
2282 1230 microseconds = 2.021230 seconds.
2288 <table tocentry="1">
2289 <title>Template patterns for numeric conversions</title>
2293 <entry>Pattern</entry>
2294 <entry>Description</entry>
2299 <entry><literal>9</literal></entry>
2300 <entry>value with the specified number of digits</entry>
2303 <entry><literal>0</literal></entry>
2304 <entry>value with leading zeros</entry>
2307 <entry><literal>.</literal> (period)</entry>
2308 <entry>decimal point</entry>
2311 <entry><literal>,</literal> (comma)</entry>
2312 <entry>group (thousand) separator</entry>
2315 <entry><literal>PR</literal></entry>
2316 <entry>negative value in angle brackets</entry>
2319 <entry><literal>S</literal></entry>
2320 <entry>negative value with minus sign (uses locale)</entry>
2323 <entry><literal>L</literal></entry>
2324 <entry>currency symbol (uses locale)</entry>
2327 <entry><literal>D</literal></entry>
2328 <entry>decimal point (uses locale)</entry>
2331 <entry><literal>G</literal></entry>
2332 <entry>group separator (uses locale)</entry>
2335 <entry><literal>MI</literal></entry>
2336 <entry>minus sign in specified position (if number < 0)</entry>
2339 <entry><literal>PL</literal></entry>
2340 <entry>plus sign in specified position (if number > 0)</entry>
2343 <entry><literal>SG</literal></entry>
2344 <entry>plus/minus sign in specified position</entry>
2347 <entry><literal>RN</literal></entry>
2348 <entry>roman numeral (input between 1 and 3999)</entry>
2351 <entry><literal>TH</literal> or <literal>th</literal></entry>
2352 <entry>convert to ordinal number</entry>
2355 <entry><literal>V</literal></entry>
2356 <entry>shift <replaceable>n</replaceable> digits (see
2360 <entry><literal>EEEE</literal></entry>
2361 <entry>scientific notation (not implemented yet)</entry>
2373 A sign formatted using <literal>SG</literal>, <literal>PL</literal>, or
2374 <literal>MI</literal> is not an anchor in
2375 the number; for example,
2376 <literal>to_char(-12, 'S9999')</literal> produces <literal>' -12'</literal>,
2377 but <literal>to_char(-12, 'MI9999')</literal> produces <literal>'- 12'</literal>.
2378 The Oracle implementation does not allow the use of
2379 <literal>MI</literal> ahead of <literal>9</literal>, but rather
2380 requires that <literal>9</literal> precede
2381 <literal>MI</literal>.
2387 <literal>9</literal> specifies a value with the same number of
2388 digits as there are <literal>9</literal>s. If a digit is
2389 not available use blank space.
2395 <literal>TH</literal> does not convert values less than zero
2396 and does not convert decimal numbers.
2402 <literal>PL</literal>, <literal>SG</literal>, and
2403 <literal>TH</literal> are <productname>PostgreSQL</productname>
2410 <literal>V</literal> effectively
2411 multiplies the input values by
2412 <literal>10^<replaceable>n</replaceable></literal>, where
2413 <replaceable>n</replaceable> is the number of digits following
2414 <literal>V</literal>.
2415 <function>to_char</function> does not support the use of
2416 <literal>V</literal> combined with a decimal point.
2417 (E.g., <literal>99.9V99</literal> is not allowed.)
2423 <table tocentry="1">
2424 <title><function>to_char</function> Examples</title>
2428 <entry>Input</entry>
2429 <entry>Output</entry>
2434 <entry><literal>to_char(now(),'Day, DD HH12:MI:SS')</literal></entry>
2435 <entry><literal>'Tuesday , 06 05:39:18'</literal></entry>
2438 <entry><literal>to_char(now(),'FMDay, FMDD HH12:MI:SS')</literal></entry>
2439 <entry><literal>'Tuesday, 6 05:39:18'</literal></entry>
2442 <entry><literal>to_char(-0.1,'99.99')</literal></entry>
2443 <entry><literal>' -.10'</literal></entry>
2446 <entry><literal>to_char(-0.1,'FM9.99')</literal></entry>
2447 <entry><literal>'-.1'</literal></entry>
2450 <entry><literal>to_char(0.1,'0.9')</literal></entry>
2451 <entry><literal>' 0.1'</literal></entry>
2454 <entry><literal>to_char(12,'9990999.9')</literal></entry>
2455 <entry><literal>' 0012.0'</literal></entry>
2458 <entry><literal>to_char(12,'FM9990999.9')</literal></entry>
2459 <entry><literal>'0012'</literal></entry>
2462 <entry><literal>to_char(485,'999')</literal></entry>
2463 <entry><literal>' 485'</literal></entry>
2466 <entry><literal>to_char(-485,'999')</literal></entry>
2467 <entry><literal>'-485'</literal></entry>
2470 <entry><literal>to_char(485,'9 9 9')</literal></entry>
2471 <entry><literal>' 4 8 5'</literal></entry>
2474 <entry><literal>to_char(1485,'9,999')</literal></entry>
2475 <entry><literal>' 1,485'</literal></entry>
2478 <entry><literal>to_char(1485,'9G999')</literal></entry>
2479 <entry><literal>' 1 485'</literal></entry>
2482 <entry><literal>to_char(148.5,'999.999')</literal></entry>
2483 <entry><literal>' 148.500'</literal></entry>
2486 <entry><literal>to_char(148.5,'999D999')</literal></entry>
2487 <entry><literal>' 148,500'</literal></entry>
2490 <entry><literal>to_char(3148.5,'9G999D999')</literal></entry>
2491 <entry><literal>' 3 148,500'</literal></entry>
2494 <entry><literal>to_char(-485,'999S')</literal></entry>
2495 <entry><literal>'485-'</literal></entry>
2498 <entry><literal>to_char(-485,'999MI')</literal></entry>
2499 <entry><literal>'485-'</literal></entry>
2502 <entry><literal>to_char(485,'999MI')</literal></entry>
2503 <entry><literal>'485'</literal></entry>
2506 <entry><literal>to_char(485,'PL999')</literal></entry>
2507 <entry><literal>'+485'</literal></entry>
2510 <entry><literal>to_char(485,'SG999')</literal></entry>
2511 <entry><literal>'+485'</literal></entry>
2514 <entry><literal>to_char(-485,'SG999')</literal></entry>
2515 <entry><literal>'-485'</literal></entry>
2518 <entry><literal>to_char(-485,'9SG99')</literal></entry>
2519 <entry><literal>'4-85'</literal></entry>
2522 <entry><literal>to_char(-485,'999PR')</literal></entry>
2523 <entry><literal>'<485>'</literal></entry>
2526 <entry><literal>to_char(485,'L999')</literal></entry>
2527 <entry><literal>'DM 485</literal></entry>
2530 <entry><literal>to_char(485,'RN')</literal></entry>
2531 <entry><literal>' CDLXXXV'</literal></entry>
2534 <entry><literal>to_char(485,'FMRN')</literal></entry>
2535 <entry><literal>'CDLXXXV'</literal></entry>
2538 <entry><literal>to_char(5.2,'FMRN')</literal></entry>
2539 <entry><literal>V</literal></entry>
2542 <entry><literal>to_char(482,'999th')</literal></entry>
2543 <entry><literal>' 482nd'</literal></entry>
2546 <entry><literal>to_char(485, '"Good number:"999')</literal></entry>
2547 <entry><literal>'Good number: 485'</literal></entry>
2550 <entry><literal>to_char(485.8,'"Pre:"999" Post:" .999')</literal></entry>
2551 <entry><literal>'Pre: 485 Post: .800'</literal></entry>
2554 <entry><literal>to_char(12,'99V999')</literal></entry>
2555 <entry><literal>' 12000'</literal></entry>
2558 <entry><literal>to_char(12.4,'99V999')</literal></entry>
2559 <entry><literal>' 12400'</literal></entry>
2562 <entry><literal>to_char(12.45, '99V9')</literal></entry>
2563 <entry><literal>' 125'</literal></entry>
2572 <sect1 id="functions-datetime">
2573 <title>Date/Time Functions and Operators</title>
2576 <xref linkend="functions-datetime-table"> shows the available
2577 functions for date/time value processing.
2578 <xref linkend="operators-datetime-table"> illustrates the
2579 behaviors of the basic arithmetic
2580 operators (<literal>+</literal>, <literal>*</literal>, etc.).
2581 For formatting functions, refer to <xref
2582 linkend="functions-formatting">. You should be familiar with the
2583 background information on date/time data types (see <xref
2584 linkend="datatype-datetime">).
2588 The date/time operators described below behave similarly for types
2589 involving time zones as well as those without.
2591 <table id="operators-datetime-table">
2592 <title>Date/Time Operators</title>
2598 <entry>Example</entry>
2599 <entry>Result</entry>
2605 <entry> <literal>+</literal> </entry>
2606 <entry><type>timestamp</type> '2001-09-28 01:00' + <type>interval</type> '23 hours'</entry>
2607 <entry><type>timestamp</type> '2001-09-29 00:00'</entry>
2611 <entry> <literal>+</literal> </entry>
2612 <entry><type>date</type> '2001-09-28' + <type>interval</type> '1 hour'</entry>
2613 <entry><type>timestamp</type> '2001-09-28 01:00'</entry>
2617 <entry> <literal>+</literal> </entry>
2618 <entry><type>time</type> '01:00' + <type>interval</type> '3 hours'</entry>
2619 <entry><type>time</type> '04:00'</entry>
2623 <entry> <literal>-</literal> </entry>
2624 <entry><type>timestamp</type> '2001-09-28 23:00' - <type>interval</type> '23 hours'</entry>
2625 <entry><type>timestamp</type> '2001-09-28'</entry>
2629 <entry> <literal>-</literal> </entry>
2630 <entry><type>date</type> '2001-09-28' - <type>interval</type> '1 hour'</entry>
2631 <entry><type>timestamp</type> '2001-09-27 23:00'</entry>
2635 <entry> <literal>-</literal> </entry>
2636 <entry><type>time</type> '05:00' - <type>interval</type> '2 hours'</entry>
2637 <entry><type>time</type> '03:00'</entry>
2641 <entry> <literal>-</literal> </entry>
2642 <entry><type>interval</type> '2 hours' - <type>time</type> '05:00'</entry>
2643 <entry><type>time</type> '03:00:00'</entry>
2647 <entry> <literal>*</literal> </entry>
2648 <entry><type>interval</type> '1 hour' * <type>int</type> '3'</entry>
2649 <entry><type>interval</type> '03:00'</entry>
2653 <entry> <literal>/</literal> </entry>
2654 <entry><type>interval</type> '1 hour' / <type>int</type> '3'</entry>
2655 <entry><type>interval</type> '00:20'</entry>
2663 The date/time functions are summarized below, with additional
2664 details in subsequent sections.
2666 <table id="functions-datetime-table">
2667 <title>Date/Time Functions</title>
2672 <entry>Return Type</entry>
2673 <entry>Description</entry>
2674 <entry>Example</entry>
2675 <entry>Result</entry>
2681 <entry><function>age</function>(<type>timestamp</type>)</entry>
2682 <entry><type>interval</type></entry>
2683 <entry>Subtract from today</entry>
2684 <entry><literal>age(timestamp '1957-06-13')</literal></entry>
2685 <entry><literal>43 years 8 mons 3 days</literal></entry>
2689 <entry><function>age</function>(<type>timestamp</type>, <type>timestamp</type>)</entry>
2690 <entry><type>interval</type></entry>
2691 <entry>Subtract arguments</entry>
2692 <entry><literal>age('2001-04-10', timestamp '1957-06-13')</literal></entry>
2693 <entry><literal>43 years 9 mons 27 days</literal></entry>
2697 <entry><function>current_date</function></entry>
2698 <entry><type>date</type></entry>
2699 <entry>Today's date; see <link linkend="functions-datetime-current">below</link>
2706 <entry><function>current_time</function></entry>
2707 <entry><type>time with time zone</type></entry>
2708 <entry>Time of day; see <link linkend="functions-datetime-current">below</link>
2715 <entry><function>current_timestamp</function></entry>
2716 <entry><type>timestamp with time zone</type></entry>
2717 <entry>Date and time; see <link linkend="functions-datetime-current">below</link>
2724 <entry><function>date_part</function>(<type>text</type>, <type>timestamp</type>)</entry>
2725 <entry><type>double precision</type></entry>
2726 <entry>Get subfield (equivalent to
2727 <function>extract</function>); see also <link
2728 linkend="functions-datetime-datepart">below</link>
2730 <entry><literal>date_part('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
2731 <entry><literal>20</literal></entry>
2735 <entry><function>date_part</function>(<type>text</type>, <type>interval</type>)</entry>
2736 <entry><type>double precision</type></entry>
2737 <entry>Get subfield (equivalent to
2738 <function>extract</function>); see also <link
2739 linkend="functions-datetime-datepart">below</link>
2741 <entry><literal>date_part('month', interval '2 years 3 months')</literal></entry>
2742 <entry><literal>3</literal></entry>
2746 <entry><function>date_trunc</function>(<type>text</type>, <type>timestamp</type>)</entry>
2747 <entry><type>timestamp</type></entry>
2748 <entry>Truncate to specified precision; see also <link
2749 linkend="functions-datetime-trunc">below</link>
2751 <entry><literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
2752 <entry><literal>2001-02-16 20:00:00+00</literal></entry>
2756 <entry><function>extract</function>(<parameter>field</parameter> from
2757 <type>timestamp</type>)</entry>
2758 <entry><type>double precision</type></entry>
2759 <entry>Get subfield; see also <link
2760 linkend="functions-datetime-extract">below</link>
2762 <entry><literal>extract(hour from timestamp '2001-02-16 20:38:40')</literal></entry>
2763 <entry><literal>20</literal></entry>
2767 <entry><function>extract</function>(<parameter>field</parameter> from
2768 <type>interval</type>)</entry>
2769 <entry><type>double precision</type></entry>
2770 <entry>Get subfield; see also <link
2771 linkend="functions-datetime-extract">below</link>
2773 <entry><literal>extract(month from interval '2 years 3 months')</literal></entry>
2774 <entry><literal>3</literal></entry>
2778 <entry><function>isfinite</function>(<type>timestamp</type>)</entry>
2779 <entry><type>boolean</type></entry>
2780 <entry>Test for finite time stamp (neither invalid nor infinity)</entry>
2781 <entry><literal>isfinite(timestamp '2001-02-16 21:28:30')</literal></entry>
2782 <entry><literal>true</literal></entry>
2786 <entry><function>isfinite</function>(<type>interval</type>)</entry>
2787 <entry><type>boolean</type></entry>
2788 <entry>Test for finite interval</entry>
2789 <entry><literal>isfinite(interval '4 hours')</literal></entry>
2790 <entry><literal>true</literal></entry>
2794 <entry><function>localtime</function></entry>
2795 <entry><type>time</type></entry>
2796 <entry>Time of day; see <link linkend="functions-datetime-current">below</link>
2803 <entry><function>localtimestamp</function></entry>
2804 <entry><type>timestamp</type></entry>
2805 <entry>Date and time; see <link linkend="functions-datetime-current">below</link>
2812 <entry><function>now</function>()</entry>
2813 <entry><type>timestamp</type></entry>
2814 <entry>Current date and time (equivalent to
2815 <function>current_timestamp</function>); see <link
2816 linkend="functions-datetime-current">below</link>
2823 <entry><function>timeofday()</function></entry>
2824 <entry><type>text</type></entry>
2825 <entry>Current date and time; see <link
2826 linkend="functions-datetime-current">below</link>
2828 <entry><literal>timeofday()</literal></entry>
2829 <entry><literal>Wed Feb 21 17:01:13.000126 2001 EST</literal></entry>
2837 <sect2 id="functions-datetime-extract">
2838 <title><function>EXTRACT</function>, <function>date_part</function></title>
2841 EXTRACT (<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
2845 The <function>extract</function> function retrieves sub-fields
2846 from date/time values, such as year or hour.
2847 <replaceable>source</replaceable> is a value expression that
2848 evaluates to type <type>timestamp</type> or <type>interval</type>.
2849 (Expressions of type <type>date</type> or <type>time</type> will
2850 be cast to <type>timestamp</type> and can therefore be used as
2851 well.) <replaceable>field</replaceable> is an identifier or
2852 string that selects what field to extract from the source value.
2853 The <function>extract</function> function returns values of type
2854 <type>double precision</type>.
2855 The following are valid values:
2857 <!-- alphabetical -->
2860 <term><literal>century</literal></term>
2863 The year field divided by 100
2868 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
2869 <lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
2874 Note that the result for the century field is simply the year field
2875 divided by 100, and not the conventional definition which puts most
2876 years in the 1900's in the twentieth century.
2882 <term><literal>day</literal></term>
2885 The day (of the month) field (1 - 31)
2890 SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
2891 <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
2898 <term><literal>decade</literal></term>
2901 The year field divided by 10
2906 SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
2907 <lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>
2914 <term><literal>dow</literal></term>
2917 The day of the week (0 - 6; Sunday is 0) (for
2918 <type>timestamp</type> values only)
2923 SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
2924 <lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
2931 <term><literal>doy</literal></term>
2934 The day of the year (1 - 365/366) (for <type>timestamp</type> values only)
2938 SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
2939 <lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>
2946 <term><literal>epoch</literal></term>
2949 For <type>date</type> and <type>timestamp</type> values, the
2950 number of seconds since 1970-01-01 00:00:00-00 (Result may be
2951 negative.); for <type>interval</type> values, the total number
2952 of seconds in the interval
2957 SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
2958 <lineannotation>Result: </lineannotation><computeroutput>982352320</computeroutput>
2960 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
2961 <lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>
2968 <term><literal>hour</literal></term>
2971 The hour field (0 - 23)
2976 SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
2977 <lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
2984 <term><literal>microseconds</literal></term>
2987 The seconds field, including fractional parts, multiplied by 1
2988 000 000. Note that this includes full seconds.
2993 SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
2994 <lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>
3001 <term><literal>millennium</literal></term>
3004 The year field divided by 1000
3009 SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
3010 <lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
3015 Note that the result for the millennium field is simply the year field
3016 divided by 1000, and not the conventional definition which puts
3017 years in the 1900's in the second millennium.
3023 <term><literal>milliseconds</literal></term>
3026 The seconds field, including fractional parts, multiplied by
3027 1000. Note that this includes full seconds.
3032 SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
3033 <lineannotation>Result: </lineannotation><computeroutput>28500</computeroutput>
3040 <term><literal>minute</literal></term>
3043 The minutes field (0 - 59)
3048 SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
3049 <lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>
3056 <term><literal>month</literal></term>
3059 For <type>timestamp</type> values, the number of the month
3060 within the year (1 - 12) ; for <type>interval</type> values
3061 the number of months, modulo 12 (0 - 11)
3066 SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
3067 <lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
3069 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
3070 <lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
3072 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
3073 <lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
3080 <term><literal>quarter</literal></term>
3083 The quarter of the year (1 - 4) that the day is in (for
3084 <type>timestamp</type> values only)
3089 SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
3090 <lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
3097 <term><literal>second</literal></term>
3100 The seconds field, including fractional parts (0 -
3101 59<footnote><simpara>60 if leap seconds are
3102 implemented by the operating system</simpara></footnote>)
3107 SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
3108 <lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
3110 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
3111 <lineannotation>Result: </lineannotation><computeroutput>28.5</computeroutput>
3118 <term><literal>timezone</literal></term>
3121 The time zone offset. XXX But in what units?
3128 <term><literal>timezone_hour</literal></term>
3131 The hour component of the time zone offset.
3137 <term><literal>timezone_minute</literal></term>
3140 The minute component of the time zone offset.
3146 <term><literal>week</literal></term>
3149 From a <type>timestamp</type> value, calculate the number of
3150 the week of the year that the day is in. By definition
3151 (<acronym>ISO</acronym> 8601), the first week of a year
3152 contains January 4 of that year. (The <acronym>ISO</acronym>
3153 week starts on Monday.) In other words, the first Thursday of
3154 a year is in week 1 of that year.
3159 SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
3160 <lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
3167 <term><literal>year</literal></term>
3175 SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
3176 <lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>
3187 The <function>extract</function> function is primarily intended
3188 for computational processing. For formatting date/time values for
3189 display, see <xref linkend="functions-formatting">.
3192 <anchor id="functions-datetime-datepart">
3194 The <function>date_part</function> function is modeled on the traditional
3195 <productname>Ingres</productname> equivalent to the
3196 <acronym>SQL</acronym>-function <function>extract</function>:
3198 date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
3200 Note that here the <replaceable>field</replaceable> value needs to
3201 be a string. The valid field values for
3202 <function>date_part</function> are the same as for
3203 <function>extract</function>.
3208 SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
3209 <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
3211 SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
3212 <lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
3218 <sect2 id="functions-datetime-trunc">
3219 <title><function>date_trunc</function></title>
3222 The function <function>date_trunc</function> is conceptually
3223 similar to the <function>trunc</function> function for numbers.
3228 date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
3230 <replaceable>source</replaceable> is a value expression of type
3231 <type>timestamp</type> (values of type <type>date</type> and
3232 <type>time</type> are cast automatically).
3233 <replaceable>field</replaceable> selects to which precision to
3234 truncate the time stamp value. The return value is of type
3235 <type>timestamp</type> with all fields that are less than the
3236 selected one set to zero (or one, for day and month).
3240 Valid values for <replaceable>field</replaceable> are:
3242 <member>microseconds</member>
3243 <member>milliseconds</member>
3244 <member>second</member>
3245 <member>minute</member>
3246 <member>hour</member>
3247 <member>day</member>
3248 <member>month</member>
3249 <member>year</member>
3250 <member>decade</member>
3251 <member>century</member>
3252 <member>millennium</member>
3259 SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
3260 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00+00</computeroutput>
3262 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
3263 <lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00+00</computeroutput>
3269 <sect2 id="functions-datetime-current">
3270 <title>Current Date/Time</title>
3273 <primary>date</primary>
3274 <secondary>current</secondary>
3278 <primary>time</primary>
3279 <secondary>current</secondary>
3283 The following functions are available to obtain the current date and/or
3289 CURRENT_TIME ( <replaceable>precision</replaceable> )
3290 CURRENT_TIMESTAMP ( <replaceable>precision</replaceable> )
3293 LOCALTIME ( <replaceable>precision</replaceable> )
3294 LOCALTIMESTAMP ( <replaceable>precision</replaceable> )
3296 <function>CURRENT_TIME</function>,
3297 <function>CURRENT_TIMESTAMP</function>,
3298 <function>LOCALTIME</function>, and
3299 <function>LOCALTIMESTAMP</function>
3300 can optionally be given
3301 a precision parameter, which causes the result to be rounded
3302 to that many fractional digits. Without a precision parameter,
3303 the result is given to the full available precision.
3308 Prior to <productname>PostgreSQL</productname> 7.2, the precision
3309 parameters were unimplemented, and the result was always given
3316 SELECT CURRENT_TIME;
3317 <computeroutput>14:39:53.662522-05</computeroutput>
3319 SELECT CURRENT_DATE;
3320 <computeroutput>2001-12-23</computeroutput>
3322 SELECT CURRENT_TIMESTAMP;
3323 <computeroutput>2001-12-23 14:39:53.662522-05</computeroutput>
3325 SELECT CURRENT_TIMESTAMP(2);
3326 <computeroutput>2001-12-23 14:39:53.66-05</computeroutput>
3328 SELECT LOCALTIMESTAMP;
3329 <computeroutput>2001-12-23 14:39:53.662522</computeroutput>
3334 The function <function>now()</function> is the traditional
3335 <productname>PostgreSQL</productname> equivalent to
3336 <function>CURRENT_TIMESTAMP</function>.
3340 There is also <function>timeofday()</function>, which for historical
3341 reasons returns a text string rather than a <type>timestamp</type> value:
3347 Sat Feb 17 19:07:32.000126 2001 EST
3352 It is quite important to realize that
3353 <function>CURRENT_TIMESTAMP</function> and related functions all return
3354 the time as of the start of the current transaction; their values do not
3355 increment while a transaction is running. But
3356 <function>timeofday()</function> returns the actual current time.
3360 All the date/time data types also accept the special literal value
3361 <literal>now</literal> to specify the current date and time. Thus,
3362 the following three all return the same result:
3364 SELECT CURRENT_TIMESTAMP;
3366 SELECT TIMESTAMP 'now';
3370 You do not want to use the third form when specifying a DEFAULT
3371 value while creating a table. The system will convert <literal>now</literal>
3372 to a <type>timestamp</type> as soon as the constant is parsed, so that when
3373 the default value is needed,
3374 the time of the table creation would be used! The first two
3375 forms will not be evaluated until the default value is used,
3376 because they are function calls. Thus they will give the desired
3377 behavior of defaulting to the time of row insertion.
3385 <sect1 id="functions-geometry">
3386 <title>Geometric Functions and Operators</title>
3389 The geometric types <type>point</type>, <type>box</type>,
3390 <type>lseg</type>, <type>line</type>, <type>path</type>,
3391 <type>polygon</type>, and <type>circle</type> have a large set of
3392 native support functions and operators.
3396 <title>Geometric Operators</title>
3400 <entry>Operator</entry>
3401 <entry>Description</entry>
3402 <entry>Usage</entry>
3408 <entry>Translation</entry>
3409 <entry><literal>box '((0,0),(1,1))' + point '(2.0,0)'</literal></entry>
3413 <entry>Translation</entry>
3414 <entry><literal>box '((0,0),(1,1))' - point '(2.0,0)'</literal></entry>
3418 <entry>Scaling/rotation</entry>
3419 <entry><literal>box '((0,0),(1,1))' * point '(2.0,0)'</literal></entry>
3423 <entry>Scaling/rotation</entry>
3424 <entry><literal>box '((0,0),(2,2))' / point '(2.0,0)'</literal></entry>
3428 <entry>Intersection</entry>
3429 <entry><literal>'((1,-1),(-1,1))' # '((1,1),(-1,-1))'</literal></entry>
3433 <entry>Number of points in polygon</entry>
3434 <entry><literal># '((1,0),(0,1),(-1,0))'</literal></entry>
3438 <entry>Point of closest proximity</entry>
3439 <entry><literal>point '(0,0)' ## lseg '((2,0),(0,2))'</literal></entry>
3442 <entry> && </entry>
3443 <entry>Overlaps?</entry>
3444 <entry><literal>box '((0,0),(1,1))' && box '((0,0),(2,2))'</literal></entry>
3447 <entry> &< </entry>
3448 <entry>Overlaps to left?</entry>
3449 <entry><literal>box '((0,0),(1,1))' &< box '((0,0),(2,2))'</literal></entry>
3452 <entry> &> </entry>
3453 <entry>Overlaps to right?</entry>
3454 <entry><literal>box '((0,0),(3,3))' &> box '((0,0),(2,2))'</literal></entry>
3457 <entry> <-> </entry>
3458 <entry>Distance between</entry>
3459 <entry><literal>circle '((0,0),1)' <-> circle '((5,0),1)'</literal></entry>
3462 <entry> << </entry>
3463 <entry>Left of?</entry>
3464 <entry><literal>circle '((0,0),1)' << circle '((5,0),1)'</literal></entry>
3467 <entry> <^ </entry>
3468 <entry>Is below?</entry>
3469 <entry><literal>circle '((0,0),1)' <^ circle '((0,5),1)'</literal></entry>
3472 <entry> >> </entry>
3473 <entry>Is right of?</entry>
3474 <entry><literal>circle '((5,0),1)' >> circle '((0,0),1)'</literal></entry>
3477 <entry> >^ </entry>
3478 <entry>Is above?</entry>
3479 <entry><literal>circle '((0,5),1)' >^ circle '((0,0),1)'</literal></entry>
3483 <entry>Intersects or overlaps</entry>
3484 <entry><literal>lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'</literal></entry>
3488 <entry>Is horizontal?</entry>
3489 <entry><literal>point '(1,0)' ?- point '(0,0)'</literal></entry>
3492 <entry> ?-| </entry>
3493 <entry>Is perpendicular?</entry>
3494 <entry><literal>lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'</literal></entry>
3497 <entry> @-@ </entry>
3498 <entry>Length or circumference</entry>
3499 <entry><literal>@-@ path '((0,0),(1,0))'</literal></entry>
3503 <entry>Is vertical?</entry>
3504 <entry><literal>point '(0,1)' ?| point '(0,0)'</literal></entry>
3507 <entry> ?|| </entry>
3508 <entry>Is parallel?</entry>
3509 <entry><literal>lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'</literal></entry>
3513 <entry>Contained or on</entry>
3514 <entry><literal>point '(1,1)' @ circle '((0,0),2)'</literal></entry>
3518 <entry>Center of</entry>
3519 <entry><literal>@@ circle '((0,0),10)'</literal></entry>
3523 <entry>Same as</entry>
3524 <entry><literal>polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'</literal></entry>
3531 <title>Geometric Functions</title>
3535 <entry>Function</entry>
3536 <entry>Returns</entry>
3537 <entry>Description</entry>
3538 <entry>Example</entry>
3543 <entry><function>area</function>(object)</entry>
3544 <entry><type>double precision</type></entry>
3545 <entry>area of item</entry>
3546 <entry><literal>area(box '((0,0),(1,1))')</literal></entry>
3549 <entry><function>box</function>(box, box)</entry>
3550 <entry><type>box</type></entry>
3551 <entry>intersection box</entry>
3552 <entry><literal>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</literal></entry>
3555 <entry><function>center</function>(object)</entry>
3556 <entry><type>point</type></entry>
3557 <entry>center of item</entry>
3558 <entry><literal>center(box '((0,0),(1,2))')</literal></entry>
3561 <entry><function>diameter</function>(circle)</entry>
3562 <entry><type>double precision</type></entry>
3563 <entry>diameter of circle</entry>
3564 <entry><literal>diameter(circle '((0,0),2.0)')</literal></entry>
3567 <entry><function>height</function>(box)</entry>
3568 <entry><type>double precision</type></entry>
3569 <entry>vertical size of box</entry>
3570 <entry><literal>height(box '((0,0),(1,1))')</literal></entry>
3573 <entry><function>isclosed</function>(path)</entry>
3574 <entry><type>boolean</type></entry>
3575 <entry>a closed path?</entry>
3576 <entry><literal>isclosed(path '((0,0),(1,1),(2,0))')</literal></entry>
3579 <entry><function>isopen</function>(path)</entry>
3580 <entry><type>boolean</type></entry>
3581 <entry>an open path?</entry>
3582 <entry><literal>isopen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3585 <entry><function>length</function>(object)</entry>
3586 <entry><type>double precision</type></entry>
3587 <entry>length of item</entry>
3588 <entry><literal>length(path '((-1,0),(1,0))')</literal></entry>
3591 <entry><function>pclose</function>(path)</entry>
3592 <entry><type>path</type></entry>
3593 <entry>convert path to closed</entry>
3594 <entry><literal>popen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3597 <!-- Not defined by this name. Implements the intersection operator '#' -->
3599 <entry><function>point</function>(lseg,lseg)</entry>
3600 <entry><type>point</type></entry>
3601 <entry>intersection</entry>
3602 <entry><literal>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</literal></entry>
3606 <entry><function>npoint</function>(path)</entry>
3607 <entry><type>integer</type></entry>
3608 <entry>number of points</entry>
3609 <entry><literal>npoints(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3612 <entry><function>popen</function>(path)</entry>
3613 <entry><type>path</type></entry>
3614 <entry>convert path to open path</entry>
3615 <entry><literal>popen(path '((0,0),(1,1),(2,0))')</literal></entry>
3618 <entry><function>radius</function>(circle)</entry>
3619 <entry><type>double precision</type></entry>
3620 <entry>radius of circle</entry>
3621 <entry><literal>radius(circle '((0,0),2.0)')</literal></entry>
3624 <entry><function>width</function>(box)</entry>
3625 <entry><type>double precision</type></entry>
3626 <entry>horizontal size</entry>
3627 <entry><literal>width(box '((0,0),(1,1))')</literal></entry>
3635 <title>Geometric Type Conversion Functions</title>
3639 <entry>Function</entry>
3640 <entry>Returns</entry>
3641 <entry>Description</entry>
3642 <entry>Example</entry>
3647 <entry><function>box</function>(<type>circle</type>)</entry>
3648 <entry><type>box</type></entry>
3649 <entry>circle to box</entry>
3650 <entry><literal>box(circle '((0,0),2.0)')</literal></entry>
3653 <entry><function>box</function>(<type>point</type>, <type>point</type>)</entry>
3654 <entry><type>box</type></entry>
3655 <entry>points to box</entry>
3656 <entry><literal>box(point '(0,0)', point '(1,1)')</literal></entry>
3659 <entry><function>box</function>(<type>polygon</type>)</entry>
3660 <entry><type>box</type></entry>
3661 <entry>polygon to box</entry>
3662 <entry><literal>box(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3665 <entry><function>circle</function>(<type>box</type>)</entry>
3666 <entry><type>circle</type></entry>
3667 <entry>to circle</entry>
3668 <entry><literal>circle(box '((0,0),(1,1))')</literal></entry>
3671 <entry><function>circle</function>(<type>point</type>, <type>double precision</type>)</entry>
3672 <entry><type>circle</type></entry>
3673 <entry>point to circle</entry>
3674 <entry><literal>circle(point '(0,0)', 2.0)</literal></entry>
3677 <entry><function>lseg</function>(<type>box</type>)</entry>
3678 <entry><type>lseg</type></entry>
3679 <entry>box diagonal to <type>lseg</type></entry>
3680 <entry><literal>lseg(box '((-1,0),(1,0))')</literal></entry>
3683 <entry><function>lseg</function>(<type>point</type>, <type>point</type>)</entry>
3684 <entry><type>lseg</type></entry>
3685 <entry>points to <type>lseg</type></entry>
3686 <entry><literal>lseg(point '(-1,0)', point '(1,0)')</literal></entry>
3689 <entry><function>path</function>(<type>polygon</type>)</entry>
3690 <entry><type>point</type></entry>
3691 <entry>polygon to path</entry>
3692 <entry><literal>path(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3695 <entry><function>point</function>(<type>circle</type>)</entry>
3696 <entry><type>point</type></entry>
3697 <entry>center</entry>
3698 <entry><literal>point(circle '((0,0),2.0)')</literal></entry>
3701 <entry><function>point</function>(<type>lseg</type>, <type>lseg</type>)</entry>
3702 <entry><type>point</type></entry>
3703 <entry>intersection</entry>
3704 <entry><literal>point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')</literal></entry>
3707 <entry><function>point</function>(<type>polygon</type>)</entry>
3708 <entry><type>point</type></entry>
3709 <entry>center</entry>
3710 <entry><literal>point(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3713 <entry><function>polygon</function>(<type>box</type>)</entry>
3714 <entry><type>polygon</type></entry>
3715 <entry>12 point polygon</entry>
3716 <entry><literal>polygon(box '((0,0),(1,1))')</literal></entry>
3719 <entry><function>polygon</function>(<type>circle</type>)</entry>
3720 <entry><type>polygon</type></entry>
3721 <entry>12-point polygon</entry>
3722 <entry><literal>polygon(circle '((0,0),2.0)')</literal></entry>
3725 <entry><function>polygon</function>(<replaceable class="parameter">npts</replaceable>, <type>circle</type>)</entry>
3726 <entry><type>polygon</type></entry>
3727 <entry><replaceable class="parameter">npts</replaceable> polygon</entry>
3728 <entry><literal>polygon(12, circle '((0,0),2.0)')</literal></entry>
3731 <entry><function>polygon</function>(<type>path</type>)</entry>
3732 <entry><type>polygon</type></entry>
3733 <entry>path to polygon</entry>
3734 <entry><literal>polygon(path '((0,0),(1,1),(2,0))')</literal></entry>
3743 <sect1 id="functions-net">
3744 <title>Network Address Type Functions</title>
3747 <table tocentry="1" id="cidr-inet-operators-table">
3748 <title><type>cidr</type> and <type>inet</type> Operators</title>
3752 <entry>Operator</entry>
3753 <entry>Description</entry>
3754 <entry>Usage</entry>
3759 <entry> < </entry>
3760 <entry>Less than</entry>
3761 <entry><literal>inet '192.168.1.5' < inet '192.168.1.6'</literal></entry>
3764 <entry> <= </entry>
3765 <entry>Less than or equal</entry>
3766 <entry><literal>inet '192.168.1.5' <= inet '192.168.1.5'</literal></entry>
3770 <entry>Equals</entry>
3771 <entry><literal>inet '192.168.1.5' = inet '192.168.1.5'</literal></entry>
3774 <entry> >= </entry>
3775 <entry>Greater or equal</entry>
3776 <entry><literal>inet '192.168.1.5' >= inet '192.168.1.5'</literal></entry>
3779 <entry> > </entry>
3780 <entry>Greater</entry>
3781 <entry><literal>inet '192.168.1.5' > inet '192.168.1.4'</literal></entry>
3784 <entry> <> </entry>
3785 <entry>Not equal</entry>
3786 <entry><literal>inet '192.168.1.5' <> inet '192.168.1.4'</literal></entry>
3789 <entry> << </entry>
3790 <entry>is contained within</entry>
3791 <entry><literal>inet '192.168.1.5' << inet '192.168.1/24'</literal></entry>
3794 <entry> <<= </entry>
3795 <entry>is contained within or equals</entry>
3796 <entry><literal>inet '192.168.1/24' <<= inet '192.168.1/24'</literal></entry>
3799 <entry> >> </entry>
3800 <entry>contains</entry>
3801 <entry><literal>inet'192.168.1/24' >> inet '192.168.1.5'</literal></entry>
3804 <entry> >>= </entry>
3805 <entry>contains or equals</entry>
3806 <entry><literal>inet '192.168.1/24' >>= inet '192.168.1/24'</literal></entry>
3813 All of the operators for <type>inet</type> can be applied to
3814 <type>cidr</type> values as well. The operators
3815 <literal><<</literal>, <literal><<=</literal>,
3816 <literal>>></literal>, <literal>>>=</literal>
3817 test for subnet inclusion: they consider only the network parts
3818 of the two addresses, ignoring any host part, and determine whether
3819 one network part is identical to or a subnet of the other.
3823 <table tocentry="1" id="cidr-inet-functions">
3824 <title><type>cidr</type> and <type>inet</type> Functions</title>
3828 <entry>Function</entry>
3829 <entry>Returns</entry>
3830 <entry>Description</entry>
3831 <entry>Example</entry>
3832 <entry>Result</entry>
3837 <entry><function>broadcast</function>(<type>inet</type>)</entry>
3838 <entry><type>inet</type></entry>
3839 <entry>broadcast address for network</entry>
3840 <entry><literal>broadcast('192.168.1.5/24')</literal></entry>
3841 <entry><literal>192.168.1.255/24</literal></entry>
3844 <entry><function>host</function>(<type>inet</type>)</entry>
3845 <entry><type>text</type></entry>
3846 <entry>extract IP address as text</entry>
3847 <entry><literal>host('192.168.1.5/24')</literal></entry>
3848 <entry><literal>192.168.1.5</literal></entry>
3851 <entry><function>masklen</function>(<type>inet</type>)</entry>
3852 <entry><type>integer</type></entry>
3853 <entry>extract netmask length</entry>
3854 <entry><literal>masklen('192.168.1.5/24')</literal></entry>
3855 <entry><literal>24</literal></entry>
3858 <entry><function>set_masklen</function>(<type>inet</type>,<type>integer</type>)</entry>
3859 <entry><type>inet</type></entry>
3860 <entry>set netmask length for <type>inet</type> value</entry>
3861 <entry><literal>set_masklen('192.168.1.5/24',16)</literal></entry>
3862 <entry><literal>192.168.1.5/16</literal></entry>
3865 <entry><function>netmask</function>(<type>inet</type>)</entry>
3866 <entry><type>inet</type></entry>
3867 <entry>construct netmask for network</entry>
3868 <entry><literal>netmask('192.168.1.5/24')</literal></entry>
3869 <entry><literal>255.255.255.0</literal></entry>
3872 <entry><function>network</function>(<type>inet</type>)</entry>
3873 <entry><type>cidr</type></entry>
3874 <entry>extract network part of address</entry>
3875 <entry><literal>network('192.168.1.5/24')</literal></entry>
3876 <entry><literal>192.168.1.0/24</literal></entry>
3879 <entry><function>text</function>(<type>inet</type>)</entry>
3880 <entry><type>text</type></entry>
3881 <entry>extract IP address and masklen as text</entry>
3882 <entry><literal>text(inet '192.168.1.5')</literal></entry>
3883 <entry><literal>192.168.1.5/32</literal></entry>
3886 <entry><function>abbrev</function>(<type>inet</type>)</entry>
3887 <entry><type>text</type></entry>
3888 <entry>extract abbreviated display as text</entry>
3889 <entry><literal>abbrev(cidr '10.1.0.0/16')</literal></entry>
3890 <entry><literal>10.1/16</literal></entry>
3897 All of the functions for <type>inet</type> can be applied to
3898 <type>cidr</type> values as well. The <function>host</function>(),
3899 <function>text</function>(), and <function>abbrev</function>() functions are primarily
3900 intended to offer alternative display formats. You can cast a text
3901 field to inet using normal casting syntax: <literal>inet(expression)</literal> or
3902 <literal>colname::inet</literal>.
3905 <table tocentry="1" id="macaddr-functions">
3906 <title><type>macaddr</type> Functions</title>
3910 <entry>Function</entry>
3911 <entry>Returns</entry>
3912 <entry>Description</entry>
3913 <entry>Example</entry>
3914 <entry>Result</entry>
3919 <entry><function>trunc</function>(<type>macaddr</type>)</entry>
3920 <entry><type>macaddr</type></entry>
3921 <entry>set last 3 bytes to zero</entry>
3922 <entry><literal>trunc(macaddr '12:34:56:78:90:ab')</literal></entry>
3923 <entry><literal>12:34:56:00:00:00</literal></entry>
3930 The function <function>trunc</function>(<type>macaddr</type>) returns a MAC
3931 address with the last 3 bytes set to 0. This can be used to
3932 associate the remaining prefix with a manufacturer. The directory
3933 <filename>contrib/mac</filename> in the source distribution contains some
3934 utilities to create and maintain such an association table.
3938 The <type>macaddr</type> type also supports the standard relational
3939 operators (<literal>></literal>, <literal><=</literal>, etc.) for
3940 lexicographical ordering.
3946 <sect1 id="functions-sequence">
3947 <title>Sequence-Manipulation Functions</title>
3950 <primary>sequences</primary>
3953 <primary>nextval</primary>
3956 <primary>currval</primary>
3959 <primary>setval</primary>
3963 <title>Sequence Functions</title>
3966 <row><entry>Function</entry> <entry>Returns</entry> <entry>Description</entry></row>
3971 <entry><function>nextval</function>(<type>text</type>)</entry>
3972 <entry><type>bigint</type></entry>
3973 <entry>Advance sequence and return new value</entry>
3976 <entry><function>currval</function>(<type>text</type>)</entry>
3977 <entry><type>bigint</type></entry>
3978 <entry>Return value most recently obtained with <function>nextval</function></entry>
3981 <entry><function>setval</function>(<type>text</type>,<type>bigint</type>)</entry>
3982 <entry><type>bigint</type></entry>
3983 <entry>Set sequence's current value</entry>
3986 <entry><function>setval</function>(<type>text</type>,<type>bigint</type>,<type>boolean</type>)</entry>
3987 <entry><type>bigint</type></entry>
3988 <entry>Set sequence's current value and <literal>is_called</literal> flag</entry>
3995 This section describes <productname>PostgreSQL</productname>'s functions
3996 for operating on <firstterm>sequence objects</firstterm>.
3997 Sequence objects (also called sequence generators or
3998 just sequences) are special single-row tables created with
3999 <command>CREATE SEQUENCE</command>. A sequence object is usually used to
4000 generate unique identifiers for rows of a table. The sequence functions
4001 provide simple, multiuser-safe methods for obtaining successive
4002 sequence values from sequence objects.
4006 For largely historical reasons, the sequence to be operated on by
4007 a sequence-function call is specified by a text-string argument.
4008 To achieve some compatibility with the handling of ordinary SQL
4009 names, the sequence functions convert their argument to lower case
4010 unless the string is double-quoted. Thus
4012 nextval('foo') <lineannotation>operates on sequence </><literal>foo</literal>
4013 nextval('FOO') <lineannotation>operates on sequence </><literal>foo</literal>
4014 nextval('"Foo"') <lineannotation>operates on sequence </><literal>Foo</literal>
4016 The sequence name can be schema-qualified if necessary:
4018 nextval('myschema.foo') <lineannotation>operates on </><literal>myschema.foo</literal>
4019 nextval('"myschema".foo') <lineannotation>same as above</lineannotation>
4020 nextval('foo') <lineannotation>searches search path for
4021 </><literal>foo</literal>
4023 Of course, the text argument can be the result of an expression,
4024 not only a simple literal, which is occasionally useful.
4028 The available sequence functions are:
4032 <term><function>nextval</function></term>
4035 Advance the sequence object to its next value and return that
4036 value. This is done atomically: even if multiple server processes
4037 execute <function>nextval</function> concurrently, each will safely receive
4038 a distinct sequence value.
4044 <term><function>currval</function></term>
4047 Return the value most recently obtained by <function>nextval</function>
4048 for this sequence in the current server process. (An error is
4049 reported if <function>nextval</function> has never been called for this
4050 sequence in this process.) Notice that because this is returning
4051 a process-local value, it gives a predictable answer even if other
4052 server processes are executing <function>nextval</function> meanwhile.
4058 <term><function>setval</function></term>
4061 Reset the sequence object's counter value. The two-parameter
4062 form sets the sequence's <literal>last_value</literal> field to the specified
4063 value and sets its <literal>is_called</literal> field to <literal>true</literal>,
4064 meaning that the next <function>nextval</function> will advance the sequence
4065 before returning a value. In the three-parameter form,
4066 <literal>is_called</literal> may be set either <literal>true</literal> or
4067 <literal>false</literal>. If it's set to <literal>false</literal>,
4068 the next <function>nextval</function> will return exactly the specified
4069 value, and sequence advancement commences with the following
4070 <function>nextval</function>. For example,
4075 SELECT setval('foo', 42); <lineannotation>Next nextval() will return 43</lineannotation>
4076 SELECT setval('foo', 42, true); <lineannotation>Same as above</lineannotation>
4077 SELECT setval('foo', 42, false); <lineannotation>Next nextval() will return 42</lineannotation>
4082 The result returned by <function>setval</function> is just the value of its
4092 To avoid blocking of concurrent transactions that obtain numbers from the
4093 same sequence, a <function>nextval</function> operation is never rolled back;
4094 that is, once a value has been fetched it is considered used, even if the
4095 transaction that did the <function>nextval</function> later aborts. This means
4096 that aborted transactions may leave unused <quote>holes</quote> in the
4097 sequence of assigned values. <function>setval</function> operations are never
4098 rolled back, either.
4103 If a sequence object has been created with default parameters,
4104 <function>nextval()</function> calls on it will return successive values
4105 beginning with one. Other behaviors can be obtained by using
4106 special parameters in the <command>CREATE SEQUENCE</command> command;
4107 see its command reference page for more information.
4113 <sect1 id="functions-conditional">
4114 <title>Conditional Expressions</title>
4117 <primary>case</primary>
4121 <primary>conditionals</primary>
4125 This section describes the <acronym>SQL</acronym>-compliant conditional expressions
4126 available in <productname>PostgreSQL</productname>.
4131 If your needs go beyond the capabilities of these conditional
4132 expressions you might want to consider writing a stored procedure
4133 in a more expressive programming language.
4137 <bridgehead renderas="sect2">CASE</bridgehead>
4140 CASE WHEN <replaceable>condition</replaceable> THEN <replaceable>result</replaceable>
4141 <optional>WHEN ...</optional>
4142 <optional>ELSE <replaceable>result</replaceable></optional>
4147 The <acronym>SQL</acronym> <token>CASE</token> expression is a
4148 generic conditional expression, similar to if/else statements in
4149 other languages. <token>CASE</token> clauses can be used wherever
4150 an expression is valid. <replaceable>condition</replaceable> is an
4151 expression that returns a <type>boolean</type> result. If the result is true
4152 then the value of the <token>CASE</token> expression is
4153 <replaceable>result</replaceable>. If the result is false any
4154 subsequent <token>WHEN</token> clauses are searched in the same
4155 manner. If no <token>WHEN</token>
4156 <replaceable>condition</replaceable> is true then the value of the
4157 case expression is the <replaceable>result</replaceable> in the
4158 <token>ELSE</token> clause. If the <token>ELSE</token> clause is
4159 omitted and no condition matches, the result is NULL.
4166 <prompt>=></prompt> <userinput>SELECT * FROM test;</userinput>
4175 <prompt>=></prompt> <userinput>SELECT a,
4176 CASE WHEN a=1 THEN 'one'
4180 FROM test;</userinput>
4193 The data types of all the <replaceable>result</replaceable>
4194 expressions must be coercible to a single output type.
4195 See <xref linkend="typeconv-union-case"> for more detail.
4199 CASE <replaceable>expression</replaceable>
4200 WHEN <replaceable>value</replaceable> THEN <replaceable>result</replaceable>
4201 <optional>WHEN ...</optional>
4202 <optional>ELSE <replaceable>result</replaceable></optional>
4207 This <quote>simple</quote> <token>CASE</token> expression is a
4208 specialized variant of the general form above. The
4209 <replaceable>expression</replaceable> is computed and compared to
4210 all the <replaceable>value</replaceable>s in the
4211 <token>WHEN</token> clauses until one is found that is equal. If
4212 no match is found, the <replaceable>result</replaceable> in the
4213 <token>ELSE</token> clause (or NULL) is returned. This is similar
4214 to the <function>switch</function> statement in C.
4219 The example above can be written using the simple
4220 <token>CASE</token> syntax:
4222 <prompt>=></prompt> <userinput>SELECT a,
4223 CASE a WHEN 1 THEN 'one'
4227 FROM test;</userinput>
4239 <bridgehead renderas="sect2">COALESCE</bridgehead>
4242 <function>COALESCE</function>(<replaceable>value</replaceable><optional
4247 The <function>COALESCE</function> function returns the first of its
4248 arguments that is not NULL. This is often useful to substitute a
4249 default value for NULL values when data is retrieved for display,
4252 SELECT COALESCE(description, short_description, '(none)') ...
4256 <bridgehead renderas="sect2">NULLIF</bridgehead>
4259 <primary>nullif</primary>
4263 <function>NULLIF</function>(<replaceable>value1</replaceable>,
4264 <replaceable>value2</replaceable>)
4268 The <function>NULLIF</function> function returns NULL if and only
4269 if <replaceable>value1</replaceable> and
4270 <replaceable>value2</replaceable> are equal. Otherwise it returns
4271 <replaceable>value1</replaceable>. This can be used to perform the
4272 inverse operation of the <function>COALESCE</function> example
4275 SELECT NULLIF(value, '(none)') ...
4281 <function>COALESCE</function> and <function>NULLIF</function> are
4282 just shorthand for <token>CASE</token> expressions. They are actually
4283 converted into <token>CASE</token> expressions at a very early stage
4284 of processing, and subsequent processing thinks it is dealing with
4285 <token>CASE</token>. Thus an incorrect <function>COALESCE</function> or
4286 <function>NULLIF</function> usage may draw an error message that
4287 refers to <token>CASE</token>.
4294 <sect1 id="functions-misc">
4295 <title>Miscellaneous Functions</title>
4298 <title>Session Information Functions</title>
4301 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4306 <entry><function>current_user</function></entry>
4307 <entry><type>name</type></entry>
4308 <entry>user name of current execution context</entry>
4311 <entry><function>session_user</function></entry>
4312 <entry><type>name</type></entry>
4313 <entry>session user name</entry>
4316 <entry><function>user</function></entry>
4317 <entry><type>name</type></entry>
4318 <entry>equivalent to <function>current_user</function></entry>
4321 <entry><function>current_schema()</function></entry>
4322 <entry><type>name</type></entry>
4323 <entry>name of current schema</entry>
4326 <entry><function>current_schemas()</function></entry>
4327 <entry><type>name[]</type></entry>
4328 <entry>names of schemas in search path</entry>
4334 <indexterm zone="functions-misc">
4335 <primary>user</primary>
4336 <secondary>current</secondary>
4339 <indexterm zone="functions-misc">
4340 <primary>schema</primary>
4341 <secondary>current</secondary>
4344 <indexterm zone="functions-misc">
4345 <primary>search path</primary>
4346 <secondary>current</secondary>
4350 The <function>session_user</function> is the user that initiated a
4351 database connection; it is fixed for the duration of that
4352 connection. The <function>current_user</function> is the user identifier
4353 that is applicable for permission checking. Normally, it is equal
4354 to the session user, but it changes during the execution of
4355 functions with the attribute <literal>SECURITY DEFINER</literal>.
4356 In Unix parlance, the session user is the <quote>real user</quote> and
4357 the current user is the <quote>effective user</quote>.
4362 <function>current_user</function>, <function>session_user</function>, and
4363 <function>user</function> have special syntactic status in <acronym>SQL</acronym>:
4364 they must be called without trailing parentheses.
4369 <title>Deprecated</title>
4371 The function <function>getpgusername()</function> is an obsolete equivalent
4372 of <function>current_user</function>.
4377 <function>current_schema</function> returns the name of the schema that is
4378 at the front of the search path (or NULL if the search path is
4379 empty). This is the schema that will be used for any tables or
4380 other named objects that are created without specifying a target schema.
4381 <function>current_schemas</function> returns an array of the names of all
4382 schemas presently in the search path. Note that these functions show
4383 only schemas that are explicitly part of the path; when a system schema
4384 is being searched implicitly, it is not listed.
4388 <title>System Information Functions</title>
4391 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4396 <entry><function>version</function></entry>
4397 <entry><type>text</type></entry>
4398 <entry>PostgreSQL version information</entry>
4404 <indexterm zone="functions-misc">
4405 <primary>version</primary>
4409 <function>version()</function> returns a string describing the PostgreSQL
4414 <title>Access Privilege Inquiry Functions</title>
4417 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4422 <entry><function>has_table_privilege</function>(<parameter>user</parameter>,
4423 <parameter>table</parameter>,
4424 <parameter>access</parameter>)
4426 <entry><type>boolean</type></entry>
4427 <entry>does user have access to table</entry>
4430 <entry><function>has_table_privilege</function>(<parameter>table</parameter>,
4431 <parameter>access</parameter>)
4433 <entry><type>boolean</type></entry>
4434 <entry>does current user have access to table</entry>
4440 <indexterm zone="functions-misc">
4441 <primary>has_table_privilege</primary>
4445 <function>has_table_privilege</function> determines whether a user
4446 can access a table in a particular way. The user can be
4447 specified by name or by ID
4448 (<classname>pg_user</classname>.<structfield>usesysid</structfield>), or if the argument is
4450 <function>current_user</function> is assumed. The table can be specified
4451 by name or by OID. (Thus, there are actually six variants of
4452 <function>has_table_privilege</function>, which can be distinguished by
4453 the number and types of their arguments.) When specifying by name,
4454 the name can be schema-qualified if necessary.
4455 The desired access type
4456 is specified by a text string, which must evaluate to one of the
4457 values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>,
4458 <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, or
4459 <literal>TRIGGER</literal>. (Case of the string is not significant, however.)
4462 SELECT has_table_privilege('myschema.mytable', 'select');
4467 <title>Catalog Information Functions</title>
4470 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4475 <entry><function>pg_get_viewdef</function>(<parameter>viewname</parameter>)</entry>
4476 <entry><type>text</type></entry>
4477 <entry>Get CREATE VIEW command for view</entry>
4480 <entry><function>pg_get_viewdef</function>(<parameter>viewOID</parameter>)</entry>
4481 <entry><type>text</type></entry>
4482 <entry>Get CREATE VIEW command for view</entry>
4485 <entry><function>pg_get_ruledef</function>(<parameter>ruleOID</parameter>)</entry>
4486 <entry><type>text</type></entry>
4487 <entry>Get CREATE RULE command for rule</entry>
4490 <entry><function>pg_get_indexdef</function>(<parameter>indexOID</parameter>)</entry>
4491 <entry><type>text</type></entry>
4492 <entry>Get CREATE INDEX command for index</entry>
4495 <entry><function>pg_get_userbyid</function>(<parameter>userid</parameter>)</entry>
4496 <entry><type>name</type></entry>
4497 <entry>Get user name given ID</entry>
4503 <indexterm zone="functions-misc">
4504 <primary>pg_get_viewdef</primary>
4507 <indexterm zone="functions-misc">
4508 <primary>pg_get_ruledef</primary>
4511 <indexterm zone="functions-misc">
4512 <primary>pg_get_indexdef</primary>
4515 <indexterm zone="functions-misc">
4516 <primary>pg_get_userbyid</primary>
4520 These functions extract information from the system catalogs.
4521 <function>pg_get_viewdef()</function>, <function>pg_get_ruledef()</function>, and
4522 <function>pg_get_indexdef()</function> respectively reconstruct the creating
4523 command for a view, rule, or index. (Note that this is a decompiled
4524 reconstruction, not the verbatim text of the command.)
4525 <function>pg_get_userbyid()</function> extracts a user's name given a
4526 <structfield>usesysid</structfield> value.
4530 <title>Comment Information Functions</title>
4533 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4538 <entry><function>obj_description</function>(<parameter>objectOID</parameter>, <parameter>tablename</parameter>)</entry>
4539 <entry><type>text</type></entry>
4540 <entry>Get comment for a database object</entry>
4543 <entry><function>obj_description</function>(<parameter>objectOID</parameter>)</entry>
4544 <entry><type>text</type></entry>
4545 <entry>Get comment for a database object (<emphasis>deprecated</emphasis>)</entry>
4548 <entry><function>col_description</function>(<parameter>tableOID</parameter>, <parameter>columnnumber</parameter>)</entry>
4549 <entry><type>text</type></entry>
4550 <entry>Get comment for a table column</entry>
4556 <indexterm zone="functions-misc">
4557 <primary>obj_description</primary>
4560 <indexterm zone="functions-misc">
4561 <primary>col_description</primary>
4565 These functions extract comments previously stored with the
4566 <command>COMMENT</command> command. <literal>NULL</literal> is returned if
4567 no comment can be found matching the specified parameters.
4571 The two-parameter form of <function>obj_description()</function> returns the
4572 comment for a database object specified by its OID and the name of the
4573 containing system catalog. For example,
4574 <literal>obj_description(123456,'pg_class')</literal>
4575 would retrieve the comment for a table with OID 123456.
4576 The one-parameter form of <function>obj_description()</function> requires only
4577 the object OID. It is now deprecated since there is no guarantee that
4578 OIDs are unique across different system catalogs; therefore, the wrong
4579 comment could be returned.
4583 <function>col_description()</function> returns the comment for a table column,
4584 which is specified by the OID of its table and its column number.
4585 <function>obj_description()</function> cannot be used for table columns since
4586 columns do not have OIDs of their own.
4592 <sect1 id="functions-aggregate">
4593 <title>Aggregate Functions</title>
4596 <title>Author</title>
4598 Written by Isaac Wilcox <email>isaac@azartmedia.com</email> on 2000-06-16
4603 <firstterm>Aggregate functions</firstterm> compute a single result
4604 value from a set of input values. The special syntax
4605 considerations for aggregate functions are explained in <xref
4606 linkend="syntax-aggregates">. Consult the <citetitle>PostgreSQL
4607 Tutorial</citetitle> for additional introductory information.
4610 <table tocentry="1">
4611 <title>Aggregate Functions</title>
4616 <entry>Function</entry>
4617 <entry>Description</entry>
4618 <entry>Notes</entry>
4624 <entry>avg(<replaceable class="parameter">expression</replaceable>)</entry>
4625 <entry>the average (arithmetic mean) of all input values</entry>
4628 <primary>average</primary>
4629 <secondary>function</secondary>
4631 Finding the average value is available on the following data
4632 types: <type>smallint</type>, <type>integer</type>,
4633 <type>bigint</type>, <type>real</type>, <type>double
4634 precision</type>, <type>numeric</type>, <type>interval</type>.
4635 The result is of type <type>numeric</type> for any integer type
4636 input, <type>double precision</type> for floating-point input,
4637 otherwise the same as the input data type.
4642 <entry><function>count</function>(*)</entry>
4643 <entry>number of input values</entry>
4644 <entry>The return value is of type <type>bigint</type>.</entry>
4648 <entry><function>count</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4650 Counts the input values for which the value of <replaceable
4651 class="parameter">expression</replaceable> is not NULL.
4653 <entry>The return value is of type <type>bigint</type>.</entry>
4657 <entry><function>max</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4658 <entry>the maximum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4660 Available for all numeric, string, and date/time types. The
4661 result has the same type as the input expression.
4666 <entry><function>min</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4667 <entry>the minimum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4669 Available for all numeric, string, and date/time types. The
4670 result has the same type as the input expression.
4675 <entry><function>stddev</function>(<replaceable
4676 class="parameter">expression</replaceable>)</entry>
4677 <entry>the sample standard deviation of the input values</entry>
4680 <primary>standard deviation</primary>
4682 Finding the standard deviation is available on the following
4683 data types: <type>smallint</type>, <type>integer</type>,
4684 <type>bigint</type>, <type>real</type>, <type>double
4685 precision</type>, <type>numeric</type>. The result is of type
4686 <type>double precision</type> for floating-point input,
4687 otherwise <type>numeric</type>.
4692 <entry><function>sum</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4693 <entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4695 Summation is available on the following data types:
4696 <type>smallint</type>, <type>integer</type>,
4697 <type>bigint</type>, <type>real</type>, <type>double
4698 precision</type>, <type>numeric</type>, <type>interval</type>.
4699 The result is of type <type>bigint</type> for <type>smallint</type>
4700 or <type>integer</type> input, <type>numeric</type> for
4702 input, <type>double precision</type> for floating-point input,
4703 otherwise the same as the input data type.
4708 <entry><function>variance</function>(<replaceable
4709 class="parameter">expression</replaceable>)</entry>
4710 <entry>the sample variance of the input values</entry>
4713 <primary>variance</primary>
4715 The variance is the square of the standard deviation. The
4716 supported data types and result types are the same as for
4726 It should be noted that except for <function>COUNT</function>,
4727 these functions return NULL when no rows are selected. In
4728 particular, <function>SUM</function> of no rows returns NULL, not
4729 zero as one might expect. <function>COALESCE</function> may be
4730 used to substitute zero for NULL when necessary.
4736 <sect1 id="functions-subquery">
4737 <title>Subquery Expressions</title>
4740 <primary>exists</primary>
4744 <primary>in</primary>
4748 <primary>not in</primary>
4752 <primary>any</primary>
4756 <primary>all</primary>
4760 <primary>some</primary>
4764 <primary>subqueries</primary>
4768 This section describes the <acronym>SQL</acronym>-compliant subquery
4769 expressions available in <productname>PostgreSQL</productname>.
4770 All of the expression forms documented in this section return
4771 Boolean (true/false) results.
4774 <bridgehead renderas="sect2">EXISTS</bridgehead>
4777 EXISTS ( <replaceable>subquery</replaceable> )
4781 The argument of <token>EXISTS</token> is an arbitrary SELECT statement,
4782 or <firstterm>subquery</firstterm>. The
4783 subquery is evaluated to determine whether it returns any rows.
4784 If it returns at least one row, the result of <token>EXISTS</token> is
4785 TRUE; if the subquery returns no rows, the result of <token>EXISTS</token>
4790 The subquery can refer to variables from the surrounding query,
4791 which will act as constants during any one evaluation of the subquery.
4795 The subquery will generally only be executed far enough to determine
4796 whether at least one row is returned, not all the way to completion.
4797 It is unwise to write a subquery that has any side-effects (such as
4798 calling sequence functions); whether the side-effects occur or not
4799 may be difficult to predict.
4803 Since the result depends only on whether any rows are returned,
4804 and not on the contents of those rows, the output list of the
4805 subquery is normally uninteresting. A common coding convention is
4806 to write all EXISTS tests in the form
4807 <literal>EXISTS(SELECT 1 WHERE ...)</literal>. There are exceptions to
4808 this rule however, such as subqueries that use <token>INTERSECT</token>.
4812 This simple example is like an inner join on col2, but it produces at
4813 most one output row for each tab1 row, even if there are multiple matching
4816 SELECT col1 FROM tab1
4817 WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
4821 <bridgehead renderas="sect2">IN (scalar form)</bridgehead>
4824 <replaceable>expression</replaceable> IN
4825 <replaceable>ble>value</replaceable><optional>, ...</optional>)
4829 The right-hand side of this form of <token>IN</token> is a parenthesized list
4830 of scalar expressions. The result is TRUE if the left-hand expression's
4831 result is equal to any of the right-hand expressions. This is a shorthand
4835 <replaceable>expression</replaceable> = <replaceable>value1</replaceable>
4837 <replaceable>expression</replaceable> = <replaceable>value2</replaceable>
4842 Note that if the left-hand expression yields NULL, or if there are
4843 no equal right-hand values and at least one right-hand expression yields
4844 NULL, the result of the <token>IN</token> construct will be NULL, not FALSE.
4845 This is in accordance with SQL's normal rules for Boolean combinations
4851 This form of <token>IN</token> is not truly a subquery expression, but it
4852 seems best to document it in the same place as subquery <token>IN</token>.
4856 <bridgehead renderas="sect2">IN (subquery form)</bridgehead>
4859 <replaceable>expression</replaceable> IN (<replaceable>subquery</replaceable>)
4863 The right-hand side of this form of <token>IN</token> is a parenthesized
4864 subquery, which must return exactly one column. The left-hand expression
4865 is evaluated and compared to each row of the subquery result.
4866 The result of <token>IN</token> is TRUE if any equal subquery row is found.
4867 The result is FALSE if no equal row is found (including the special
4868 case where the subquery returns no rows).
4872 Note that if the left-hand expression yields NULL, or if there are
4873 no equal right-hand values and at least one right-hand row yields
4874 NULL, the result of the <token>IN</token> construct will be NULL, not FALSE.
4875 This is in accordance with SQL's normal rules for Boolean combinations
4880 As with <token>EXISTS</token>, it's unwise to assume that the subquery will
4881 be evaluated completely.
4885 (<replaceable>expression</replaceable>,
4886 <replaceable>ble>expres</replaceable><optional>nal>,</optional>nal>)
4887 IN (<replaceable>subquery</replaceable>)
4891 The right-hand side of this form of <token>IN</token> is a parenthesized
4892 subquery, which must return exactly as many columns as there are
4893 expressions in the left-hand list. The left-hand expressions are
4894 evaluated and compared row-wise to each row of the subquery result.
4895 The result of <token>IN</token> is TRUE if any equal subquery row is found.
4896 The result is FALSE if no equal row is found (including the special
4897 case where the subquery returns no rows).
4901 As usual, NULLs in the expressions or subquery rows are combined per
4902 the normal rules of SQL Boolean expressions. Two rows are considered
4903 equal if all their corresponding members are non-null and equal; the rows
4904 are unequal if any corresponding members are non-null and unequal;
4905 otherwise the result of that row comparison is unknown (NULL).
4906 If all the row results are either unequal or NULL, with at least one NULL,
4907 then the result of <token>IN</token> is NULL.
4910 <bridgehead renderas="sect2">NOT IN (scalar form)</bridgehead>
4913 <replaceable>expression</replaceable> NOT IN
4914 <replaceable>ble>value</replaceable><optional>, ...</optional>)
4918 The right-hand side of this form of <token>NOT IN</token> is a parenthesized list
4919 of scalar expressions. The result is TRUE if the left-hand expression's
4920 result is unequal to all of the right-hand expressions. This is a shorthand
4924 <replaceable>expression</replaceable> <> <replaceable>value1</replaceable>
4926 <replaceable>expression</replaceable> <> <replaceable>value2</replaceable>
4931 Note that if the left-hand expression yields NULL, or if there are
4932 no equal right-hand values and at least one right-hand expression yields
4933 NULL, the result of the <token>NOT IN</token> construct will be NULL, not TRUE
4934 as one might naively expect.
4935 This is in accordance with SQL's normal rules for Boolean combinations
4941 <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all
4942 cases. However, NULLs are much more likely to trip up the novice when
4943 working with <token>NOT IN</token> than when working with <token>IN</token>.
4944 It's best to express your condition positively if possible.
4948 <bridgehead renderas="sect2">NOT IN (subquery form)</bridgehead>
4951 <replaceable>expression</replaceable> NOT IN (<replaceable>subquery</replaceable>)
4955 The right-hand side of this form of <token>NOT IN</token> is a parenthesized
4956 subquery, which must return exactly one column. The left-hand expression
4957 is evaluated and compared to each row of the subquery result.
4958 The result of <token>NOT IN</token> is TRUE if only unequal subquery rows
4959 are found (including the special case where the subquery returns no rows).
4960 The result is FALSE if any equal row is found.
4964 Note that if the left-hand expression yields NULL, or if there are
4965 no equal right-hand values and at least one right-hand row yields
4966 NULL, the result of the <token>NOT IN</token> construct will be NULL, not TRUE.
4967 This is in accordance with SQL's normal rules for Boolean combinations
4972 As with <token>EXISTS</token>, it's unwise to assume that the subquery will
4973 be evaluated completely.
4977 (<replaceable>expression</replaceable>,
4978 <replaceable>ble>expres</replaceable><optional>nal>,</optional>nal>)
4979 NOT IN (<replaceable>subquery</replaceable>)
4983 The right-hand side of this form of <token>NOT IN</token> is a parenthesized
4984 subquery, which must return exactly as many columns as there are
4985 expressions in the left-hand list. The left-hand expressions are
4986 evaluated and compared row-wise to each row of the subquery result.
4987 The result of <token>NOT IN</token> is TRUE if only unequal subquery rows
4988 are found (including the special case where the subquery returns no rows).
4989 The result is FALSE if any equal row is found.
4993 As usual, NULLs in the expressions or subquery rows are combined per
4994 the normal rules of SQL Boolean expressions. Two rows are considered
4995 equal if all their corresponding members are non-null and equal; the rows
4996 are unequal if any corresponding members are non-null and unequal;
4997 otherwise the result of that row comparison is unknown (NULL).
4998 If all the row results are either unequal or NULL, with at least one NULL,
4999 then the result of <token>NOT IN</token> is NULL.
5002 <bridgehead renderas="sect2">ANY</bridgehead>
5005 <replaceable>expression</replaceable>
5006 <replaceable>ble>oper</replaceable>ble> ANY (<replaceable>subquery</replaceable>)
5007 <replaceable>expression</replaceable>
5008 <replaceable>ble>oper</replaceable>ble> SOME (<replaceable>subquery</replaceable>)
5012 The right-hand side of this form of <token>ANY</token> is a parenthesized
5013 subquery, which must return exactly one column. The left-hand expression
5014 is evaluated and compared to each row of the subquery result using the
5015 given <replaceable>operator</replaceable>, which must yield a Boolean
5017 The result of <token>ANY</token> is TRUE if any true result is obtained.
5018 The result is FALSE if no true result is found (including the special
5019 case where the subquery returns no rows).
5023 <token>SOME</token> is a synonym for <token>ANY</token>.
5024 <token>IN</token> is equivalent to <literal>= ANY</literal>.
5028 Note that if there are no successes and at least one right-hand row yields
5029 NULL for the operator's result, the result of the <token>ANY</token> construct
5030 will be NULL, not FALSE.
5031 This is in accordance with SQL's normal rules for Boolean combinations
5036 As with <token>EXISTS</token>, it's unwise to assume that the subquery will
5037 be evaluated completely.
5041 (<replaceable>expression</replaceable>,
5042 <replaceable>ble>expres</replaceable><optional>nal>,</optional>optiona<replaceable>aceable></replaceable>aceable> ANY (<replaceable>subquery</replaceable>)
5043 (<replaceable>expression</replaceable>,
5044 <replaceable>ble>expres</replaceable><optional>nal>,</optional>optiona<replaceable>aceable></replaceable>aceable> SOME (<replaceable>subquery</replaceable>)
5048 The right-hand side of this form of <token>ANY</token> is a parenthesized
5049 subquery, which must return exactly as many columns as there are
5050 expressions in the left-hand list. The left-hand expressions are
5051 evaluated and compared row-wise to each row of the subquery result,
5052 using the given <replaceable>operator</replaceable>. Presently,
5053 only <literal>=</literal> and <literal><></literal> operators are allowed
5054 in row-wise <token>ANY</token> queries.
5055 The result of <token>ANY</token> is TRUE if any equal or unequal row is
5056 found, respectively.
5057 The result is FALSE if no such row is found (including the special
5058 case where the subquery returns no rows).
5062 As usual, NULLs in the expressions or subquery rows are combined per
5063 the normal rules of SQL Boolean expressions. Two rows are considered
5064 equal if all their corresponding members are non-null and equal; the rows
5065 are unequal if any corresponding members are non-null and unequal;
5066 otherwise the result of that row comparison is unknown (NULL).
5067 If there is at least one NULL row result, then the result of <token>ANY</token>
5068 cannot be FALSE; it will be TRUE or NULL.
5071 <bridgehead renderas="sect2">ALL</bridgehead>
5074 <replaceable>expression</replaceable>
5075 <replaceable>ble>oper</replaceable>ble> ALL (<replaceable>subquery</replaceable>)
5079 The right-hand side of this form of <token>ALL</token> is a parenthesized
5080 subquery, which must return exactly one column. The left-hand expression
5081 is evaluated and compared to each row of the subquery result using the
5082 given <replaceable>operator</replaceable>, which must yield a Boolean
5084 The result of <token>ALL</token> is TRUE if all rows yield TRUE
5085 (including the special case where the subquery returns no rows).
5086 The result is FALSE if any false result is found.
5090 <token>NOT IN</token> is equivalent to <literal><> ALL</literal>.
5094 Note that if there are no failures but at least one right-hand row yields
5095 NULL for the operator's result, the result of the <token>ALL</token> construct
5096 will be NULL, not TRUE.
5097 This is in accordance with SQL's normal rules for Boolean combinations
5102 As with <token>EXISTS</token>, it's unwise to assume that the subquery will
5103 be evaluated completely.
5107 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)
5111 The right-hand side of this form of <token>ALL</token> is a parenthesized
5112 subquery, which must return exactly as many columns as there are
5113 expressions in the left-hand list. The left-hand expressions are
5114 evaluated and compared row-wise to each row of the subquery result,
5115 using the given <replaceable>operator</replaceable>. Presently,
5116 only <literal>=</literal> and <literal><></literal> operators are allowed
5117 in row-wise <token>ALL</token> queries.
5118 The result of <token>ALL</token> is TRUE if all subquery rows are equal
5119 or unequal, respectively (including the special
5120 case where the subquery returns no rows).
5121 The result is FALSE if any row is found to be unequal or equal,
5126 As usual, NULLs in the expressions or subquery rows are combined per
5127 the normal rules of SQL Boolean expressions. Two rows are considered
5128 equal if all their corresponding members are non-null and equal; the rows
5129 are unequal if any corresponding members are non-null and unequal;
5130 otherwise the result of that row comparison is unknown (NULL).
5131 If there is at least one NULL row result, then the result of <token>ALL</token>
5132 cannot be TRUE; it will be FALSE or NULL.
5135 <bridgehead renderas="sect2">Row-wise comparison</bridgehead>
5138 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>)
5139 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> (<replaceable>expression</replaceable> <replaceable>expression</replaceable><optional>, ...</optional>)
5143 The left-hand side is a list of scalar expressions. The right-hand side
5144 can be either a list of scalar expressions of the same length, or a
5145 parenthesized subquery, which must return exactly as many columns as there
5146 are expressions on the left-hand side. Furthermore, the subquery cannot
5147 return more than one row. (If it returns zero rows, the result is taken to
5148 be NULL.) The left-hand side is evaluated and compared row-wise to the
5149 single subquery result row, or to the right-hand expression list.
5150 Presently, only <literal>=</literal> and <literal><></literal> operators are allowed
5151 in row-wise comparisons.
5152 The result is TRUE if the two rows are equal or unequal, respectively.
5156 As usual, NULLs in the expressions or subquery rows are combined per
5157 the normal rules of SQL Boolean expressions. Two rows are considered
5158 equal if all their corresponding members are non-null and equal; the rows
5159 are unequal if any corresponding members are non-null and unequal;
5160 otherwise the result of the row comparison is unknown (NULL).
5167 <!-- Keep this comment at the end of the file
5172 sgml-minimize-attributes:nil
5173 sgml-always-quote-attributes:t
5176 sgml-parent-document:nil
5177 sgml-default-dtd-file:"./reference.ced"
5178 sgml-exposed-tags:nil
5179 sgml-local-catalogs:("/usr/lib/sgml/catalog")
5180 sgml-local-ecat-files:nil