2 $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.101 2002/06/11 16:00:17 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</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</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>now</function>()</entry>
2795 <entry><type>timestamp</type></entry>
2796 <entry>Current date and time (equivalent to
2797 <function>current_timestamp</function>); see <link
2798 linkend="functions-datetime-current">below</link>
2805 <entry><function>timeofday()</function></entry>
2806 <entry><type>text</type></entry>
2807 <entry>Current date and time; see <link
2808 linkend="functions-datetime-current">below</link>
2810 <entry><literal>timeofday()</literal></entry>
2811 <entry><literal>Wed Feb 21 17:01:13.000126 2001 EST</literal></entry>
2819 <sect2 id="functions-datetime-extract">
2820 <title><function>EXTRACT</function>, <function>date_part</function></title>
2823 EXTRACT (<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
2827 The <function>extract</function> function retrieves sub-fields
2828 from date/time values, such as year or hour.
2829 <replaceable>source</replaceable> is a value expression that
2830 evaluates to type <type>timestamp</type> or <type>interval</type>.
2831 (Expressions of type <type>date</type> or <type>time</type> will
2832 be cast to <type>timestamp</type> and can therefore be used as
2833 well.) <replaceable>field</replaceable> is an identifier or
2834 string that selects what field to extract from the source value.
2835 The <function>extract</function> function returns values of type
2836 <type>double precision</type>.
2837 The following are valid values:
2839 <!-- alphabetical -->
2842 <term><literal>century</literal></term>
2845 The year field divided by 100
2850 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
2851 <lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
2856 Note that the result for the century field is simply the year field
2857 divided by 100, and not the conventional definition which puts most
2858 years in the 1900's in the twentieth century.
2864 <term><literal>day</literal></term>
2867 The day (of the month) field (1 - 31)
2872 SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
2873 <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
2880 <term><literal>decade</literal></term>
2883 The year field divided by 10
2888 SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
2889 <lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>
2896 <term><literal>dow</literal></term>
2899 The day of the week (0 - 6; Sunday is 0) (for
2900 <type>timestamp</type> values only)
2905 SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
2906 <lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
2913 <term><literal>doy</literal></term>
2916 The day of the year (1 - 365/366) (for <type>timestamp</type> values only)
2920 SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
2921 <lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>
2928 <term><literal>epoch</literal></term>
2931 For <type>date</type> and <type>timestamp</type> values, the
2932 number of seconds since 1970-01-01 00:00:00-00 (Result may be
2933 negative.); for <type>interval</type> values, the total number
2934 of seconds in the interval
2939 SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
2940 <lineannotation>Result: </lineannotation><computeroutput>982352320</computeroutput>
2942 SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
2943 <lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>
2950 <term><literal>hour</literal></term>
2953 The hour field (0 - 23)
2958 SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
2959 <lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
2966 <term><literal>microseconds</literal></term>
2969 The seconds field, including fractional parts, multiplied by 1
2970 000 000. Note that this includes full seconds.
2975 SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
2976 <lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>
2983 <term><literal>millennium</literal></term>
2986 The year field divided by 1000
2991 SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
2992 <lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
2997 Note that the result for the millennium field is simply the year field
2998 divided by 1000, and not the conventional definition which puts
2999 years in the 1900's in the second millennium.
3005 <term><literal>milliseconds</literal></term>
3008 The seconds field, including fractional parts, multiplied by
3009 1000. Note that this includes full seconds.
3014 SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
3015 <lineannotation>Result: </lineannotation><computeroutput>28500</computeroutput>
3022 <term><literal>minute</literal></term>
3025 The minutes field (0 - 59)
3030 SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
3031 <lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>
3038 <term><literal>month</literal></term>
3041 For <type>timestamp</type> values, the number of the month
3042 within the year (1 - 12) ; for <type>interval</type> values
3043 the number of months, modulo 12 (0 - 11)
3048 SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
3049 <lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
3051 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
3052 <lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
3054 SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
3055 <lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
3062 <term><literal>quarter</literal></term>
3065 The quarter of the year (1 - 4) that the day is in (for
3066 <type>timestamp</type> values only)
3071 SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
3072 <lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
3079 <term><literal>second</literal></term>
3082 The seconds field, including fractional parts (0 -
3083 59<footnote><simpara>60 if leap seconds are
3084 implemented by the operating system</simpara></footnote>)
3089 SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
3090 <lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
3092 SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
3093 <lineannotation>Result: </lineannotation><computeroutput>28.5</computeroutput>
3100 <term><literal>timezone</literal></term>
3103 The time zone offset. XXX But in what units?
3110 <term><literal>timezone_hour</literal></term>
3113 The hour component of the time zone offset.
3119 <term><literal>timezone_minute</literal></term>
3122 The minute component of the time zone offset.
3128 <term><literal>week</literal></term>
3131 From a <type>timestamp</type> value, calculate the number of
3132 the week of the year that the day is in. By definition
3133 (<acronym>ISO</acronym> 8601), the first week of a year
3134 contains January 4 of that year. (The <acronym>ISO</acronym>
3135 week starts on Monday.) In other words, the first Thursday of
3136 a year is in week 1 of that year.
3141 SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
3142 <lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
3149 <term><literal>year</literal></term>
3157 SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
3158 <lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>
3169 The <function>extract</function> function is primarily intended
3170 for computational processing. For formatting date/time values for
3171 display, see <xref linkend="functions-formatting">.
3174 <anchor id="functions-datetime-datepart">
3176 The <function>date_part</function> function is modeled on the traditional
3177 <productname>Ingres</productname> equivalent to the
3178 <acronym>SQL</acronym>-function <function>extract</function>:
3180 date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
3182 Note that here the <replaceable>field</replaceable> value needs to
3183 be a string. The valid field values for
3184 <function>date_part</function> are the same as for
3185 <function>extract</function>.
3190 SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
3191 <lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
3193 SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
3194 <lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
3200 <sect2 id="functions-datetime-trunc">
3201 <title><function>date_trunc</function></title>
3204 The function <function>date_trunc</function> is conceptually
3205 similar to the <function>trunc</function> function for numbers.
3210 date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
3212 <replaceable>source</replaceable> is a value expression of type
3213 <type>timestamp</type> (values of type <type>date</type> and
3214 <type>time</type> are cast automatically).
3215 <replaceable>field</replaceable> selects to which precision to
3216 truncate the time stamp value. The return value is of type
3217 <type>timestamp</type> with all fields that are less than the
3218 selected one set to zero (or one, for day and month).
3222 Valid values for <replaceable>field</replaceable> are:
3224 <member>microseconds</member>
3225 <member>milliseconds</member>
3226 <member>second</member>
3227 <member>minute</member>
3228 <member>hour</member>
3229 <member>day</member>
3230 <member>month</member>
3231 <member>year</member>
3232 <member>decade</member>
3233 <member>century</member>
3234 <member>millennium</member>
3241 SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
3242 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00+00</computeroutput>
3244 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
3245 <lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00+00</computeroutput>
3251 <sect2 id="functions-datetime-current">
3252 <title>Current Date/Time</title>
3255 <primary>date</primary>
3256 <secondary>current</secondary>
3260 <primary>time</primary>
3261 <secondary>current</secondary>
3265 The following functions are available to obtain the current date and/or
3271 CURRENT_TIME ( <replaceable>precision</replaceable> )
3272 CURRENT_TIMESTAMP ( <replaceable>precision</replaceable> )
3274 <function>CURRENT_TIME</function> and
3275 <function>CURRENT_TIMESTAMP</function> can optionally be given
3276 a precision parameter, which causes the result to be rounded
3277 to that many fractional digits. Without a precision parameter,
3278 the result is given to full available precision.
3283 Prior to <productname>PostgreSQL</productname> 7.2, the precision parameters
3284 were unimplemented, and the result was always given in integer
3291 The <acronym>SQL99</acronym> standard requires these functions to
3292 be written without any parentheses, unless a precision parameter
3293 is given. As of <productname>PostgreSQL</productname> 7.2, an empty pair of
3294 parentheses can be written, but this is deprecated and may be
3295 removed in a future release.
3301 SELECT CURRENT_TIME;
3302 <computeroutput>14:39:53.662522-05</computeroutput>
3304 SELECT CURRENT_DATE;
3305 <computeroutput>2001-12-23</computeroutput>
3307 SELECT CURRENT_TIMESTAMP;
3308 <computeroutput>2001-12-23 14:39:53.662522-05</computeroutput>
3310 SELECT CURRENT_TIMESTAMP(2);
3311 <computeroutput>2001-12-23 14:39:53.66-05</computeroutput>
3316 The function <function>now()</function> is the traditional
3317 <productname>PostgreSQL</productname> equivalent to
3318 <function>CURRENT_TIMESTAMP</function>.
3322 There is also <function>timeofday()</function>, which for historical
3323 reasons returns a text string rather than a <type>timestamp</type> value:
3329 Sat Feb 17 19:07:32.000126 2001 EST
3334 It is quite important to realize that
3335 <function>CURRENT_TIMESTAMP</function> and related functions all return
3336 the time as of the start of the current transaction; their values do not
3337 increment while a transaction is running. But
3338 <function>timeofday()</function> returns the actual current time.
3342 All the date/time data types also accept the special literal value
3343 <literal>now</literal> to specify the current date and time. Thus,
3344 the following three all return the same result:
3346 SELECT CURRENT_TIMESTAMP;
3348 SELECT TIMESTAMP 'now';
3352 You do not want to use the third form when specifying a DEFAULT
3353 value while creating a table. The system will convert <literal>now</literal>
3354 to a <type>timestamp</type> as soon as the constant is parsed, so that when
3355 the default value is needed,
3356 the time of the table creation would be used! The first two
3357 forms will not be evaluated until the default value is used,
3358 because they are function calls. Thus they will give the desired
3359 behavior of defaulting to the time of row insertion.
3367 <sect1 id="functions-geometry">
3368 <title>Geometric Functions and Operators</title>
3371 The geometric types <type>point</type>, <type>box</type>,
3372 <type>lseg</type>, <type>line</type>, <type>path</type>,
3373 <type>polygon</type>, and <type>circle</type> have a large set of
3374 native support functions and operators.
3378 <title>Geometric Operators</title>
3382 <entry>Operator</entry>
3383 <entry>Description</entry>
3384 <entry>Usage</entry>
3390 <entry>Translation</entry>
3391 <entry><literal>box '((0,0),(1,1))' + point '(2.0,0)'</literal></entry>
3395 <entry>Translation</entry>
3396 <entry><literal>box '((0,0),(1,1))' - point '(2.0,0)'</literal></entry>
3400 <entry>Scaling/rotation</entry>
3401 <entry><literal>box '((0,0),(1,1))' * point '(2.0,0)'</literal></entry>
3405 <entry>Scaling/rotation</entry>
3406 <entry><literal>box '((0,0),(2,2))' / point '(2.0,0)'</literal></entry>
3410 <entry>Intersection</entry>
3411 <entry><literal>'((1,-1),(-1,1))' # '((1,1),(-1,-1))'</literal></entry>
3415 <entry>Number of points in polygon</entry>
3416 <entry><literal># '((1,0),(0,1),(-1,0))'</literal></entry>
3420 <entry>Point of closest proximity</entry>
3421 <entry><literal>point '(0,0)' ## lseg '((2,0),(0,2))'</literal></entry>
3424 <entry> && </entry>
3425 <entry>Overlaps?</entry>
3426 <entry><literal>box '((0,0),(1,1))' && box '((0,0),(2,2))'</literal></entry>
3429 <entry> &< </entry>
3430 <entry>Overlaps to left?</entry>
3431 <entry><literal>box '((0,0),(1,1))' &< box '((0,0),(2,2))'</literal></entry>
3434 <entry> &> </entry>
3435 <entry>Overlaps to right?</entry>
3436 <entry><literal>box '((0,0),(3,3))' &> box '((0,0),(2,2))'</literal></entry>
3439 <entry> <-> </entry>
3440 <entry>Distance between</entry>
3441 <entry><literal>circle '((0,0),1)' <-> circle '((5,0),1)'</literal></entry>
3444 <entry> << </entry>
3445 <entry>Left of?</entry>
3446 <entry><literal>circle '((0,0),1)' << circle '((5,0),1)'</literal></entry>
3449 <entry> <^ </entry>
3450 <entry>Is below?</entry>
3451 <entry><literal>circle '((0,0),1)' <^ circle '((0,5),1)'</literal></entry>
3454 <entry> >> </entry>
3455 <entry>Is right of?</entry>
3456 <entry><literal>circle '((5,0),1)' >> circle '((0,0),1)'</literal></entry>
3459 <entry> >^ </entry>
3460 <entry>Is above?</entry>
3461 <entry><literal>circle '((0,5),1)' >^ circle '((0,0),1)'</literal></entry>
3465 <entry>Intersects or overlaps</entry>
3466 <entry><literal>lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'</literal></entry>
3470 <entry>Is horizontal?</entry>
3471 <entry><literal>point '(1,0)' ?- point '(0,0)'</literal></entry>
3474 <entry> ?-| </entry>
3475 <entry>Is perpendicular?</entry>
3476 <entry><literal>lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'</literal></entry>
3479 <entry> @-@ </entry>
3480 <entry>Length or circumference</entry>
3481 <entry><literal>@-@ path '((0,0),(1,0))'</literal></entry>
3485 <entry>Is vertical?</entry>
3486 <entry><literal>point '(0,1)' ?| point '(0,0)'</literal></entry>
3489 <entry> ?|| </entry>
3490 <entry>Is parallel?</entry>
3491 <entry><literal>lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'</literal></entry>
3495 <entry>Contained or on</entry>
3496 <entry><literal>point '(1,1)' @ circle '((0,0),2)'</literal></entry>
3500 <entry>Center of</entry>
3501 <entry><literal>@@ circle '((0,0),10)'</literal></entry>
3505 <entry>Same as</entry>
3506 <entry><literal>polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'</literal></entry>
3513 <title>Geometric Functions</title>
3517 <entry>Function</entry>
3518 <entry>Returns</entry>
3519 <entry>Description</entry>
3520 <entry>Example</entry>
3525 <entry><function>area</function>(object)</entry>
3526 <entry><type>double precision</type></entry>
3527 <entry>area of item</entry>
3528 <entry><literal>area(box '((0,0),(1,1))')</literal></entry>
3531 <entry><function>box</function>(box, box)</entry>
3532 <entry><type>box</type></entry>
3533 <entry>intersection box</entry>
3534 <entry><literal>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</literal></entry>
3537 <entry><function>center</function>(object)</entry>
3538 <entry><type>point</type></entry>
3539 <entry>center of item</entry>
3540 <entry><literal>center(box '((0,0),(1,2))')</literal></entry>
3543 <entry><function>diameter</function>(circle)</entry>
3544 <entry><type>double precision</type></entry>
3545 <entry>diameter of circle</entry>
3546 <entry><literal>diameter(circle '((0,0),2.0)')</literal></entry>
3549 <entry><function>height</function>(box)</entry>
3550 <entry><type>double precision</type></entry>
3551 <entry>vertical size of box</entry>
3552 <entry><literal>height(box '((0,0),(1,1))')</literal></entry>
3555 <entry><function>isclosed</function>(path)</entry>
3556 <entry><type>boolean</type></entry>
3557 <entry>a closed path?</entry>
3558 <entry><literal>isclosed(path '((0,0),(1,1),(2,0))')</literal></entry>
3561 <entry><function>isopen</function>(path)</entry>
3562 <entry><type>boolean</type></entry>
3563 <entry>an open path?</entry>
3564 <entry><literal>isopen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3567 <entry><function>length</function>(object)</entry>
3568 <entry><type>double precision</type></entry>
3569 <entry>length of item</entry>
3570 <entry><literal>length(path '((-1,0),(1,0))')</literal></entry>
3573 <entry><function>pclose</function>(path)</entry>
3574 <entry><type>path</type></entry>
3575 <entry>convert path to closed</entry>
3576 <entry><literal>popen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3579 <!-- Not defined by this name. Implements the intersection operator '#' -->
3581 <entry><function>point</function>(lseg,lseg)</entry>
3582 <entry><type>point</type></entry>
3583 <entry>intersection</entry>
3584 <entry><literal>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</literal></entry>
3588 <entry><function>npoint</function>(path)</entry>
3589 <entry><type>integer</type></entry>
3590 <entry>number of points</entry>
3591 <entry><literal>npoints(path '[(0,0),(1,1),(2,0)]')</literal></entry>
3594 <entry><function>popen</function>(path)</entry>
3595 <entry><type>path</type></entry>
3596 <entry>convert path to open path</entry>
3597 <entry><literal>popen(path '((0,0),(1,1),(2,0))')</literal></entry>
3600 <entry><function>radius</function>(circle)</entry>
3601 <entry><type>double precision</type></entry>
3602 <entry>radius of circle</entry>
3603 <entry><literal>radius(circle '((0,0),2.0)')</literal></entry>
3606 <entry><function>width</function>(box)</entry>
3607 <entry><type>double precision</type></entry>
3608 <entry>horizontal size</entry>
3609 <entry><literal>width(box '((0,0),(1,1))')</literal></entry>
3617 <title>Geometric Type Conversion Functions</title>
3621 <entry>Function</entry>
3622 <entry>Returns</entry>
3623 <entry>Description</entry>
3624 <entry>Example</entry>
3629 <entry><function>box</function>(<type>circle</type>)</entry>
3630 <entry><type>box</type></entry>
3631 <entry>circle to box</entry>
3632 <entry><literal>box(circle '((0,0),2.0)')</literal></entry>
3635 <entry><function>box</function>(<type>point</type>, <type>point</type>)</entry>
3636 <entry><type>box</type></entry>
3637 <entry>points to box</entry>
3638 <entry><literal>box(point '(0,0)', point '(1,1)')</literal></entry>
3641 <entry><function>box</function>(<type>polygon</type>)</entry>
3642 <entry><type>box</type></entry>
3643 <entry>polygon to box</entry>
3644 <entry><literal>box(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3647 <entry><function>circle</function>(<type>box</type>)</entry>
3648 <entry><type>circle</type></entry>
3649 <entry>to circle</entry>
3650 <entry><literal>circle(box '((0,0),(1,1))')</literal></entry>
3653 <entry><function>circle</function>(<type>point</type>, <type>double precision</type>)</entry>
3654 <entry><type>circle</type></entry>
3655 <entry>point to circle</entry>
3656 <entry><literal>circle(point '(0,0)', 2.0)</literal></entry>
3659 <entry><function>lseg</function>(<type>box</type>)</entry>
3660 <entry><type>lseg</type></entry>
3661 <entry>box diagonal to <type>lseg</type></entry>
3662 <entry><literal>lseg(box '((-1,0),(1,0))')</literal></entry>
3665 <entry><function>lseg</function>(<type>point</type>, <type>point</type>)</entry>
3666 <entry><type>lseg</type></entry>
3667 <entry>points to <type>lseg</type></entry>
3668 <entry><literal>lseg(point '(-1,0)', point '(1,0)')</literal></entry>
3671 <entry><function>path</function>(<type>polygon</type>)</entry>
3672 <entry><type>point</type></entry>
3673 <entry>polygon to path</entry>
3674 <entry><literal>path(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3677 <entry><function>point</function>(<type>circle</type>)</entry>
3678 <entry><type>point</type></entry>
3679 <entry>center</entry>
3680 <entry><literal>point(circle '((0,0),2.0)')</literal></entry>
3683 <entry><function>point</function>(<type>lseg</type>, <type>lseg</type>)</entry>
3684 <entry><type>point</type></entry>
3685 <entry>intersection</entry>
3686 <entry><literal>point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')</literal></entry>
3689 <entry><function>point</function>(<type>polygon</type>)</entry>
3690 <entry><type>point</type></entry>
3691 <entry>center</entry>
3692 <entry><literal>point(polygon '((0,0),(1,1),(2,0))')</literal></entry>
3695 <entry><function>polygon</function>(<type>box</type>)</entry>
3696 <entry><type>polygon</type></entry>
3697 <entry>12 point polygon</entry>
3698 <entry><literal>polygon(box '((0,0),(1,1))')</literal></entry>
3701 <entry><function>polygon</function>(<type>circle</type>)</entry>
3702 <entry><type>polygon</type></entry>
3703 <entry>12-point polygon</entry>
3704 <entry><literal>polygon(circle '((0,0),2.0)')</literal></entry>
3707 <entry><function>polygon</function>(<replaceable class="parameter">npts</replaceable>, <type>circle</type>)</entry>
3708 <entry><type>polygon</type></entry>
3709 <entry><replaceable class="parameter">npts</replaceable> polygon</entry>
3710 <entry><literal>polygon(12, circle '((0,0),2.0)')</literal></entry>
3713 <entry><function>polygon</function>(<type>path</type>)</entry>
3714 <entry><type>polygon</type></entry>
3715 <entry>path to polygon</entry>
3716 <entry><literal>polygon(path '((0,0),(1,1),(2,0))')</literal></entry>
3725 <sect1 id="functions-net">
3726 <title>Network Address Type Functions</title>
3729 <table tocentry="1" id="cidr-inet-operators-table">
3730 <title><type>cidr</type> and <type>inet</type> Operators</title>
3734 <entry>Operator</entry>
3735 <entry>Description</entry>
3736 <entry>Usage</entry>
3741 <entry> < </entry>
3742 <entry>Less than</entry>
3743 <entry><literal>inet '192.168.1.5' < inet '192.168.1.6'</literal></entry>
3746 <entry> <= </entry>
3747 <entry>Less than or equal</entry>
3748 <entry><literal>inet '192.168.1.5' <= inet '192.168.1.5'</literal></entry>
3752 <entry>Equals</entry>
3753 <entry><literal>inet '192.168.1.5' = inet '192.168.1.5'</literal></entry>
3756 <entry> >= </entry>
3757 <entry>Greater or equal</entry>
3758 <entry><literal>inet '192.168.1.5' >= inet '192.168.1.5'</literal></entry>
3761 <entry> > </entry>
3762 <entry>Greater</entry>
3763 <entry><literal>inet '192.168.1.5' > inet '192.168.1.4'</literal></entry>
3766 <entry> <> </entry>
3767 <entry>Not equal</entry>
3768 <entry><literal>inet '192.168.1.5' <> inet '192.168.1.4'</literal></entry>
3771 <entry> << </entry>
3772 <entry>is contained within</entry>
3773 <entry><literal>inet '192.168.1.5' << inet '192.168.1/24'</literal></entry>
3776 <entry> <<= </entry>
3777 <entry>is contained within or equals</entry>
3778 <entry><literal>inet '192.168.1/24' <<= inet '192.168.1/24'</literal></entry>
3781 <entry> >> </entry>
3782 <entry>contains</entry>
3783 <entry><literal>inet'192.168.1/24' >> inet '192.168.1.5'</literal></entry>
3786 <entry> >>= </entry>
3787 <entry>contains or equals</entry>
3788 <entry><literal>inet '192.168.1/24' >>= inet '192.168.1/24'</literal></entry>
3795 All of the operators for <type>inet</type> can be applied to
3796 <type>cidr</type> values as well. The operators
3797 <literal><<</literal>, <literal><<=</literal>,
3798 <literal>>></literal>, <literal>>>=</literal>
3799 test for subnet inclusion: they consider only the network parts
3800 of the two addresses, ignoring any host part, and determine whether
3801 one network part is identical to or a subnet of the other.
3805 <table tocentry="1" id="cidr-inet-functions">
3806 <title><type>cidr</type> and <type>inet</type> Functions</title>
3810 <entry>Function</entry>
3811 <entry>Returns</entry>
3812 <entry>Description</entry>
3813 <entry>Example</entry>
3814 <entry>Result</entry>
3819 <entry><function>broadcast</function>(<type>inet</type>)</entry>
3820 <entry><type>inet</type></entry>
3821 <entry>broadcast address for network</entry>
3822 <entry><literal>broadcast('192.168.1.5/24')</literal></entry>
3823 <entry><literal>192.168.1.255/24</literal></entry>
3826 <entry><function>host</function>(<type>inet</type>)</entry>
3827 <entry><type>text</type></entry>
3828 <entry>extract IP address as text</entry>
3829 <entry><literal>host('192.168.1.5/24')</literal></entry>
3830 <entry><literal>192.168.1.5</literal></entry>
3833 <entry><function>masklen</function>(<type>inet</type>)</entry>
3834 <entry><type>integer</type></entry>
3835 <entry>extract netmask length</entry>
3836 <entry><literal>masklen('192.168.1.5/24')</literal></entry>
3837 <entry><literal>24</literal></entry>
3840 <entry><function>set_masklen</function>(<type>inet</type>,<type>integer</type>)</entry>
3841 <entry><type>inet</type></entry>
3842 <entry>set netmask length for <type>inet</type> value</entry>
3843 <entry><literal>set_masklen('192.168.1.5/24',16)</literal></entry>
3844 <entry><literal>192.168.1.5/16</literal></entry>
3847 <entry><function>netmask</function>(<type>inet</type>)</entry>
3848 <entry><type>inet</type></entry>
3849 <entry>construct netmask for network</entry>
3850 <entry><literal>netmask('192.168.1.5/24')</literal></entry>
3851 <entry><literal>255.255.255.0</literal></entry>
3854 <entry><function>network</function>(<type>inet</type>)</entry>
3855 <entry><type>cidr</type></entry>
3856 <entry>extract network part of address</entry>
3857 <entry><literal>network('192.168.1.5/24')</literal></entry>
3858 <entry><literal>192.168.1.0/24</literal></entry>
3861 <entry><function>text</function>(<type>inet</type>)</entry>
3862 <entry><type>text</type></entry>
3863 <entry>extract IP address and masklen as text</entry>
3864 <entry><literal>text(inet '192.168.1.5')</literal></entry>
3865 <entry><literal>192.168.1.5/32</literal></entry>
3868 <entry><function>abbrev</function>(<type>inet</type>)</entry>
3869 <entry><type>text</type></entry>
3870 <entry>extract abbreviated display as text</entry>
3871 <entry><literal>abbrev(cidr '10.1.0.0/16')</literal></entry>
3872 <entry><literal>10.1/16</literal></entry>
3879 All of the functions for <type>inet</type> can be applied to
3880 <type>cidr</type> values as well. The <function>host</function>(),
3881 <function>text</function>(), and <function>abbrev</function>() functions are primarily
3882 intended to offer alternative display formats. You can cast a text
3883 field to inet using normal casting syntax: <literal>inet(expression)</literal> or
3884 <literal>colname::inet</literal>.
3887 <table tocentry="1" id="macaddr-functions">
3888 <title><type>macaddr</type> Functions</title>
3892 <entry>Function</entry>
3893 <entry>Returns</entry>
3894 <entry>Description</entry>
3895 <entry>Example</entry>
3896 <entry>Result</entry>
3901 <entry><function>trunc</function>(<type>macaddr</type>)</entry>
3902 <entry><type>macaddr</type></entry>
3903 <entry>set last 3 bytes to zero</entry>
3904 <entry><literal>trunc(macaddr '12:34:56:78:90:ab')</literal></entry>
3905 <entry><literal>12:34:56:00:00:00</literal></entry>
3912 The function <function>trunc</function>(<type>macaddr</type>) returns a MAC
3913 address with the last 3 bytes set to 0. This can be used to
3914 associate the remaining prefix with a manufacturer. The directory
3915 <filename>contrib/mac</filename> in the source distribution contains some
3916 utilities to create and maintain such an association table.
3920 The <type>macaddr</type> type also supports the standard relational
3921 operators (<literal>></literal>, <literal><=</literal>, etc.) for
3922 lexicographical ordering.
3928 <sect1 id="functions-sequence">
3929 <title>Sequence-Manipulation Functions</title>
3932 <primary>sequences</primary>
3935 <primary>nextval</primary>
3938 <primary>currval</primary>
3941 <primary>setval</primary>
3945 <title>Sequence Functions</title>
3948 <row><entry>Function</entry> <entry>Returns</entry> <entry>Description</entry></row>
3953 <entry><function>nextval</function>(<type>text</type>)</entry>
3954 <entry><type>bigint</type></entry>
3955 <entry>Advance sequence and return new value</entry>
3958 <entry><function>currval</function>(<type>text</type>)</entry>
3959 <entry><type>bigint</type></entry>
3960 <entry>Return value most recently obtained with <function>nextval</function></entry>
3963 <entry><function>setval</function>(<type>text</type>,<type>bigint</type>)</entry>
3964 <entry><type>bigint</type></entry>
3965 <entry>Set sequence's current value</entry>
3968 <entry><function>setval</function>(<type>text</type>,<type>bigint</type>,<type>boolean</type>)</entry>
3969 <entry><type>bigint</type></entry>
3970 <entry>Set sequence's current value and <literal>is_called</literal> flag</entry>
3977 This section describes <productname>PostgreSQL</productname>'s functions
3978 for operating on <firstterm>sequence objects</firstterm>.
3979 Sequence objects (also called sequence generators or
3980 just sequences) are special single-row tables created with
3981 <command>CREATE SEQUENCE</command>. A sequence object is usually used to
3982 generate unique identifiers for rows of a table. The sequence functions
3983 provide simple, multiuser-safe methods for obtaining successive
3984 sequence values from sequence objects.
3988 For largely historical reasons, the sequence to be operated on by
3989 a sequence-function call is specified by a text-string argument.
3990 To achieve some compatibility with the handling of ordinary SQL
3991 names, the sequence functions convert their argument to lower case
3992 unless the string is double-quoted. Thus
3994 nextval('foo') <lineannotation>operates on sequence </><literal>foo</literal>
3995 nextval('FOO') <lineannotation>operates on sequence </><literal>foo</literal>
3996 nextval('"Foo"') <lineannotation>operates on sequence </><literal>Foo</literal>
3998 The sequence name can be schema-qualified if necessary:
4000 nextval('myschema.foo') <lineannotation>operates on </><literal>myschema.foo</literal>
4001 nextval('"myschema".foo') <lineannotation>same as above</lineannotation>
4002 nextval('foo') <lineannotation>searches search path for
4003 </><literal>foo</literal>
4005 Of course, the text argument can be the result of an expression,
4006 not only a simple literal, which is occasionally useful.
4010 The available sequence functions are:
4014 <term><function>nextval</function></term>
4017 Advance the sequence object to its next value and return that
4018 value. This is done atomically: even if multiple server processes
4019 execute <function>nextval</function> concurrently, each will safely receive
4020 a distinct sequence value.
4026 <term><function>currval</function></term>
4029 Return the value most recently obtained by <function>nextval</function>
4030 for this sequence in the current server process. (An error is
4031 reported if <function>nextval</function> has never been called for this
4032 sequence in this process.) Notice that because this is returning
4033 a process-local value, it gives a predictable answer even if other
4034 server processes are executing <function>nextval</function> meanwhile.
4040 <term><function>setval</function></term>
4043 Reset the sequence object's counter value. The two-parameter
4044 form sets the sequence's <literal>last_value</literal> field to the specified
4045 value and sets its <literal>is_called</literal> field to <literal>true</literal>,
4046 meaning that the next <function>nextval</function> will advance the sequence
4047 before returning a value. In the three-parameter form,
4048 <literal>is_called</literal> may be set either <literal>true</literal> or
4049 <literal>false</literal>. If it's set to <literal>false</literal>,
4050 the next <function>nextval</function> will return exactly the specified
4051 value, and sequence advancement commences with the following
4052 <function>nextval</function>. For example,
4057 SELECT setval('foo', 42); <lineannotation>Next nextval() will return 43</lineannotation>
4058 SELECT setval('foo', 42, true); <lineannotation>Same as above</lineannotation>
4059 SELECT setval('foo', 42, false); <lineannotation>Next nextval() will return 42</lineannotation>
4064 The result returned by <function>setval</function> is just the value of its
4074 To avoid blocking of concurrent transactions that obtain numbers from the
4075 same sequence, a <function>nextval</function> operation is never rolled back;
4076 that is, once a value has been fetched it is considered used, even if the
4077 transaction that did the <function>nextval</function> later aborts. This means
4078 that aborted transactions may leave unused <quote>holes</quote> in the
4079 sequence of assigned values. <function>setval</function> operations are never
4080 rolled back, either.
4085 If a sequence object has been created with default parameters,
4086 <function>nextval()</function> calls on it will return successive values
4087 beginning with one. Other behaviors can be obtained by using
4088 special parameters in the <command>CREATE SEQUENCE</command> command;
4089 see its command reference page for more information.
4095 <sect1 id="functions-conditional">
4096 <title>Conditional Expressions</title>
4099 <primary>case</primary>
4103 <primary>conditionals</primary>
4107 This section describes the <acronym>SQL</acronym>-compliant conditional expressions
4108 available in <productname>PostgreSQL</productname>.
4113 If your needs go beyond the capabilities of these conditional
4114 expressions you might want to consider writing a stored procedure
4115 in a more expressive programming language.
4119 <bridgehead renderas="sect2">CASE</bridgehead>
4122 CASE WHEN <replaceable>condition</replaceable> THEN <replaceable>result</replaceable>
4123 <optional>WHEN ...</optional>
4124 <optional>ELSE <replaceable>result</replaceable></optional>
4129 The <acronym>SQL</acronym> <token>CASE</token> expression is a
4130 generic conditional expression, similar to if/else statements in
4131 other languages. <token>CASE</token> clauses can be used wherever
4132 an expression is valid. <replaceable>condition</replaceable> is an
4133 expression that returns a <type>boolean</type> result. If the result is true
4134 then the value of the <token>CASE</token> expression is
4135 <replaceable>result</replaceable>. If the result is false any
4136 subsequent <token>WHEN</token> clauses are searched in the same
4137 manner. If no <token>WHEN</token>
4138 <replaceable>condition</replaceable> is true then the value of the
4139 case expression is the <replaceable>result</replaceable> in the
4140 <token>ELSE</token> clause. If the <token>ELSE</token> clause is
4141 omitted and no condition matches, the result is NULL.
4148 <prompt>=></prompt> <userinput>SELECT * FROM test;</userinput>
4157 <prompt>=></prompt> <userinput>SELECT a,
4158 CASE WHEN a=1 THEN 'one'
4162 FROM test;</userinput>
4175 The data types of all the <replaceable>result</replaceable>
4176 expressions must be coercible to a single output type.
4177 See <xref linkend="typeconv-union-case"> for more detail.
4181 CASE <replaceable>expression</replaceable>
4182 WHEN <replaceable>value</replaceable> THEN <replaceable>result</replaceable>
4183 <optional>WHEN ...</optional>
4184 <optional>ELSE <replaceable>result</replaceable></optional>
4189 This <quote>simple</quote> <token>CASE</token> expression is a
4190 specialized variant of the general form above. The
4191 <replaceable>expression</replaceable> is computed and compared to
4192 all the <replaceable>value</replaceable>s in the
4193 <token>WHEN</token> clauses until one is found that is equal. If
4194 no match is found, the <replaceable>result</replaceable> in the
4195 <token>ELSE</token> clause (or NULL) is returned. This is similar
4196 to the <function>switch</function> statement in C.
4201 The example above can be written using the simple
4202 <token>CASE</token> syntax:
4204 <prompt>=></prompt> <userinput>SELECT a,
4205 CASE a WHEN 1 THEN 'one'
4209 FROM test;</userinput>
4221 <bridgehead renderas="sect2">COALESCE</bridgehead>
4224 <function>COALESCE</function>(<replaceable>value</replaceable><optional
4229 The <function>COALESCE</function> function returns the first of its
4230 arguments that is not NULL. This is often useful to substitute a
4231 default value for NULL values when data is retrieved for display,
4234 SELECT COALESCE(description, short_description, '(none)') ...
4238 <bridgehead renderas="sect2">NULLIF</bridgehead>
4241 <primary>nullif</primary>
4245 <function>NULLIF</function>(<replaceable>value1</replaceable>,
4246 <replaceable>value2</replaceable>)
4250 The <function>NULLIF</function> function returns NULL if and only
4251 if <replaceable>value1</replaceable> and
4252 <replaceable>value2</replaceable> are equal. Otherwise it returns
4253 <replaceable>value1</replaceable>. This can be used to perform the
4254 inverse operation of the <function>COALESCE</function> example
4257 SELECT NULLIF(value, '(none)') ...
4263 <function>COALESCE</function> and <function>NULLIF</function> are
4264 just shorthand for <token>CASE</token> expressions. They are actually
4265 converted into <token>CASE</token> expressions at a very early stage
4266 of processing, and subsequent processing thinks it is dealing with
4267 <token>CASE</token>. Thus an incorrect <function>COALESCE</function> or
4268 <function>NULLIF</function> usage may draw an error message that
4269 refers to <token>CASE</token>.
4276 <sect1 id="functions-misc">
4277 <title>Miscellaneous Functions</title>
4280 <title>Session Information Functions</title>
4283 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4288 <entry><function>current_user</function></entry>
4289 <entry><type>name</type></entry>
4290 <entry>user name of current execution context</entry>
4293 <entry><function>session_user</function></entry>
4294 <entry><type>name</type></entry>
4295 <entry>session user name</entry>
4298 <entry><function>user</function></entry>
4299 <entry><type>name</type></entry>
4300 <entry>equivalent to <function>current_user</function></entry>
4303 <entry><function>current_schema()</function></entry>
4304 <entry><type>name</type></entry>
4305 <entry>name of current schema</entry>
4308 <entry><function>current_schemas()</function></entry>
4309 <entry><type>name[]</type></entry>
4310 <entry>names of schemas in search path</entry>
4316 <indexterm zone="functions-misc">
4317 <primary>user</primary>
4318 <secondary>current</secondary>
4321 <indexterm zone="functions-misc">
4322 <primary>schema</primary>
4323 <secondary>current</secondary>
4326 <indexterm zone="functions-misc">
4327 <primary>search path</primary>
4328 <secondary>current</secondary>
4332 The <function>session_user</function> is the user that initiated a
4333 database connection; it is fixed for the duration of that
4334 connection. The <function>current_user</function> is the user identifier
4335 that is applicable for permission checking. Normally, it is equal
4336 to the session user, but it changes during the execution of
4337 functions with the attribute <literal>SECURITY DEFINER</literal>.
4338 In Unix parlance, the session user is the <quote>real user</quote> and
4339 the current user is the <quote>effective user</quote>.
4344 <function>current_user</function>, <function>session_user</function>, and
4345 <function>user</function> have special syntactic status in <acronym>SQL</acronym>:
4346 they must be called without trailing parentheses.
4351 <title>Deprecated</title>
4353 The function <function>getpgusername()</function> is an obsolete equivalent
4354 of <function>current_user</function>.
4359 <function>current_schema</function> returns the name of the schema that is
4360 at the front of the search path (or NULL if the search path is
4361 empty). This is the schema that will be used for any tables or
4362 other named objects that are created without specifying a target schema.
4363 <function>current_schemas</function> returns an array of the names of all
4364 schemas presently in the search path. Note that these functions show
4365 only schemas that are explicitly part of the path; when a system schema
4366 is being searched implicitly, it is not listed.
4370 <title>System Information Functions</title>
4373 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4378 <entry><function>version</function></entry>
4379 <entry><type>text</type></entry>
4380 <entry>PostgreSQL version information</entry>
4386 <indexterm zone="functions-misc">
4387 <primary>version</primary>
4391 <function>version()</function> returns a string describing the PostgreSQL
4396 <title>Access Privilege Inquiry Functions</title>
4399 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4404 <entry><function>has_table_privilege</function>(<parameter>user</parameter>,
4405 <parameter>table</parameter>,
4406 <parameter>access</parameter>)
4408 <entry><type>boolean</type></entry>
4409 <entry>does user have access to table</entry>
4412 <entry><function>has_table_privilege</function>(<parameter>table</parameter>,
4413 <parameter>access</parameter>)
4415 <entry><type>boolean</type></entry>
4416 <entry>does current user have access to table</entry>
4422 <indexterm zone="functions-misc">
4423 <primary>has_table_privilege</primary>
4427 <function>has_table_privilege</function> determines whether a user
4428 can access a table in a particular way. The user can be
4429 specified by name or by ID
4430 (<classname>pg_user</classname>.<structfield>usesysid</structfield>), or if the argument is
4432 <function>current_user</function> is assumed. The table can be specified
4433 by name or by OID. (Thus, there are actually six variants of
4434 <function>has_table_privilege</function>, which can be distinguished by
4435 the number and types of their arguments.) When specifying by name,
4436 the name can be schema-qualified if necessary.
4437 The desired access type
4438 is specified by a text string, which must evaluate to one of the
4439 values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>,
4440 <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, or
4441 <literal>TRIGGER</literal>. (Case of the string is not significant, however.)
4444 SELECT has_table_privilege('myschema.mytable', 'select');
4449 <title>Catalog Information Functions</title>
4452 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4457 <entry><function>pg_get_viewdef</function>(<parameter>viewname</parameter>)</entry>
4458 <entry><type>text</type></entry>
4459 <entry>Get CREATE VIEW command for view</entry>
4462 <entry><function>pg_get_viewdef</function>(<parameter>viewOID</parameter>)</entry>
4463 <entry><type>text</type></entry>
4464 <entry>Get CREATE VIEW command for view</entry>
4467 <entry><function>pg_get_ruledef</function>(<parameter>ruleOID</parameter>)</entry>
4468 <entry><type>text</type></entry>
4469 <entry>Get CREATE RULE command for rule</entry>
4472 <entry><function>pg_get_indexdef</function>(<parameter>indexOID</parameter>)</entry>
4473 <entry><type>text</type></entry>
4474 <entry>Get CREATE INDEX command for index</entry>
4477 <entry><function>pg_get_userbyid</function>(<parameter>userid</parameter>)</entry>
4478 <entry><type>name</type></entry>
4479 <entry>Get user name given ID</entry>
4485 <indexterm zone="functions-misc">
4486 <primary>pg_get_viewdef</primary>
4489 <indexterm zone="functions-misc">
4490 <primary>pg_get_ruledef</primary>
4493 <indexterm zone="functions-misc">
4494 <primary>pg_get_indexdef</primary>
4497 <indexterm zone="functions-misc">
4498 <primary>pg_get_userbyid</primary>
4502 These functions extract information from the system catalogs.
4503 <function>pg_get_viewdef()</function>, <function>pg_get_ruledef()</function>, and
4504 <function>pg_get_indexdef()</function> respectively reconstruct the creating
4505 command for a view, rule, or index. (Note that this is a decompiled
4506 reconstruction, not the verbatim text of the command.)
4507 <function>pg_get_userbyid()</function> extracts a user's name given a
4508 <structfield>usesysid</structfield> value.
4512 <title>Comment Information Functions</title>
4515 <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
4520 <entry><function>obj_description</function>(<parameter>objectOID</parameter>, <parameter>tablename</parameter>)</entry>
4521 <entry><type>text</type></entry>
4522 <entry>Get comment for a database object</entry>
4525 <entry><function>obj_description</function>(<parameter>objectOID</parameter>)</entry>
4526 <entry><type>text</type></entry>
4527 <entry>Get comment for a database object (<emphasis>deprecated</emphasis>)</entry>
4530 <entry><function>col_description</function>(<parameter>tableOID</parameter>, <parameter>columnnumber</parameter>)</entry>
4531 <entry><type>text</type></entry>
4532 <entry>Get comment for a table column</entry>
4538 <indexterm zone="functions-misc">
4539 <primary>obj_description</primary>
4542 <indexterm zone="functions-misc">
4543 <primary>col_description</primary>
4547 These functions extract comments previously stored with the
4548 <command>COMMENT</command> command. <literal>NULL</literal> is returned if
4549 no comment can be found matching the specified parameters.
4553 The two-parameter form of <function>obj_description()</function> returns the
4554 comment for a database object specified by its OID and the name of the
4555 containing system catalog. For example,
4556 <literal>obj_description(123456,'pg_class')</literal>
4557 would retrieve the comment for a table with OID 123456.
4558 The one-parameter form of <function>obj_description()</function> requires only
4559 the object OID. It is now deprecated since there is no guarantee that
4560 OIDs are unique across different system catalogs; therefore, the wrong
4561 comment could be returned.
4565 <function>col_description()</function> returns the comment for a table column,
4566 which is specified by the OID of its table and its column number.
4567 <function>obj_description()</function> cannot be used for table columns since
4568 columns do not have OIDs of their own.
4574 <sect1 id="functions-aggregate">
4575 <title>Aggregate Functions</title>
4578 <title>Author</title>
4580 Written by Isaac Wilcox <email>isaac@azartmedia.com</email> on 2000-06-16
4585 <firstterm>Aggregate functions</firstterm> compute a single result
4586 value from a set of input values. The special syntax
4587 considerations for aggregate functions are explained in <xref
4588 linkend="syntax-aggregates">. Consult the <citetitle>PostgreSQL
4589 Tutorial</citetitle> for additional introductory information.
4592 <table tocentry="1">
4593 <title>Aggregate Functions</title>
4598 <entry>Function</entry>
4599 <entry>Description</entry>
4600 <entry>Notes</entry>
4606 <entry>avg(<replaceable class="parameter">expression</replaceable>)</entry>
4607 <entry>the average (arithmetic mean) of all input values</entry>
4610 <primary>average</primary>
4611 <secondary>function</secondary>
4613 Finding the average value is available on the following data
4614 types: <type>smallint</type>, <type>integer</type>,
4615 <type>bigint</type>, <type>real</type>, <type>double
4616 precision</type>, <type>numeric</type>, <type>interval</type>.
4617 The result is of type <type>numeric</type> for any integer type
4618 input, <type>double precision</type> for floating-point input,
4619 otherwise the same as the input data type.
4624 <entry><function>count</function>(*)</entry>
4625 <entry>number of input values</entry>
4626 <entry>The return value is of type <type>bigint</type>.</entry>
4630 <entry><function>count</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4632 Counts the input values for which the value of <replaceable
4633 class="parameter">expression</replaceable> is not NULL.
4635 <entry>The return value is of type <type>bigint</type>.</entry>
4639 <entry><function>max</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4640 <entry>the maximum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4642 Available for all numeric, string, and date/time types. The
4643 result has the same type as the input expression.
4648 <entry><function>min</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4649 <entry>the minimum value of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4651 Available for all numeric, string, and date/time types. The
4652 result has the same type as the input expression.
4657 <entry><function>stddev</function>(<replaceable
4658 class="parameter">expression</replaceable>)</entry>
4659 <entry>the sample standard deviation of the input values</entry>
4662 <primary>standard deviation</primary>
4664 Finding the standard deviation is available on the following
4665 data types: <type>smallint</type>, <type>integer</type>,
4666 <type>bigint</type>, <type>real</type>, <type>double
4667 precision</type>, <type>numeric</type>. The result is of type
4668 <type>double precision</type> for floating-point input,
4669 otherwise <type>numeric</type>.
4674 <entry><function>sum</function>(<replaceable class="parameter">expression</replaceable>)</entry>
4675 <entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>
4677 Summation is available on the following data types:
4678 <type>smallint</type>, <type>integer</type>,
4679 <type>bigint</type>, <type>real</type>, <type>double
4680 precision</type>, <type>numeric</type>, <type>interval</type>.
4681 The result is of type <type>bigint</type> for <type>smallint</type>
4682 or <type>integer</type> input, <type>numeric</type> for
4684 input, <type>double precision</type> for floating-point input,
4685 otherwise the same as the input data type.
4690 <entry><function>variance</function>(<replaceable
4691 class="parameter">expression</replaceable>)</entry>
4692 <entry>the sample variance of the input values</entry>
4695 <primary>variance</primary>
4697 The variance is the square of the standard deviation. The
4698 supported data types and result types are the same as for
4708 It should be noted that except for <function>COUNT</function>,
4709 these functions return NULL when no rows are selected. In
4710 particular, <function>SUM</function> of no rows returns NULL, not
4711 zero as one might expect. <function>COALESCE</function> may be
4712 used to substitute zero for NULL when necessary.
4718 <sect1 id="functions-subquery">
4719 <title>Subquery Expressions</title>
4722 <primary>exists</primary>
4726 <primary>in</primary>
4730 <primary>not in</primary>
4734 <primary>any</primary>
4738 <primary>all</primary>
4742 <primary>some</primary>
4746 <primary>subqueries</primary>
4750 This section describes the <acronym>SQL</acronym>-compliant subquery
4751 expressions available in <productname>PostgreSQL</productname>.
4752 All of the expression forms documented in this section return
4753 Boolean (true/false) results.
4756 <bridgehead renderas="sect2">EXISTS</bridgehead>
4759 EXISTS ( <replaceable>subquery</replaceable> )
4763 The argument of <token>EXISTS</token> is an arbitrary SELECT statement,
4764 or <firstterm>subquery</firstterm>. The
4765 subquery is evaluated to determine whether it returns any rows.
4766 If it returns at least one row, the result of <token>EXISTS</token> is
4767 TRUE; if the subquery returns no rows, the result of <token>EXISTS</token>
4772 The subquery can refer to variables from the surrounding query,
4773 which will act as constants during any one evaluation of the subquery.
4777 The subquery will generally only be executed far enough to determine
4778 whether at least one row is returned, not all the way to completion.
4779 It is unwise to write a subquery that has any side-effects (such as
4780 calling sequence functions); whether the side-effects occur or not
4781 may be difficult to predict.
4785 Since the result depends only on whether any rows are returned,
4786 and not on the contents of those rows, the output list of the
4787 subquery is normally uninteresting. A common coding convention is
4788 to write all EXISTS tests in the form
4789 <literal>EXISTS(SELECT 1 WHERE ...)</literal>. There are exceptions to
4790 this rule however, such as subqueries that use <token>INTERSECT</token>.
4794 This simple example is like an inner join on col2, but it produces at
4795 most one output row for each tab1 row, even if there are multiple matching
4798 SELECT col1 FROM tab1
4799 WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
4803 <bridgehead renderas="sect2">IN (scalar form)</bridgehead>
4806 <replaceable>expression</replaceable> IN
4807 <replaceable>ble>value</replaceable><optional>, ...</optional>)
4811 The right-hand side of this form of <token>IN</token> is a parenthesized list
4812 of scalar expressions. The result is TRUE if the left-hand expression's
4813 result is equal to any of the right-hand expressions. This is a shorthand
4817 <replaceable>expression</replaceable> = <replaceable>value1</replaceable>
4819 <replaceable>expression</replaceable> = <replaceable>value2</replaceable>
4824 Note that if the left-hand expression yields NULL, or if there are
4825 no equal right-hand values and at least one right-hand expression yields
4826 NULL, the result of the <token>IN</token> construct will be NULL, not FALSE.
4827 This is in accordance with SQL's normal rules for Boolean combinations
4833 This form of <token>IN</token> is not truly a subquery expression, but it
4834 seems best to document it in the same place as subquery <token>IN</token>.
4838 <bridgehead renderas="sect2">IN (subquery form)</bridgehead>
4841 <replaceable>expression</replaceable> IN (<replaceable>subquery</replaceable>)
4845 The right-hand side of this form of <token>IN</token> is a parenthesized
4846 subquery, which must return exactly one column. The left-hand expression
4847 is evaluated and compared to each row of the subquery result.
4848 The result of <token>IN</token> is TRUE if any equal subquery row is found.
4849 The result is FALSE if no equal row is found (including the special
4850 case where the subquery returns no rows).
4854 Note that if the left-hand expression yields NULL, or if there are
4855 no equal right-hand values and at least one right-hand row yields
4856 NULL, the result of the <token>IN</token> construct will be NULL, not FALSE.
4857 This is in accordance with SQL's normal rules for Boolean combinations
4862 As with <token>EXISTS</token>, it's unwise to assume that the subquery will
4863 be evaluated completely.
4867 (<replaceable>expression</replaceable>,
4868 <replaceable>ble>expres</replaceable><optional>nal>,</optional>nal>)
4869 IN (<replaceable>subquery</replaceable>)
4873 The right-hand side of this form of <token>IN</token> is a parenthesized
4874 subquery, which must return exactly as many columns as there are
4875 expressions in the left-hand list. The left-hand expressions are
4876 evaluated and compared row-wise to each row of the subquery result.
4877 The result of <token>IN</token> is TRUE if any equal subquery row is found.
4878 The result is FALSE if no equal row is found (including the special
4879 case where the subquery returns no rows).
4883 As usual, NULLs in the expressions or subquery rows are combined per
4884 the normal rules of SQL Boolean expressions. Two rows are considered
4885 equal if all their corresponding members are non-null and equal; the rows
4886 are unequal if any corresponding members are non-null and unequal;
4887 otherwise the result of that row comparison is unknown (NULL).
4888 If all the row results are either unequal or NULL, with at least one NULL,
4889 then the result of <token>IN</token> is NULL.
4892 <bridgehead renderas="sect2">NOT IN (scalar form)</bridgehead>
4895 <replaceable>expression</replaceable> NOT IN
4896 <replaceable>ble>value</replaceable><optional>, ...</optional>)
4900 The right-hand side of this form of <token>NOT IN</token> is a parenthesized list
4901 of scalar expressions. The result is TRUE if the left-hand expression's
4902 result is unequal to all of the right-hand expressions. This is a shorthand
4906 <replaceable>expression</replaceable> <> <replaceable>value1</replaceable>
4908 <replaceable>expression</replaceable> <> <replaceable>value2</replaceable>
4913 Note that if the left-hand expression yields NULL, or if there are
4914 no equal right-hand values and at least one right-hand expression yields
4915 NULL, the result of the <token>NOT IN</token> construct will be NULL, not TRUE
4916 as one might naively expect.
4917 This is in accordance with SQL's normal rules for Boolean combinations
4923 <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all
4924 cases. However, NULLs are much more likely to trip up the novice when
4925 working with <token>NOT IN</token> than when working with <token>IN</token>.
4926 It's best to express your condition positively if possible.
4930 <bridgehead renderas="sect2">NOT IN (subquery form)</bridgehead>
4933 <replaceable>expression</replaceable> NOT IN (<replaceable>subquery</replaceable>)
4937 The right-hand side of this form of <token>NOT IN</token> is a parenthesized
4938 subquery, which must return exactly one column. The left-hand expression
4939 is evaluated and compared to each row of the subquery result.
4940 The result of <token>NOT IN</token> is TRUE if only unequal subquery rows
4941 are found (including the special case where the subquery returns no rows).
4942 The result is FALSE if any equal row is found.
4946 Note that if the left-hand expression yields NULL, or if there are
4947 no equal right-hand values and at least one right-hand row yields
4948 NULL, the result of the <token>NOT IN</token> construct will be NULL, not TRUE.
4949 This is in accordance with SQL's normal rules for Boolean combinations
4954 As with <token>EXISTS</token>, it's unwise to assume that the subquery will
4955 be evaluated completely.
4959 (<replaceable>expression</replaceable>,
4960 <replaceable>ble>expres</replaceable><optional>nal>,</optional>nal>)
4961 NOT IN (<replaceable>subquery</replaceable>)
4965 The right-hand side of this form of <token>NOT IN</token> is a parenthesized
4966 subquery, which must return exactly as many columns as there are
4967 expressions in the left-hand list. The left-hand expressions are
4968 evaluated and compared row-wise to each row of the subquery result.
4969 The result of <token>NOT IN</token> is TRUE if only unequal subquery rows
4970 are found (including the special case where the subquery returns no rows).
4971 The result is FALSE if any equal row is found.
4975 As usual, NULLs in the expressions or subquery rows are combined per
4976 the normal rules of SQL Boolean expressions. Two rows are considered
4977 equal if all their corresponding members are non-null and equal; the rows
4978 are unequal if any corresponding members are non-null and unequal;
4979 otherwise the result of that row comparison is unknown (NULL).
4980 If all the row results are either unequal or NULL, with at least one NULL,
4981 then the result of <token>NOT IN</token> is NULL.
4984 <bridgehead renderas="sect2">ANY</bridgehead>
4987 <replaceable>expression</replaceable>
4988 <replaceable>ble>oper</replaceable>ble> ANY (<replaceable>subquery</replaceable>)
4989 <replaceable>expression</replaceable>
4990 <replaceable>ble>oper</replaceable>ble> SOME (<replaceable>subquery</replaceable>)
4994 The right-hand side of this form of <token>ANY</token> is a parenthesized
4995 subquery, which must return exactly one column. The left-hand expression
4996 is evaluated and compared to each row of the subquery result using the
4997 given <replaceable>operator</replaceable>, which must yield a Boolean
4999 The result of <token>ANY</token> is TRUE if any true result is obtained.
5000 The result is FALSE if no true result is found (including the special
5001 case where the subquery returns no rows).
5005 <token>SOME</token> is a synonym for <token>ANY</token>.
5006 <token>IN</token> is equivalent to <literal>= ANY</literal>.
5010 Note that if there are no successes and at least one right-hand row yields
5011 NULL for the operator's result, the result of the <token>ANY</token> construct
5012 will be NULL, not FALSE.
5013 This is in accordance with SQL's normal rules for Boolean combinations
5018 As with <token>EXISTS</token>, it's unwise to assume that the subquery will
5019 be evaluated completely.
5023 (<replaceable>expression</replaceable>,
5024 <replaceable>ble>expres</replaceable><optional>nal>,</optional>optiona<replaceable>aceable></replaceable>aceable> ANY (<replaceable>subquery</replaceable>)
5025 (<replaceable>expression</replaceable>,
5026 <replaceable>ble>expres</replaceable><optional>nal>,</optional>optiona<replaceable>aceable></replaceable>aceable> SOME (<replaceable>subquery</replaceable>)
5030 The right-hand side of this form of <token>ANY</token> is a parenthesized
5031 subquery, which must return exactly as many columns as there are
5032 expressions in the left-hand list. The left-hand expressions are
5033 evaluated and compared row-wise to each row of the subquery result,
5034 using the given <replaceable>operator</replaceable>. Presently,
5035 only <literal>=</literal> and <literal><></literal> operators are allowed
5036 in row-wise <token>ANY</token> queries.
5037 The result of <token>ANY</token> is TRUE if any equal or unequal row is
5038 found, respectively.
5039 The result is FALSE if no such row is found (including the special
5040 case where the subquery returns no rows).
5044 As usual, NULLs in the expressions or subquery rows are combined per
5045 the normal rules of SQL Boolean expressions. Two rows are considered
5046 equal if all their corresponding members are non-null and equal; the rows
5047 are unequal if any corresponding members are non-null and unequal;
5048 otherwise the result of that row comparison is unknown (NULL).
5049 If there is at least one NULL row result, then the result of <token>ANY</token>
5050 cannot be FALSE; it will be TRUE or NULL.
5053 <bridgehead renderas="sect2">ALL</bridgehead>
5056 <replaceable>expression</replaceable>
5057 <replaceable>ble>oper</replaceable>ble> ALL (<replaceable>subquery</replaceable>)
5061 The right-hand side of this form of <token>ALL</token> is a parenthesized
5062 subquery, which must return exactly one column. The left-hand expression
5063 is evaluated and compared to each row of the subquery result using the
5064 given <replaceable>operator</replaceable>, which must yield a Boolean
5066 The result of <token>ALL</token> is TRUE if all rows yield TRUE
5067 (including the special case where the subquery returns no rows).
5068 The result is FALSE if any false result is found.
5072 <token>NOT IN</token> is equivalent to <literal><> ALL</literal>.
5076 Note that if there are no failures but at least one right-hand row yields
5077 NULL for the operator's result, the result of the <token>ALL</token> construct
5078 will be NULL, not TRUE.
5079 This is in accordance with SQL's normal rules for Boolean combinations
5084 As with <token>EXISTS</token>, it's unwise to assume that the subquery will
5085 be evaluated completely.
5089 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)
5093 The right-hand side of this form of <token>ALL</token> is a parenthesized
5094 subquery, which must return exactly as many columns as there are
5095 expressions in the left-hand list. The left-hand expressions are
5096 evaluated and compared row-wise to each row of the subquery result,
5097 using the given <replaceable>operator</replaceable>. Presently,
5098 only <literal>=</literal> and <literal><></literal> operators are allowed
5099 in row-wise <token>ALL</token> queries.
5100 The result of <token>ALL</token> is TRUE if all subquery rows are equal
5101 or unequal, respectively (including the special
5102 case where the subquery returns no rows).
5103 The result is FALSE if any row is found to be unequal or equal,
5108 As usual, NULLs in the expressions or subquery rows are combined per
5109 the normal rules of SQL Boolean expressions. Two rows are considered
5110 equal if all their corresponding members are non-null and equal; the rows
5111 are unequal if any corresponding members are non-null and unequal;
5112 otherwise the result of that row comparison is unknown (NULL).
5113 If there is at least one NULL row result, then the result of <token>ALL</token>
5114 cannot be TRUE; it will be FALSE or NULL.
5117 <bridgehead renderas="sect2">Row-wise comparison</bridgehead>
5120 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>)
5121 (<replaceable>expression</replaceable>, <replaceable>expression</replaceable><optional>, ...</optional>) <replaceable>operator</replaceable> (<replaceable>expression</replaceable> <replaceable>expression</replaceable><optional>, ...</optional>)
5125 The left-hand side is a list of scalar expressions. The right-hand side
5126 can be either a list of scalar expressions of the same length, or a
5127 parenthesized subquery, which must return exactly as many columns as there
5128 are expressions on the left-hand side. Furthermore, the subquery cannot
5129 return more than one row. (If it returns zero rows, the result is taken to
5130 be NULL.) The left-hand side is evaluated and compared row-wise to the
5131 single subquery result row, or to the right-hand expression list.
5132 Presently, only <literal>=</literal> and <literal><></literal> operators are allowed
5133 in row-wise comparisons.
5134 The result is TRUE if the two rows are equal or unequal, respectively.
5138 As usual, NULLs in the expressions or subquery rows are combined per
5139 the normal rules of SQL Boolean expressions. Two rows are considered
5140 equal if all their corresponding members are non-null and equal; the rows
5141 are unequal if any corresponding members are non-null and unequal;
5142 otherwise the result of the row comparison is unknown (NULL).
5149 <!-- Keep this comment at the end of the file
5154 sgml-minimize-attributes:nil
5155 sgml-always-quote-attributes:t
5158 sgml-parent-document:nil
5159 sgml-default-dtd-file:"./reference.ced"
5160 sgml-exposed-tags:nil
5161 sgml-local-catalogs:("/usr/lib/sgml/catalog")
5162 sgml-local-ecat-files:nil