func.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.132 2002/11/23 04:04:43 momjian Exp $
PostgreSQL documentation
-->

 <chapter id="functions">
  <title>Functions and Operators</title>

  <indexterm zone="functions">
   <primary>functions</primary>
  </indexterm>

  <indexterm zone="functions">
   <primary>operators</primary>
  </indexterm>

  <para>
   <productname>PostgreSQL</productname> provides a large number of
   functions and operators for the built-in data types.  Users can also
   define their own functions and operators, as described in the
   &cite-programmer;.  The
   <application>psql</application> commands <command>\df</command> and
   <command>\do</command> can be used to show the list of all actually
   available functions and operators, respectively.
  </para>

  <para>
   If you are concerned about portability then take note that most of
   the functions and operators described in this chapter, with the
   exception of the most trivial arithmetic and comparison operators
   and some explicitly marked functions, are not specified by the
   <acronym>SQL</acronym>
   standard. Some of this extended functionality is present in other
   <acronym>SQL</acronym> implementations, and in many cases this
   functionality is compatible and consistent between various products.
  </para>


  <sect1 id="functions-logical">
   <title>Logical Operators</title>

   <indexterm zone="functions-logical">
    <primary>operators</primary>
    <secondary>logical</secondary>
   </indexterm>

   <indexterm>
    <primary>Boolean</primary>
    <secondary>operators</secondary>
    <see>operators, logical</see>
   </indexterm>

   <para>
    The usual logical operators are available:

    <indexterm>
     <primary>and</primary>
     <secondary>operator</secondary>
    </indexterm>

    <indexterm>
     <primary>or</primary>
     <secondary>operator</secondary>
    </indexterm>

    <indexterm>
     <primary>not</primary>
     <secondary>operator</secondary>
    </indexterm>

    <simplelist>
     <member>AND</member>
     <member>OR</member>
     <member>NOT</member>
    </simplelist>

    <acronym>SQL</acronym> uses a three-valued Boolean logic where the null value represents
    <quote>unknown</quote>.  Observe the following truth tables:

    <informaltable>
     <tgroup cols="4">
      <thead>
       <row>
        <entry><replaceable>a</replaceable></entry>
        <entry><replaceable>b</replaceable></entry>
        <entry><replaceable>a</replaceable> AND <replaceable>b</replaceable></entry>
        <entry><replaceable>a</replaceable> OR <replaceable>b</replaceable></entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>TRUE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>TRUE</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
       </row>

       <row>
        <entry>FALSE</entry>
        <entry>NULL</entry>
        <entry>FALSE</entry>
        <entry>NULL</entry>
       </row>

       <row>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
       </row>
      </tbody>
     </tgroup>
    </informaltable>

    <informaltable>
     <tgroup cols="2">
      <thead>
       <row>
        <entry><replaceable>a</replaceable></entry>
        <entry>NOT <replaceable>a</replaceable></entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry>TRUE</entry>
        <entry>FALSE</entry>
       </row>

       <row>
        <entry>FALSE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>NULL</entry>
        <entry>NULL</entry>
       </row>
      </tbody>
     </tgroup>
    </informaltable>
   </para>
  </sect1>

  <sect1 id="functions-comparison">
   <title>Comparison Operators</title>

   <indexterm zone="functions-comparison">
    <primary>comparison</primary>
    <secondary>operators</secondary>
   </indexterm>

   <para>
    The usual comparison operators are available, shown in <xref
    linkend="functions-comparison-table">.
   </para>

   <table id="functions-comparison-table">
    <title>Comparison Operators</title>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Operator</entry>
       <entry>Description</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry> <literal>&lt;</literal> </entry>
       <entry>less than</entry>
      </row>

      <row>
       <entry> <literal>&gt;</literal> </entry>
       <entry>greater than</entry>
      </row>

      <row>
       <entry> <literal>&lt;=</literal> </entry>
       <entry>less than or equal to</entry>
      </row>

      <row>
       <entry> <literal>&gt;=</literal> </entry>
       <entry>greater than or equal to</entry>
      </row>

      <row>
       <entry> <literal>=</literal> </entry>
       <entry>equal</entry>
      </row>

      <row>
       <entry> <literal>&lt;&gt;</literal> or <literal>!=</literal> </entry>
       <entry>not equal</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <note>
    <para>
     The <literal>!=</literal> operator is converted to
     <literal>&lt;&gt;</literal> in the parser stage.  It is not
     possible to implement <literal>!=</literal> and
     <literal>&lt;&gt;</literal> operators that do different things.
    </para>
   </note>

   <para>
    Comparison operators are available for all data types where this
    makes sense.  All comparison operators are binary operators that
    return values of type <type>boolean</type>; expressions like
    <literal>1 &lt; 2 &lt; 3</literal> are not valid (because there is
    no <literal>&lt;</literal> operator to compare a Boolean value with
    <literal>3</literal>).
   </para>

   <para>
    <indexterm>
     <primary>between</primary>
    </indexterm>
    In addition to the comparison operators, the special
    <token>BETWEEN</token> construct is available.
<synopsis>
<replaceable>a</replaceable> BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
</synopsis>
    is equivalent to
<synopsis>
<replaceable>a</replaceable> &gt;= <replaceable>x</replaceable> AND <replaceable>a</replaceable> &lt;= <replaceable>y</replaceable>
</synopsis>
    Similarly,
<synopsis>
<replaceable>a</replaceable> NOT BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
</synopsis>
    is equivalent to
<synopsis>
<replaceable>a</replaceable> &lt; <replaceable>x</replaceable> OR <replaceable>a</replaceable> &gt; <replaceable>y</replaceable>
</synopsis>
    There is no difference between the two respective forms apart from
    the <acronym>CPU</acronym> cycles required to rewrite the first one
    into the second one internally.
   </para>

   <para>
    To check whether a value is or is not null, use the constructs
<synopsis>
<replaceable>expression</replaceable> IS NULL
<replaceable>expression</replaceable> IS NOT NULL
</synopsis>
    or the equivalent, but nonstandard, constructs
<synopsis>
<replaceable>expression</replaceable> ISNULL
<replaceable>expression</replaceable> NOTNULL
</synopsis>
   </para>

   <para>
    Do <emphasis>not</emphasis> write
    <literal><replaceable>expression</replaceable> = NULL</literal>
    because <literal>NULL</> is not <quote>equal to</quote>
    <literal>NULL</>.  (The null value represents an unknown value,
    and it is not known whether two unknown values are equal.)
   </para>

   <para>
    Some applications may (incorrectly) require that
    <literal><replaceable>expression</replaceable> = NULL</literal>
    returns true if <replaceable>expression</replaceable> evaluates to
    the null value.  To support these applications, the run-time option
    <varname>transform_null_equals</varname> can be turned on (e.g.,
    <literal>SET transform_null_equals TO ON;</literal>).
    <productname>PostgreSQL</productname> will then convert
    <literal>x = NULL</literal> clauses to
    <literal>x IS NULL</literal>.  This was 
    the default behavior in releases 6.5 through 7.1.
   </para>

   <para>
    Boolean values can also be tested using the constructs
<synopsis>
<replaceable>expression</replaceable> IS TRUE
<replaceable>expression</replaceable> IS NOT TRUE
<replaceable>expression</replaceable> IS FALSE
<replaceable>expression</replaceable> IS NOT FALSE
<replaceable>expression</replaceable> IS UNKNOWN
<replaceable>expression</replaceable> IS NOT UNKNOWN
</synopsis>
    These are similar to <literal>IS NULL</literal> in that they will
    always return true or false, never a null value, even when the operand is null.
    A null input is treated as the logical value <quote>unknown</>.
   </para>
  </sect1>

  <sect1 id="functions-math">
   <title>Mathematical Functions and Operators</title>

   <para>
    Mathematical operators are provided for many
    <productname>PostgreSQL</productname> types. For types without
    common mathematical conventions for all possible permutations 
    (e.g., date/time types) we
    describe the actual behavior in subsequent sections.
   </para>

   <para>
    <xref linkend="functions-math-op-table"> shows the available mathematical operators.
   </para>

   <table id="functions-math-op-table">
    <title>Mathematical Operators</title>

    <tgroup cols="4">
     <thead>
      <row>
       <entry>Name</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry> <literal>+</literal> </entry>
       <entry>addition</entry>
       <entry>2 + 3</entry>
       <entry>5</entry>
      </row>

      <row>
       <entry> <literal>-</literal> </entry>
       <entry>subtraction</entry>
       <entry>2 - 3</entry>
       <entry>-1</entry>
      </row>

      <row>
       <entry> <literal>*</literal> </entry>
       <entry>multiplication</entry>
       <entry>2 * 3</entry>
       <entry>6</entry>
      </row>

      <row>
       <entry> <literal>/</literal> </entry>
       <entry>division (integer division truncates results)</entry>
       <entry>4 / 2</entry>
       <entry>2</entry>
      </row>

      <row>
       <entry> <literal>%</literal> </entry>
       <entry>modulo (remainder)</entry>
       <entry>5 % 4</entry>
       <entry>1</entry>
      </row>

      <row>
       <entry> <literal>^</literal> </entry>
       <entry>exponentiation</entry>
       <entry>2.0 ^ 3.0</entry>
       <entry>8</entry>
      </row>

      <row>
       <entry> <literal>|/</literal> </entry>
       <entry>square root</entry>
       <entry>|/ 25.0</entry>
       <entry>5</entry>
      </row>

      <row>
       <entry> <literal>||/</literal> </entry>
       <entry>cube root</entry>
       <entry>||/ 27.0</entry>
       <entry>3</entry>
      </row>

      <row>
       <entry> <literal>!</literal> </entry>
       <entry>factorial</entry>
       <entry>5 !</entry>
       <entry>120</entry>
      </row>

      <row>
       <entry> <literal>!!</literal> </entry>
       <entry>factorial (prefix operator)</entry>
       <entry>!! 5</entry>
       <entry>120</entry>
      </row>

      <row>
       <entry> <literal>@</literal> </entry>
       <entry>absolute value</entry>
       <entry>@ -5.0</entry>
       <entry>5</entry>
      </row>

      <row>
       <entry> <literal>&amp;</literal> </entry>
       <entry>binary AND</entry>
       <entry>91 & 15</entry>
       <entry>11</entry>
      </row>

      <row>
       <entry> <literal>|</literal> </entry>
       <entry>binary OR</entry>
       <entry>32 | 3</entry>
       <entry>35</entry>
      </row>

      <row>
       <entry> <literal>#</literal> </entry>
       <entry>binary XOR</entry>
       <entry>17 # 5</entry>
       <entry>20</entry>
      </row>

      <row>
       <entry> <literal>~</literal> </entry>
       <entry>binary NOT</entry>
       <entry>~1</entry>
       <entry>-2</entry>
      </row>

      <row>
       <entry> &lt;&lt; </entry>
       <entry>binary shift left</entry>
       <entry>1 &lt;&lt; 4</entry>
       <entry>16</entry>
      </row>

      <row>
       <entry> &gt;&gt; </entry>
       <entry>binary shift right</entry>
       <entry>8 &gt;&gt; 2</entry>
       <entry>2</entry>
      </row>

     </tbody>
    </tgroup>
   </table>

   <para>
    The <quote>binary</quote> operators are also available for the bit
    string types <type>BIT</type> and <type>BIT VARYING</type>, as
    shown in <xref linkend="functions-math-bit-table">.
    Bit string arguments to <literal>&</literal>, <literal>|</literal>,
    and <literal>#</literal> must be of equal length.  When bit
    shifting, the original length of the string is preserved, as shown in the table.
   </para>

    <table id="functions-math-bit-table">
     <title>Bit String Binary Operators</title>

     <tgroup cols="2">
      <thead>
       <row>
        <entry>Example</entry>
        <entry>Result</entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry>B'10001' & B'01101'</entry>
        <entry>00001</entry>
       </row>
       <row>
        <entry>B'10001' | B'01101'</entry>
        <entry>11101</entry>
       </row>
       <row>
        <entry>B'10001' # B'01101'</entry>
        <entry>11110</entry>
       </row>
       <row>
        <entry>~ B'10001'</entry>
        <entry>01110</entry>
       </row>
       <row>
        <entry>B'10001' &lt;&lt; 3</entry>
        <entry>01000</entry>
       </row>
       <row>
        <entry>B'10001' &gt;&gt; 2</entry>
        <entry>00100</entry>
       </row>
      </tbody>
     </tgroup>
    </table>

  <para>
   <xref linkend="functions-math-func-table"> shows the available
   mathematical functions.  In the table, <literal>dp</literal>
   indicates <type>double precision</type>.  The functions
   <function>exp</function>, <function>ln</function>,
   <function>log</function>, <function>pow</function>,
   <function>round</function> (1 argument), <function>sqrt</function>,
   and <function>trunc</function> (1 argument) are also available for
   the type <type>numeric</type> in place of <type>double
   precision</type>.  Functions returning a <type>numeric</type>
   result take <type>numeric</type> input arguments, unless otherwise
   specified.  Many of these functions are implemented on top of the
   host system's C library; accuracy and behavior in boundary cases
   could therefore vary depending on the host system.
  </para>

   <table id="functions-math-func-table">
    <title>Mathematical Functions</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><function>abs</function>(<replaceable>x</replaceable>)</entry>
       <entry>(same as <replaceable>x</>)</entry>
       <entry>absolute value</entry>
       <entry><literal>abs(-17.4)</literal></entry>
       <entry>17.4</entry>
      </row>

      <row>
       <entry><function>cbrt</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>cube root</entry>
       <entry><literal>cbrt(27.0)</literal></entry>
       <entry>3</entry>
      </row>

      <row>
       <entry><function>ceil</function>(<type>numeric</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>smallest integer not less than argument</entry>
       <entry><literal>ceil(-42.8)</literal></entry>
       <entry>-42</entry>
      </row>

      <row>
       <entry><function>degrees</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>radians to degrees</entry>
       <entry><literal>degrees(0.5)</literal></entry>
       <entry>28.6478897565412</entry>
      </row>

      <row>
       <entry><function>exp</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>exponential</entry>
       <entry><literal>exp(1.0)</literal></entry>
       <entry>2.71828182845905</entry>
      </row>

      <row>
       <entry><function>floor</function>(<type>numeric</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>largest integer not greater than argument</entry>
       <entry><literal>floor(-42.8)</literal></entry>
       <entry>-43</entry>
      </row>

      <row>
       <entry><function>ln</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>natural logarithm</entry>
       <entry><literal>ln(2.0)</literal></entry>
       <entry>0.693147180559945</entry>
      </row>

      <row>
       <entry><function>log</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>base 10 logarithm</entry>
       <entry><literal>log(100.0)</literal></entry>
       <entry>2</entry>
      </row>

      <row>
       <entry><function>log</function>(<parameter>b</parameter> <type>numeric</type>,
        <parameter>x</parameter> <type>numeric</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>logarithm to base <parameter>b</parameter></entry>
       <entry><literal>log(2.0, 64.0)</literal></entry>
       <entry>6.0000000000</entry>
      </row>

      <row>
       <entry><function>mod</function>(<parameter>y</parameter>,
        <parameter>x</parameter>)</entry>
       <entry>(same as argument types)</entry>
       <entry>remainder of <parameter>y</parameter>/<parameter>x</parameter></entry>
       <entry><literal>mod(9,4)</literal></entry>
       <entry>1</entry>
      </row>

      <row>
       <entry><function>pi</function>()</entry>
       <entry><type>dp</type></entry>
       <entry><quote>Pi</quote> constant</entry>
       <entry><literal>pi()</literal></entry>
       <entry>3.14159265358979</entry>
      </row>

      <row>
       <entry><function>pow</function>(<parameter>e</parameter> <type>dp</type>,
        <parameter>n</parameter> <type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>raise a number to exponent <parameter>e</parameter></entry>
       <entry><literal>pow(9.0, 3.0)</literal></entry>
       <entry>729</entry>
      </row>

      <row>
       <entry><function>radians</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>degrees to radians</entry>
       <entry><literal>radians(45.0)</literal></entry>
       <entry>0.785398163397448</entry>
      </row>

      <row>
       <entry><function>random</function>()</entry>
       <entry><type>dp</type></entry>
       <entry>value between 0.0 to 1.0</entry>
       <entry><literal>random()</literal></entry>
       <entry></entry>
      </row>

      <row>
       <entry><function>round</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>round to nearest integer</entry>
       <entry><literal>round(42.4)</literal></entry>
       <entry>42</entry>
      </row>

      <row>
       <entry><function>round</function>(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>round to <parameter>s</parameter> decimal places</entry>
       <entry><literal>round(42.4382, 2)</literal></entry>
       <entry>42.44</entry>
      </row>
<!--
     <row>
      <entry><function>setseed</function>(<replaceable>new-seed</replaceable>)</entry>
      <entry>set seed for subsequent random() calls</entry>
      <entry><literal>setseed(0.54823)</literal></entry>
      <entry></entry>
     </row>
-->
      <row>
       <entry><function>sign</function>(<type>numeric</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>sign of the argument (-1, 0, +1)</entry>
       <entry><literal>sign(-8.4)</literal></entry>
       <entry>-1</entry>
      </row>

      <row>
       <entry><function>sqrt</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>square root</entry>
       <entry><literal>sqrt(2.0)</literal></entry>
       <entry>1.4142135623731</entry>
      </row>

      <row>
       <entry><function>trunc</function>(<type>dp</type>)</entry>
       <entry><type>dp</type></entry>
       <entry>truncate toward zero</entry>
       <entry><literal>trunc(42.8)</literal></entry>
       <entry>42</entry>
      </row>

      <row>
       <entry><function>trunc</function>(<type>numeric</type>,
        <parameter>r</parameter> <type>integer</type>)</entry>
       <entry><type>numeric</type></entry>
       <entry>truncate to <parameter>s</parameter> decimal places</entry>
       <entry><literal>trunc(42.4382, 2)</literal></entry>
       <entry>42.43</entry>
      </row>

     </tbody>
    </tgroup>
   </table>

  <para>
   Finally, <xref linkend="functions-math-trig-table"> shows the
   available trigonometric functions.  All trigonometric functions
   have arguments and return values of type <type>double
   precision</type>.
  </para>

   <table id="functions-math-trig-table">
    <title>Trigonometric Functions</title>

    <tgroup cols="2">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Description</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><function>acos</function>(<replaceable>x</replaceable>)</entry>
       <entry>inverse cosine</entry>
      </row>

      <row>
       <entry><function>asin</function>(<replaceable>x</replaceable>)</entry>
       <entry>inverse sine</entry>
      </row>

      <row>
       <entry><function>atan</function>(<replaceable>x</replaceable>)</entry>
       <entry>inverse tangent</entry>
      </row>

      <row>
       <entry><function>atan2</function>(<replaceable>x</replaceable>,
        <replaceable>y</replaceable>)</entry>
       <entry>inverse tangent of
        <replaceable>x</replaceable>/<replaceable>y</replaceable></entry>
      </row>

      <row>
       <entry><function>cos</function>(<replaceable>x</replaceable>)</entry>
       <entry>cosine</entry>
      </row>

      <row>
       <entry><function>cot</function>(<replaceable>x</replaceable>)</entry>
       <entry>cotangent</entry>
      </row>

      <row>
       <entry><function>sin</function>(<replaceable>x</replaceable>)</entry>
       <entry>sine</entry>
      </row>

      <row>
       <entry><function>tan</function>(<replaceable>x</replaceable>)</entry>
       <entry>tangent</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  </sect1>


  <sect1 id="functions-string">
   <title>String Functions and Operators</title>

   <para>
    This section describes functions and operators for examining and
    manipulating string values.  Strings in this context include values
    of all the types <type>CHARACTER</type>, <type>CHARACTER
     VARYING</type>, and <type>TEXT</type>.  Unless otherwise noted, all
    of the functions listed below work on all of these types, but be
    wary of potential effects of the automatic padding when using the
    <type>CHARACTER</type> type.  Generally, the functions described
    here also work on data of non-string types by converting that data
    to a string representation first.  Some functions also exist
    natively for bit-string types.
   </para>

   <para>
    <acronym>SQL</acronym> defines some string functions with a special syntax where
    certain key words rather than commas are used to separate the
    arguments.  Details are in <xref linkend="functions-string-sql">.
    These functions are also implemented using the regular syntax for
    function invocation.  (See <xref linkend="functions-string-other">.)
   </para>

   <table id="functions-string-sql">
    <title><acronym>SQL</acronym> String Functions and Operators</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>  
      </row>
     </thead>

     <tbody>
      <row>
       <entry> <parameter>string</parameter> <literal>||</literal>
        <parameter>string</parameter> </entry>
       <entry> <type>text</type> </entry>
       <entry>
        String concatenation
        <indexterm>
         <primary>character strings</primary>
         <secondary>concatenation</secondary>
        </indexterm>
       </entry>
       <entry><literal>'Post' || 'greSQL'</literal></entry>
       <entry><literal>PostgreSQL</literal></entry>
      </row>

      <row>
       <entry><function>bit_length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>Number of bits in string</entry>
       <entry><literal>bit_length('jose')</literal></entry>
       <entry><literal>32</literal></entry>
      </row>

      <row>
       <entry><function>char_length</function>(<parameter>string</parameter>) or <function>character_length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>
        Number of characters in string
        <indexterm>
         <primary>character strings</primary>
         <secondary>length</secondary>
        </indexterm>
        <indexterm>
         <primary>length</primary>
         <secondary>character strings</secondary>
         <see>character strings, length</see>
        </indexterm>
       </entry>
       <entry><literal>char_length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry><function>convert</function>(<parameter>string</parameter>
       using <parameter>conversion_name</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Change encoding using specified conversion name.  Conversions
        can be defined by <command>CREATE CONVERSION</command>.  Also
        there are some pre-defined conversion names. See <xref
        linkend="conversion-names"> for available conversion
        names.
       </entry>
       <entry><literal>convert('PostgreSQL' using iso_8859_1_to_utf_8)</literal></entry>
       <entry><literal>'PostgreSQL'</literal> in Unicode (UTF-8) encoding</entry>
      </row>

      <row>
       <entry><function>lower</function>(<parameter>string</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Convert string to lower case</entry>
       <entry><literal>lower('TOM')</literal></entry>
       <entry><literal>tom</literal></entry>
      </row>

      <row>
       <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>Number of bytes in string</entry>
       <entry><literal>octet_length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry><function>overlay</function>(<parameter>string</parameter> placing <parameter>string</parameter> from <type>integer</type> <optional>for <type>integer</type></optional>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Insert substring
        <indexterm>
         <primary>overlay</primary>
        </indexterm>
       </entry>
       <entry><literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal></entry>
       <entry><literal>Thomas</literal></entry>
      </row>

      <row>
       <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>Location of specified substring</entry>
       <entry><literal>position('om' in 'Thomas')</literal></entry>
       <entry><literal>3</literal></entry>
      </row>

      <row>
       <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Thomas' from 2 for 3)</literal></entry>
       <entry><literal>hom</literal></entry>
      </row>

      <row>
       <entry><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring matching POSIX regular expression
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Thomas' from '...$')</literal></entry>
       <entry><literal>mas</literal></entry>
      </row>

      <row>
       <entry><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable> for <replaceable>escape</replaceable>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring matching SQL regular expression
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Thomas' from '%#"o_a#"_' for '#')</literal></entry>
       <entry><literal>oma</literal></entry>
      </row>

      <row>
       <entry>
        <function>trim</function>(<optional>leading | trailing | both</optional>
        <optional><parameter>characters</parameter></optional> from
        <parameter>string</parameter>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string containing only the
        <parameter>characters</parameter> (a space by default) from the
        beginning/end/both ends of the <parameter>string</parameter>
       </entry>
       <entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>
       <entry><literal>Tom</literal></entry>
      </row>

      <row>
       <entry><function>upper</function>(<parameter>string</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Convert string to upper case</entry>
       <entry><literal>upper('tom')</literal></entry>
       <entry><literal>TOM</literal></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Additional string manipulation functions are available and are
    listed in <xref linkend="functions-string-other">.  Some of them are used internally to implement the
    <acronym>SQL</acronym>-standard string functions listed in <xref linkend="functions-string-sql">.
   </para>

   <table id="functions-string-other">
    <title>Other String Functions</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><function>ascii</function>(<type>text</type>)</entry>
       <entry>integer</entry>
       <entry><acronym>ASCII</acronym> code of the first character of the argument.</entry>
       <entry><literal>ascii('x')</literal></entry>
       <entry><literal>120</literal></entry>
      </row>

      <row>
       <entry><function>btrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>trim</parameter> <type>text</type>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Remove (trim) the longest string consisting only of characters
        in <parameter>trim</parameter> from the start and end of
        <parameter>string</parameter>
       </entry>
       <entry><literal>btrim('xyxtrimyyx','xy')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><function>chr</function>(<type>integer</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Character with the given <acronym>ASCII</acronym> code</entry>
       <entry><literal>chr(65)</literal></entry>
       <entry><literal>A</literal></entry>
      </row>

      <row>
       <entry>
        <function>convert</function>(<parameter>string</parameter>
        <type>text</type>,
        <optional><parameter>src_encoding</parameter> <type>name</type>,</optional>
        <parameter>dest_encoding</parameter> <type>name</type>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Convert string to <parameter>dest_encoding</parameter>.
        The original encoding is specified by
        <parameter>src_encoding</parameter>.  If
        <parameter>src_encoding</parameter> is omitted, database
        encoding is assumed.
       </entry>
       <entry><literal>convert('text_in_unicode', 'UNICODE', 'LATIN1')</literal></entry>
       <entry><literal>text_in_unicode</literal> represented in ISO 8859-1</entry>
      </row>

      <row>
       <entry>
        <function>decode</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>type</parameter> <type>text</type>)
       </entry>
       <entry><type>bytea</type></entry>
       <entry>
        Decode binary data from <parameter>string</parameter> previously 
        encoded with <function>encode()</>.  Parameter type is same as in <function>encode()</>.
       </entry>
       <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
       <entry><literal>123\000\001</literal></entry>
      </row>       

      <row>
       <entry>
        <function>encode</function>(<parameter>data</parameter> <type>bytea</type>,
        <parameter>type</parameter> <type>text</type>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Encode binary data to <acronym>ASCII</acronym>-only representation.  Supported
        types are: base64, hex, escape.
       </entry>
       <entry><literal>encode('123\\000\\001', 'base64')</literal></entry>
       <entry><literal>MTIzAAE=</literal></entry>
      </row>       

      <row>
       <entry><function>initcap</function>(<type>text</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Convert first letter of each word (whitespace separated) to upper case</entry>
       <entry><literal>initcap('hi thomas')</literal></entry>
       <entry><literal>Hi Thomas</literal></entry>
      </row>

      <row>
       <entry><function>length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>
        Length of string
        <indexterm>
         <primary>character strings</primary>
         <secondary>length</secondary>
        </indexterm>
        <indexterm>
         <primary>length</primary>
         <secondary>character strings</secondary>
         <see>character strings, length</see>
        </indexterm>
       </entry>
       <entry><literal>length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry>
        <function>lpad</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>length</parameter> <type>integer</type>
        <optional>, <parameter>fill</parameter> <type>text</type></optional>)
       </entry>
       <entry>text</entry>
       <entry>
        Fill up the <parameter>string</parameter> to length
        <parameter>length</parameter> by prepending the characters
        <parameter>fill</parameter> (a space by default).  If the
        <parameter>string</parameter> is already longer than
        <parameter>length</parameter> then it is truncated (on the
        right).
       </entry>
       <entry><literal>lpad('hi', 5, 'xy')</literal></entry>
       <entry><literal>xyxhi</literal></entry>
      </row>

      <row>
       <entry><function>ltrim</function>(<parameter>string</parameter> <type>text</type>, <parameter>text</parameter> <type>text</type>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string containing only characters from
        <parameter>trim</parameter> from the start of the string.
       </entry>
       <entry><literal>ltrim('zzzytrim','xyz')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><function>pg_client_encoding</function>()</entry>
       <entry><type>name</type></entry>
       <entry>
        Current client encoding name.
       </entry>
       <entry><literal>pg_client_encoding()</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
      </row>

      <row>
       <entry><function>quote_ident</function>(<parameter>string</parameter> text)</entry>
       <entry><type>text</type></entry>
       <entry>
        Return the given string suitably quoted to be used as an identifier
	in an SQL query string.
	Quotes are added only if necessary (i.e., if the string contains
	non-identifier characters or would be case-folded).
	Embedded quotes are properly doubled.
       </entry>
       <entry><literal>quote_ident('Foo')</literal></entry>
       <entry><literal>"Foo"</literal></entry>
      </row>

      <row>
       <entry><function>quote_literal</function>(<parameter>string</parameter> text)</entry>
       <entry><type>text</type></entry>
       <entry>
        Return the given string suitably quoted to be used as a literal
	in an SQL query string.
	Embedded quotes and backslashes are properly doubled.
       </entry>
       <entry><literal>quote_literal('O\'Reilly')</literal></entry>
       <entry><literal>'O''Reilly'</literal></entry>
      </row>

      <row>
       <entry><function>repeat</function>(<type>text</type>, <type>integer</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Repeat text a number of times</entry>
       <entry><literal>repeat('Pg', 4)</literal></entry>
       <entry><literal>PgPgPgPg</literal></entry>
      </row>

      <row>
       <entry><function>replace</function>(<parameter>string</parameter> <type>text</type>,
       <parameter>from</parameter> <type>text</type>,
       <parameter>to</parameter> <type>text</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Replace all occurrences in <parameter>string</parameter> of substring
        <parameter>from</parameter> with substring <parameter>to</parameter>
       </entry>
       <entry><literal>replace('abcdefabcdef', 'cd', 'XX')</literal></entry>
       <entry><literal>abXXefabXXef</literal></entry>
      </row>

      <row>
       <entry>
        <function>rpad</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>length</parameter> <type>integer</type>
        <optional>, <parameter>fill</parameter> <type>text</type></optional>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Fill up the <parameter>string</parameter> to length
        <parameter>length</parameter> by appending the characters
        <parameter>fill</parameter> (a space by default).  If the
        <parameter>string</parameter> is already longer than
        <parameter>length</parameter> then it is truncated.
       </entry>
       <entry><literal>rpad('hi', 5, 'xy')</literal></entry>
       <entry><literal>hixyx</literal></entry>
      </row>

      <row>
       <entry><function>rtrim</function>(<parameter>string</parameter>
        text, <parameter>trim</parameter> text)</entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string containing only characters from
        <parameter>trim</parameter> from the end of the string.
       </entry>
       <entry><literal>rtrim('trimxxxx','x')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><function>split_part</function>(<parameter>string</parameter> <type>text</type>,
       <parameter>delimiter</parameter> <type>text</type>,
       <parameter>column</parameter> <type>integer</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Split <parameter>string</parameter> on <parameter>delimiter</parameter>
        returning the resulting (one based) <parameter>column</parameter> number.
       </entry>
       <entry><literal>split_part('abc~@~def~@~ghi','~@~',2)</literal></entry>
       <entry><literal>def</literal></entry>
      </row>

      <row>
       <entry><function>strpos</function>(<parameter>string</parameter>, <parameter>substring</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Locate specified substring (same as
        <literal>position(<parameter>substring</parameter> in
         <parameter>string</parameter>)</literal>, but note the reversed
        argument order)
       </entry>
       <entry><literal>strpos('high','ig')</literal></entry>
       <entry><literal>2</literal></entry>
      </row>

      <row>
       <entry><function>substr</function>(<parameter>string</parameter>, <parameter>from</parameter> <optional>, <parameter>count</parameter></optional>)</entry>
       <entry><type>text</type></entry>
       <entry>
        Extract specified substring (same as
        <literal>substring(<parameter>string</parameter> from <parameter>from</parameter> for <parameter>count</parameter>)</literal>)
       </entry>
       <entry><literal>substr('alphabet', 3, 2)</literal></entry>
       <entry><literal>ph</literal></entry>
      </row>

      <row>
       <entry><function>to_ascii</function>(<type>text</type>
        <optional>, <parameter>encoding</parameter></optional>)</entry>
       <entry><type>text</type></entry>

       <entry>
       Convert text to <acronym>ASCII</acronym> from other encoding
       <footnote>
        <para>
         The <function>to_ascii</function> function supports conversion from
         <literal>LATIN1</>, <literal>LATIN2</>, and <literal>WIN1250</> only.
        </para>
       </footnote>
       </entry>

       <entry><literal>to_ascii('Karel')</literal></entry>
       <entry><literal>Karel</literal></entry>
      </row>

      <row>
       <entry><function>to_hex</function>(<parameter>number</parameter> <type>integer</type>
       or <type>bigint</type>)</entry>
       <entry><type>text</type></entry>
       <entry>Convert <parameter>number</parameter> to its equivalent hexadecimal
        representation
       </entry>
       <entry><literal>to_hex(9223372036854775807::bigint)</literal></entry>
       <entry><literal>7fffffffffffffff</literal></entry>
      </row>

      <row>
       <entry>
        <function>translate</function>(<parameter>string</parameter>
        <type>text</type>,
        <parameter>from</parameter> <type>text</type>,
        <parameter>to</parameter> <type>text</type>)
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Any character in <parameter>string</parameter> that matches a
        character in the <parameter>from</parameter> set is replaced by
        the corresponding character in the <parameter>to</parameter>
        set.
       </entry>
       <entry><literal>translate('12345', '14', 'ax')</literal></entry>
       <entry><literal>a23x5</literal></entry>
      </row>       
      
     </tbody>
    </tgroup>
   </table>


   <table id="conversion-names">
    <title>Built-in Conversions</title>
    <tgroup cols="3">
     <thead>
      <row>
       <entry>Conversion Name
        <footnote>
         <para>
          The conversion names follow a standard naming scheme: The
          official name of the source encoding with all
          non-alphanumeric characters replaced by underscores followed
          by <literal>_to_</literal> followed by the equally processed
          destination encoding name. Therefore the names might deviate
          from the customary encoding names.
         </para>
        </footnote>
       </entry>
       <entry>Source Encoding</entry>
       <entry>Destination Encoding</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><literal>ascii_to_mic</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>ascii_to_utf_8</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>big5_to_euc_tw</literal></entry>
       <entry><literal>BIG5</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
      </row>

      <row>
       <entry><literal>big5_to_mic</literal></entry>
       <entry><literal>BIG5</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>big5_to_utf_8</literal></entry>
       <entry><literal>BIG5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>euc_cn_to_mic</literal></entry>
       <entry><literal>EUC_CN</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>euc_cn_to_utf_8</literal></entry>
       <entry><literal>EUC_CN</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>euc_jp_to_mic</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>euc_jp_to_sjis</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
       <entry><literal>SJIS</literal></entry>
      </row>

      <row>
       <entry><literal>euc_jp_to_utf_8</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>euc_kr_to_mic</literal></entry>
       <entry><literal>EUC_KR</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>euc_kr_to_utf_8</literal></entry>
       <entry><literal>EUC_KR</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>euc_tw_to_big5</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
       <entry><literal>BIG5</literal></entry>
      </row>

      <row>
       <entry><literal>euc_tw_to_mic</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>euc_tw_to_utf_8</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>gb18030_to_utf_8</literal></entry>
       <entry><literal>GB18030</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>gbk_to_utf_8</literal></entry>
       <entry><literal>GBK</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_10_to_utf_8</literal></entry>
       <entry><literal>LATIN6</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_13_to_utf_8</literal></entry>
       <entry><literal>LATIN7</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_14_to_utf_8</literal></entry>
       <entry><literal>LATIN8</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_15_to_utf_8</literal></entry>
       <entry><literal>LATIN9</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_16_to_utf_8</literal></entry>
       <entry><literal>LATIN10</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_1_to_mic</literal></entry>
       <entry><literal>LATIN1</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_1_to_utf_8</literal></entry>
       <entry><literal>LATIN1</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_2_to_mic</literal></entry>
       <entry><literal>LATIN2</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_2_to_utf_8</literal></entry>
       <entry><literal>LATIN2</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_2_to_windows_1250</literal></entry>
       <entry><literal>LATIN2</literal></entry>
       <entry><literal>WIN1250</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_3_to_mic</literal></entry>
       <entry><literal>LATIN3</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_3_to_utf_8</literal></entry>
       <entry><literal>LATIN3</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_4_to_mic</literal></entry>
       <entry><literal>LATIN4</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_4_to_utf_8</literal></entry>
       <entry><literal>LATIN4</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_5_to_koi8_r</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>KOI8</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_5_to_mic</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_5_to_utf_8</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_5_to_windows_1251</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>WIN</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_5_to_windows_866</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
       <entry><literal>ALT</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_6_to_utf_8</literal></entry>
       <entry><literal>ISO_8859_6</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_7_to_utf_8</literal></entry>
       <entry><literal>ISO_8859_7</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_8_to_utf_8</literal></entry>
       <entry><literal>ISO_8859_8</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>iso_8859_9_to_utf_8</literal></entry>
       <entry><literal>LATIN5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>johab_to_utf_8</literal></entry>
       <entry><literal>JOHAB</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>koi8_r_to_iso_8859_5</literal></entry>
       <entry><literal>KOI8</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
      </row>

      <row>
       <entry><literal>koi8_r_to_mic</literal></entry>
       <entry><literal>KOI8</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>koi8_r_to_utf_8</literal></entry>
       <entry><literal>KOI8</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>koi8_r_to_windows_1251</literal></entry>
       <entry><literal>KOI8</literal></entry>
       <entry><literal>WIN</literal></entry>
      </row>

      <row>
       <entry><literal>koi8_r_to_windows_866</literal></entry>
       <entry><literal>KOI8</literal></entry>
       <entry><literal>ALT</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_ascii</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_big5</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>BIG5</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_euc_cn</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>EUC_CN</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_euc_jp</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_euc_kr</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>EUC_KR</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_euc_tw</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_iso_8859_1</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>LATIN1</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_iso_8859_2</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>LATIN2</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_iso_8859_3</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>LATIN3</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_iso_8859_4</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>LATIN4</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_iso_8859_5</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_koi8_r</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>KOI8</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_sjis</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>SJIS</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_windows_1250</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>WIN1250</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_windows_1251</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>WIN</literal></entry>
      </row>

      <row>
       <entry><literal>mic_to_windows_866</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
       <entry><literal>ALT</literal></entry>
      </row>

      <row>
       <entry><literal>sjis_to_euc_jp</literal></entry>
       <entry><literal>SJIS</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
      </row>

      <row>
       <entry><literal>sjis_to_mic</literal></entry>
       <entry><literal>SJIS</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>sjis_to_utf_8</literal></entry>
       <entry><literal>SJIS</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>tcvn_to_utf_8</literal></entry>
       <entry><literal>TCVN</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>uhc_to_utf_8</literal></entry>
       <entry><literal>UHC</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_ascii</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_big5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>BIG5</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_euc_cn</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>EUC_CN</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_euc_jp</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>EUC_JP</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_euc_kr</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>EUC_KR</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_euc_tw</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>EUC_TW</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_gb18030</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>GB18030</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_gbk</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>GBK</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_1</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN1</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_10</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN6</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_13</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN7</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_14</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN8</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_15</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN9</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_16</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN10</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_2</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN2</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_3</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN3</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_4</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN4</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_5</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_6</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ISO_8859_6</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_7</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ISO_8859_7</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_8</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ISO_8859_8</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_iso_8859_9</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>LATIN5</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_johab</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>JOHAB</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_koi8_r</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>KOI8</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_sjis</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>SJIS</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_tcvn</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>TCVN</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_uhc</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>UHC</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_windows_1250</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>WIN1250</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_windows_1251</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>WIN</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_windows_1256</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>WIN1256</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_windows_866</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>ALT</literal></entry>
      </row>

      <row>
       <entry><literal>utf_8_to_windows_874</literal></entry>
       <entry><literal>UNICODE</literal></entry>
       <entry><literal>WIN874</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1250_to_iso_8859_2</literal></entry>
       <entry><literal>WIN1250</literal></entry>
       <entry><literal>LATIN2</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1250_to_mic</literal></entry>
       <entry><literal>WIN1250</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1250_to_utf_8</literal></entry>
       <entry><literal>WIN1250</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1251_to_iso_8859_5</literal></entry>
       <entry><literal>WIN</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1251_to_koi8_r</literal></entry>
       <entry><literal>WIN</literal></entry>
       <entry><literal>KOI8</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1251_to_mic</literal></entry>
       <entry><literal>WIN</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1251_to_utf_8</literal></entry>
       <entry><literal>WIN</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1251_to_windows_866</literal></entry>
       <entry><literal>WIN</literal></entry>
       <entry><literal>ALT</literal></entry>
      </row>

      <row>
       <entry><literal>windows_1256_to_utf_8</literal></entry>
       <entry><literal>WIN1256</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>windows_866_to_iso_8859_5</literal></entry>
       <entry><literal>ALT</literal></entry>
       <entry><literal>ISO_8859_5</literal></entry>
      </row>

      <row>
       <entry><literal>windows_866_to_koi8_r</literal></entry>
       <entry><literal>ALT</literal></entry>
       <entry><literal>KOI8</literal></entry>
      </row>

      <row>
       <entry><literal>windows_866_to_mic</literal></entry>
       <entry><literal>ALT</literal></entry>
       <entry><literal>MULE_INTERNAL</literal></entry>
      </row>

      <row>
       <entry><literal>windows_866_to_utf_8</literal></entry>
       <entry><literal>ALT</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

      <row>
       <entry><literal>windows_866_to_windows_1251</literal></entry>
       <entry><literal>ALT</literal></entry>
       <entry><literal>WIN</literal></entry>
      </row>

      <row>
       <entry><literal>windows_874_to_utf_8</literal></entry>
       <entry><literal>WIN874</literal></entry>
       <entry><literal>UNICODE</literal></entry>
      </row>

     </tbody>
    </tgroup>
   </table>

  </sect1>


  <sect1 id="functions-binarystring">
   <title>Binary String Functions and Operators</title>

   <para>
    This section describes functions and operators for examining and
    manipulating binary string values.  Strings in this context include
    values of the type <type>BYTEA</type>.
   </para>

   <para>
    <acronym>SQL</acronym> defines some string functions with a
    special syntax where 
    certain key words rather than commas are used to separate the
    arguments.  Details are in
    <xref linkend="functions-binarystring-sql">.
    Some functions are also implemented using the regular syntax for
    function invocation.
    (See <xref linkend="functions-binarystring-other">.)
   </para>

   <table id="functions-binarystring-sql">
    <title><acronym>SQL</acronym> Binary String Functions and Operators</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>  
      </row>
     </thead>

     <tbody>
      <row>
       <entry> <parameter>string</parameter> <literal>||</literal>
        <parameter>string</parameter> </entry>
       <entry> <type>bytea</type> </entry>
       <entry>
        String concatenation
        <indexterm>
         <primary>binary strings</primary>
         <secondary>concatenation</secondary>
        </indexterm>
       </entry>
       <entry><literal>'\\\\Post'::bytea || '\\047greSQL\\000'::bytea</literal></entry>
       <entry><literal>\\Post'greSQL\000</literal></entry>
      </row>

      <row>
       <entry><function>octet_length</function>(<parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>Number of bytes in binary string</entry>
       <entry><literal>octet_length('jo\\000se'::bytea)</literal></entry>
       <entry><literal>5</literal></entry>
      </row>

      <row>
       <entry><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</entry>
       <entry><type>integer</type></entry>
       <entry>Location of specified substring</entry>
      <entry><literal>position('\\000om'::bytea in 'Th\\000omas'::bytea)</literal></entry>
       <entry><literal>3</literal></entry>
      </row>

      <row>
       <entry><function>substring</function>(<parameter>string</parameter> <optional>from <type>integer</type></optional> <optional>for <type>integer</type></optional>)</entry>
       <entry><type>bytea</type></entry>
       <entry>
        Extract substring
        <indexterm>
         <primary>substring</primary>
        </indexterm>
       </entry>
       <entry><literal>substring('Th\\000omas'::bytea from 2 for 3)</literal></entry>
       <entry><literal>h\000o</literal></entry>
      </row>

      <row>
       <entry>
        <function>trim</function>(<optional>both</optional>
        <parameter>characters</parameter> from
        <parameter>string</parameter>)
       </entry>
       <entry><type>bytea</type></entry>
       <entry>
        Remove the longest string containing only the
        <parameter>characters</parameter> from the
        beginning/end/both ends of the <parameter>string</parameter>
       </entry>
       <entry><literal>trim('\\000'::bytea from '\\000Tom\\000'::bytea)</literal></entry>
       <entry><literal>Tom</literal></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Additional binary string manipulation functions are available and
    are listed in <xref linkend="functions-binarystring-other">.  Some
    of them are used internally to implement the
    <acronym>SQL</acronym>-standard string functions listed in <xref
    linkend="functions-binarystring-sql">.
   </para>

   <table id="functions-binarystring-other">
    <title>Other Binary String Functions</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><function>btrim</function>(<parameter>string</parameter>
        <type>bytea</type> <parameter>trim</parameter> <type>bytea</type>)</entry>
       <entry><type>bytea</type></entry>
       <entry>
        Remove (trim) the longest string consisting only of characters
        in <parameter>trim</parameter> from the start and end of
        <parameter>string</parameter>.
      </entry>
      <entry><literal>btrim('\\000trim\\000'::bytea,'\\000'::bytea)</literal></entry>
      <entry><literal>trim</literal></entry>
     </row>

     <row>
      <entry><function>length</function>(<parameter>string</parameter>)</entry>
      <entry><type>integer</type></entry>
      <entry>
       Length of binary string
       <indexterm>
        <primary>binary strings</primary>
	<secondary>length</secondary>
       </indexterm>
       <indexterm>
        <primary>length</primary>
	<secondary>binary strings</secondary>
	<see>binary strings, length</see>
       </indexterm>
      </entry>
      <entry><literal>length('jo\\000se'::bytea)</literal></entry>
      <entry><literal>5</literal></entry>
     </row>

     <row>
      <entry>
       <function>encode</function>(<parameter>string</parameter> <type>bytea</type>,
              <parameter>type</parameter> <type>text</type>)
      </entry>
      <entry><type>text</type></entry>
      <entry>
       Encode binary string to <acronym>ASCII</acronym>-only representation.  Supported
       types are: base64, hex, escape.
      </entry>
      <entry><literal>encode('123\\000456'::bytea, 'escape')</literal></entry>
      <entry><literal>123\000456</literal></entry>
     </row>       

     <row>
      <entry>
       <function>decode</function>(<parameter>string</parameter> <type>text</type>,
              <parameter>type</parameter> <type>text</type>)
      </entry>
      <entry><type>bytea</type></entry>
      <entry>
       Decode binary string from <parameter>string</parameter> previously 
       encoded with <literal>encode()</>.  Parameter type is same as in <literal>encode()</>.
      </entry>
      <entry><literal>decode('123\\000456', 'escape')</literal></entry>
      <entry><literal>123\000456</literal></entry>
     </row>       

    </tbody>
   </tgroup>
  </table>

 </sect1>


 <sect1 id="functions-matching">
  <title>Pattern Matching</title>

   <para>
    There are three separate approaches to pattern matching provided by
    <productname>PostgreSQL</productname>:  the traditional
    <acronym>SQL</acronym> 
    <function>LIKE</function> operator, the more recent
    <acronym>SQL99</acronym> 
    <function>SIMILAR TO</function> operator, and
    <acronym>POSIX</acronym>-style regular expressions.
    Additionally, a pattern matching function,
    <function>SUBSTRING</function>, is available, using either
    <acronym>SQL99</acronym>-style or POSIX-style regular expressions.
   </para>

   <tip>
    <para>
     If you have pattern matching needs that go beyond this,
     consider writing a user-defined function in Perl or Tcl.
    </para>
   </tip>

  <sect2 id="functions-like">
   <title><function>LIKE</function></title>

   <indexterm>
    <primary>like</primary>
   </indexterm>

<synopsis>
<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
</synopsis>

    <para>
     Every <replaceable>pattern</replaceable> defines a set of strings.
     The <function>LIKE</function> expression returns true if the
     <replaceable>string</replaceable> is contained in the set of
     strings represented by <replaceable>pattern</replaceable>.  (As
     expected, the <function>NOT LIKE</function> expression returns
     false if <function>LIKE</function> returns true, and vice versa.
     An equivalent expression is
     <literal>NOT (<replaceable>string</replaceable> LIKE
      <replaceable>pattern</replaceable>)</literal>.)
    </para>

    <para>
     If <replaceable>pattern</replaceable> does not contain percent
     signs or underscore, then the pattern only represents the string
     itself; in that case <function>LIKE</function> acts like the
     equals operator.  An underscore (<literal>_</literal>) in
     <replaceable>pattern</replaceable> stands for (matches) any single
     character; a percent sign (<literal>%</literal>) matches any string
     of zero or more characters.
    </para>

   <para>
    Some examples:
<programlisting>
'abc' LIKE 'abc'    <lineannotation>true</lineannotation>
'abc' LIKE 'a%'     <lineannotation>true</lineannotation>
'abc' LIKE '_b_'    <lineannotation>true</lineannotation>
'abc' LIKE 'c'      <lineannotation>false</lineannotation>
</programlisting>
   </para>
   
   <para>
    <function>LIKE</function> pattern matches always cover the entire
    string.  To match a pattern anywhere within a string, the
    pattern must therefore start and end with a percent sign.
   </para>

   <para>
    To match a literal underscore or percent sign without matching
    other characters, the respective character in
    <replaceable>pattern</replaceable> must be 
    preceded by the escape character.  The default escape
    character is the backslash but a different one may be selected by
    using the <literal>ESCAPE</literal> clause.  To match the escape
    character itself, write two escape characters.
   </para>

   <para>
    Note that the backslash already has a special meaning in string
    literals, so to write a pattern constant that contains a backslash
    you must write two backslashes in the query.  Thus, writing a pattern
    that actually matches a literal backslash means writing four backslashes
    in the query.  You can avoid this by selecting a different escape
    character with <literal>ESCAPE</literal>; then backslash is not special
    to <function>LIKE</function> anymore. (But it is still special to the string
    literal parser, so you still need two of them.)
   </para>

   <para>
    It's also possible to select no escape character by writing
    <literal>ESCAPE ''</literal>.  This effectively disables the
    escape mechanism, which makes it impossible to turn off the
    special meaning of underscore and percent signs in the pattern.
   </para>

   <para>
    The keyword <token>ILIKE</token> can be used instead of
    <token>LIKE</token> to make the match case insensitive according
    to the active locale.  This is not in the <acronym>SQL</acronym> standard but is a
    <productname>PostgreSQL</productname> extension.
   </para>

   <para>
    The operator <literal>~~</literal> is equivalent to
    <function>LIKE</function>, and <literal>~~*</literal> corresponds to
    <function>ILIKE</function>.  There are also
    <literal>!~~</literal> and <literal>!~~*</literal> operators that
    represent <function>NOT LIKE</function> and <function>NOT
    ILIKE</function>.  All of these operators are
    <productname>PostgreSQL</productname>-specific.
   </para>
  </sect2>


  <sect2 id="functions-sql99-regexp">
   <title><function>SIMILAR TO</function> and <acronym>SQL99</acronym>
     Regular Expressions</title>

   <indexterm zone="functions-sql99-regexp">
    <primary>regular expressions</primary>
    <!-- <seealso>pattern matching</seealso> breaks index build -->
   </indexterm>

   <indexterm>
    <primary>similar to</primary>
   </indexterm>

   <indexterm>
    <primary>substring</primary>
   </indexterm>

<synopsis>
<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
</synopsis>

    <para>
     The <function>SIMILAR TO</function> operator returns true or false
     depending on whether its pattern matches the given string.  It is
     much like <function>LIKE</function>, except that it interprets the
     pattern using <acronym>SQL99</acronym>'s definition of a regular
     expression.
     <acronym>SQL99</acronym>'s regular expressions are a curious cross
     between <function>LIKE</function> notation and common regular expression
     notation.
    </para>

    <para>
     Like <function>LIKE</function>, the  <function>SIMILAR TO</function>
     operator succeeds only if its pattern matches the entire string;
     this is unlike common regular expression practice, wherein the pattern
     may match any part of the string.
     Also like
     <function>LIKE</function>, <function>SIMILAR TO</function> uses
     <literal>%</> and <literal>_</> as wildcard characters denoting
     any string and any single character, respectively (these are
     comparable to <literal>.*</> and <literal>.</> in POSIX regular
     expressions).
    </para>

    <para>
     In addition to these facilities borrowed from <function>LIKE</function>,
     <function>SIMILAR TO</function> supports these pattern-matching
     metacharacters borrowed from POSIX regular expressions:

    <itemizedlist>
     <listitem>
      <para>
       <literal>|</literal> denotes alternation (either of two alternatives).
      </para>
     </listitem>
     <listitem>
      <para>
       <literal>*</literal> denotes repetition of the previous item zero
       or more times.
      </para>
     </listitem>
     <listitem>
      <para>
       <literal>+</literal> denotes repetition of the previous item one
       or more times.
      </para>
     </listitem>
     <listitem>
      <para>
       Parentheses <literal>()</literal> may be used to group items into
       a single logical item.
      </para>
     </listitem>
     <listitem>
      <para>
       A bracket expression <literal>[...]</literal> specifies a character
       class, just as in POSIX regular expressions.
      </para>
     </listitem>
    </itemizedlist>

     Notice that bounded repetition (<literal>?</> and <literal>{...}</>)
     are not provided, though they exist in POSIX.  Also, dot (<literal>.</>)
     is not a metacharacter.
    </para>

    <para>
     As with <function>LIKE</>, a backslash disables the special meaning
     of any of these metacharacters; or a different escape character can
     be specified with <literal>ESCAPE</>.
    </para>

   <para>
    Some examples:
<programlisting>
'abc' SIMILAR TO 'abc'      <lineannotation>true</lineannotation>
'abc' SIMILAR TO 'a'        <lineannotation>false</lineannotation>
'abc' SIMILAR TO '%(b|d)%'  <lineannotation>true</lineannotation>
'abc' SIMILAR TO '(b|c)%'   <lineannotation>false</lineannotation>
</programlisting>
   </para>

    <para>
     The <function>SUBSTRING</> function with three parameters,
     <function>SUBSTRING(<parameter>string</parameter> FROM
     <replaceable>pattern</replaceable> FOR
     <replaceable>escape</replaceable>)</function>, provides
     extraction of a substring that matches a SQL99 regular expression
     pattern.  As with <literal>SIMILAR TO</>, the specified pattern
     must match to the entire data string, else the function fails and
     returns null.  To indicate the part of the pattern that should be
     returned on success, SQL99 specifies that the pattern must
     contain two occurrences of the escape character followed by
     double quote (<literal>"</>).  The text matching the portion of
     the pattern between these markers is returned.
    </para>

   <para>
    Some examples:
<programlisting>
SUBSTRING('foobar' FROM '%#"o_b#"%' FOR '#')   <lineannotation>oob</lineannotation>
SUBSTRING('foobar' FROM '#"o_b#"%' FOR '#')    <lineannotation>NULL</lineannotation>
</programlisting>
   </para>
  </sect2>

  <sect2 id="functions-posix-regexp">
   <title><acronym>POSIX</acronym> Regular Expressions</title>

   <indexterm zone="functions-posix-regexp">
    <primary>regular expressions</primary>
    <seealso>pattern matching</seealso>
   </indexterm>

   <para>
    <xref linkend="functions-posix-table"> lists the available
    operators for pattern matching using POSIX regular expressions.
   </para>

   <table id="functions-posix-table">
    <title>Regular Expression Match Operators</title>

    <tgroup cols="3">
     <thead>
      <row>
       <entry>Operator</entry>
       <entry>Description</entry>
       <entry>Example</entry>
      </row>
     </thead>

      <tbody>
       <row>
        <entry> <literal>~</literal> </entry>
        <entry>Matches regular expression, case sensitive</entry>
        <entry><literal>'thomas' ~ '.*thomas.*'</literal></entry>
       </row>

       <row>
        <entry> <literal>~*</literal> </entry>
        <entry>Matches regular expression, case insensitive</entry>
        <entry><literal>'thomas' ~* '.*Thomas.*'</literal></entry>
       </row>

       <row>
        <entry> <literal>!~</literal> </entry>
        <entry>Does not match regular expression, case sensitive</entry>
        <entry><literal>'thomas' !~ '.*Thomas.*'</literal></entry>
       </row>

       <row>
        <entry> <literal>!~*</literal> </entry>
        <entry>Does not match regular expression, case insensitive</entry>
        <entry><literal>'thomas' !~* '.*vadim.*'</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

    <para>
     <acronym>POSIX</acronym> regular expressions provide a more
     powerful means for 
     pattern matching than the <function>LIKE</function> and
     <function>SIMILAR TO</> operators.
     Many Unix tools such as <command>egrep</command>,
     <command>sed</command>, or <command>awk</command> use a pattern
     matching language that is similar to the one described here.
    </para>

    <para>
     A regular expression is a character sequence that is an
     abbreviated definition of a set of strings (a <firstterm>regular
      set</firstterm>).  A string is said to match a regular expression
     if it is a member of the regular set described by the regular
     expression.  As with <function>LIKE</function>, pattern characters
     match string characters exactly unless they are special characters
     in the regular expression language --- but regular expressions use
     different special characters than <function>LIKE</function> does.
     Unlike <function>LIKE</function> patterns, a
     regular expression is allowed to match anywhere within a string, unless
     the regular expression is explicitly anchored to the beginning or
     end of the string.
    </para>

   <para>
    Some examples:
<programlisting>
'abc' ~ 'abc'    <lineannotation>true</lineannotation>
'abc' ~ '^a'     <lineannotation>true</lineannotation>
'abc' ~ '(b|d)'  <lineannotation>true</lineannotation>
'abc' ~ '^(b|c)' <lineannotation>false</lineannotation>
</programlisting>
   </para>

    <para>
     The <function>SUBSTRING</> function with two parameters,
     <function>SUBSTRING(<parameter>string</parameter> FROM
     <replaceable>pattern</replaceable>)</function>, provides extraction of a substring
     that matches a POSIX regular expression pattern.  It returns null if
     there is no match, otherwise the portion of the text that matched the
     pattern.  But if the pattern contains any parentheses, the portion
     of the text that matched the first parenthesized subexpression (the
     one whose left parenthesis comes first) is
     returned.  You can always put parentheses around the whole expression
     if you want to use parentheses within it without triggering this
     exception.
    </para>

   <para>
    Some examples:
<programlisting>
SUBSTRING('foobar' FROM 'o.b')     <lineannotation>oob</lineannotation>
SUBSTRING('foobar' FROM 'o(.)b')   <lineannotation>o</lineannotation>
</programlisting>
   </para>

<!-- derived from the re_format.7 man page -->
   <para>
    Regular expressions (<acronym>RE</acronym>s), as defined in
     <acronym>POSIX</acronym> 
    1003.2, come in two forms: modern <acronym>RE</acronym>s (roughly those of
    <command>egrep</command>; 1003.2 calls these
    <quote>extended</quote> <acronym>RE</acronym>s) and obsolete <acronym>RE</acronym>s (roughly those of
    <command>ed</command>; 1003.2 <quote>basic</quote> <acronym>RE</acronym>s).
    <productname>PostgreSQL</productname> implements the modern form.
   </para>

   <para>
    A (modern) RE is one or more non-empty
    <firstterm>branches</firstterm>, separated by
    <literal>|</literal>.  It matches anything that matches one of the
    branches.
   </para>

   <para>
    A branch is one or more <firstterm>pieces</firstterm>,
    concatenated.  It matches a match for the first, followed by a
    match for the second, etc.
   </para>

   <para>
    A piece is an <firstterm>atom</firstterm> possibly followed by a
    single <literal>*</literal>, <literal>+</literal>,
    <literal>?</literal>, or <firstterm>bound</firstterm>.  An atom
    followed by <literal>*</literal> matches a sequence of 0 or more
    matches of the atom.  An atom followed by <literal>+</literal>
    matches a sequence of 1 or more matches of the atom.  An atom
    followed by <literal>?</literal> matches a sequence of 0 or 1
    matches of the atom.
   </para>

   <para>
    A <firstterm>bound</firstterm> is <literal>{</literal> followed by
    an unsigned decimal integer, possibly followed by
    <literal>,</literal> possibly followed by another unsigned decimal
    integer, always followed by <literal>}</literal>.  The integers
    must lie between 0 and <symbol>RE_DUP_MAX</symbol> (255)
    inclusive, and if there are two of them, the first may not exceed
    the second.  An atom followed by a bound containing one integer
    <replaceable>i</replaceable> and no comma matches a sequence of
    exactly <replaceable>i</replaceable> matches of the atom.  An atom
    followed by a bound containing one integer
    <replaceable>i</replaceable> and a comma matches a sequence of
    <replaceable>i</replaceable> or more matches of the atom.  An atom
    followed by a bound containing two integers
    <replaceable>i</replaceable> and <replaceable>j</replaceable>
    matches a sequence of <replaceable>i</replaceable> through
    <replaceable>j</replaceable> (inclusive) matches of the atom.
   </para>

   <note>
    <para>
     A repetition operator (<literal>?</literal>,
     <literal>*</literal>, <literal>+</literal>, or bounds) cannot
     follow another repetition operator.  A repetition operator cannot
     begin an expression or subexpression or follow
     <literal>^</literal> or <literal>|</literal>.
    </para>
   </note>

   <para>
    An <firstterm>atom</firstterm> is a regular expression enclosed in
    <literal>()</literal> (matching a match for the regular
    expression), an empty set of <literal>()</literal> (matching the
    null string), a <firstterm>bracket expression</firstterm> (see
    below), <literal>.</literal> (matching any single character),
    <literal>^</literal> (matching the null string at the beginning of the
    input string), <literal>$</literal> (matching the null string at the end
    of the input string), a <literal>\</literal> followed by one of the
    characters <literal>^.[$()|*+?{\</literal> (matching that
    character taken as an ordinary character), a <literal>\</literal>
    followed by any other character (matching that character taken as
    an ordinary character, as if the <literal>\</literal> had not been
    present), or a single character with no other significance
    (matching that character).  A <literal>{</literal> followed by a
    character other than a digit is an ordinary character, not the
    beginning of a bound.  It is illegal to end an RE with
    <literal>\</literal>.
   </para>

   <para>
    Note that the backslash (<literal>\</literal>) already has a special
    meaning in string
    literals, so to write a pattern constant that contains a backslash
    you must write two backslashes in the query.
   </para>

   <para>
    A <firstterm>bracket expression</firstterm> is a list of
    characters enclosed in <literal>[]</literal>.  It normally matches
    any single character from the list (but see below).  If the list
    begins with <literal>^</literal>, it matches any single character
    (but see below) not from the rest of the list.  If two characters
    in the list are separated by <literal>-</literal>, this is
    shorthand for the full range of characters between those two
    (inclusive) in the collating sequence,
    e.g. <literal>[0-9]</literal> in <acronym>ASCII</acronym> matches
    any decimal digit.  It is illegal for two ranges to share an
    endpoint, e.g.  <literal>a-c-e</literal>.  Ranges are very
    collating-sequence-dependent, and portable programs should avoid
    relying on them.
   </para>

   <para>
    To include a literal <literal>]</literal> in the list, make it the
    first character (following a possible <literal>^</literal>).  To
    include a literal <literal>-</literal>, make it the first or last
    character, or the second endpoint of a range.  To use a literal
    <literal>-</literal> as the first endpoint of a range, enclose it
    in <literal>[.</literal> and <literal>.]</literal> to make it a
    collating element (see below).  With the exception of these and
    some combinations using <literal>[</literal> (see next
    paragraphs), all other special characters, including
    <literal>\</literal>, lose their special significance within a
    bracket expression.
   </para>

   <para>
    Within a bracket expression, a collating element (a character, a
    multiple-character sequence that collates as if it were a single
    character, or a collating-sequence name for either) enclosed in
    <literal>[.</literal> and <literal>.]</literal> stands for the
    sequence of characters of that collating element.  The sequence is
    a single element of the bracket expression's list.  A bracket
    expression containing a multiple-character collating element can thus
    match more than one character, e.g. if the collating sequence
    includes a <literal>ch</literal> collating element, then the RE
    <literal>[[.ch.]]*c</literal> matches the first five characters of
    <literal>chchcc</literal>.
   </para>

   <para>
    Within a bracket expression, a collating element enclosed in
    <literal>[=</literal> and <literal>=]</literal> is an equivalence
    class, standing for the sequences of characters of all collating
    elements equivalent to that one, including itself.  (If there are
    no other equivalent collating elements, the treatment is as if the
    enclosing delimiters were <literal>[.</literal> and
    <literal>.]</literal>.)  For example, if <literal>o</literal> and
    <literal>^</literal> are the members of an equivalence class, then
    <literal>[[=o=]]</literal>, <literal>[[=^=]]</literal>, and
    <literal>[o^]</literal> are all synonymous.  An equivalence class
    may not be an endpoint of a range.
   </para>

   <para>
    Within a bracket expression, the name of a character class
    enclosed in <literal>[:</literal> and <literal>:]</literal> stands
    for the list of all characters belonging to that class.  Standard
    character class names are: <literal>alnum</literal>,
    <literal>alpha</literal>, <literal>blank</literal>,
    <literal>cntrl</literal>, <literal>digit</literal>,
    <literal>graph</literal>, <literal>lower</literal>,
    <literal>print</literal>, <literal>punct</literal>,
    <literal>space</literal>, <literal>upper</literal>,
    <literal>xdigit</literal>.  These stand for the character classes
    defined in
    <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>.
    A locale may provide others.  A character class may not be used as
    an endpoint of a range.
   </para>

   <para>
    There are two special cases of bracket expressions:  the bracket
    expressions <literal>[[:&lt;:]]</literal> and
    <literal>[[:>:]]</literal> match the null string at the beginning
    and end of a word respectively.  A word is defined as a sequence
    of word characters which is neither preceded nor followed by word
    characters.  A word character is an alnum character (as defined by
    <citerefentry><refentrytitle>ctype</refentrytitle><manvolnum>3</manvolnum></citerefentry>)
    or an underscore.  This is an extension, compatible with but not
    specified by <acronym>POSIX</acronym> 1003.2, and should be used with caution in
    software intended to be portable to other systems.
   </para>

   <para>
    In the event that an RE could match more than one substring of a
    given string, the RE matches the one starting earliest in the
    string.  If the RE could match more than one substring starting at
    that point, it matches the longest.  Subexpressions also match the
    longest possible substrings, subject to the constraint that the
    whole match be as long as possible, with subexpressions starting
    earlier in the RE taking priority over ones starting later.  Note
    that higher-level subexpressions thus take priority over their
    lower-level component subexpressions.
   </para>

   <para>
    Match lengths are measured in characters, not collating
    elements.  A null string is considered longer than no match at
    all.  For example, <literal>bb*</literal> matches the three middle
    characters of <literal>abbbc</literal>,
    <literal>(wee|week)(knights|nights)</literal> matches all ten
    characters of <literal>weeknights</literal>, when
    <literal>(.*).*</literal> is matched against
    <literal>abc</literal> the parenthesized subexpression matches all
    three characters, and when <literal>(a*)*</literal> is matched
    against <literal>bc</literal> both the whole RE and the
    parenthesized subexpression match the null string.
   </para>

   <para>
    If case-independent matching is specified, the effect is much as
    if all case distinctions had vanished from the alphabet.  When an
    alphabetic that exists in multiple cases appears as an ordinary
    character outside a bracket expression, it is effectively
    transformed into a bracket expression containing both cases,
    e.g. <literal>x</literal> becomes <literal>[xX]</literal>.  When
    it appears inside a bracket expression, all case counterparts of
    it are added to the bracket expression, so that (e.g.)
    <literal>[x]</literal> becomes <literal>[xX]</literal> and
    <literal>[^x]</literal> becomes <literal>[^xX]</literal>.
   </para>

   <para>
    There is no particular limit on the length of <acronym>RE</acronym>s, except insofar
    as memory is limited.  Memory usage is approximately linear in RE
    size, and largely insensitive to RE complexity, except for bounded
    repetitions.  Bounded repetitions are implemented by macro
    expansion, which is costly in time and space if counts are large
    or bounded repetitions are nested.  An RE like, say,
    <literal>((((a{1,100}){1,100}){1,100}){1,100}){1,100}</literal>
    will (eventually) run almost any existing machine out of swap
    space.
    <footnote>
     <para>
      This was written in 1994, mind you.  The
      numbers have probably changed, but the problem
      persists.
     </para>
    </footnote>
   </para>
<!-- end re_format.7 man page -->
  </sect2>

 </sect1>


  <sect1 id="functions-formatting">
   <title>Data Type Formatting Functions</title>

   <indexterm zone="functions-formatting">
    <primary>formatting</primary>
   </indexterm>

   <para>
    The <productname>PostgreSQL</productname> formatting functions
    provide a powerful set of tools for converting various data types
    (date/time, integer, floating point, numeric) to formatted strings
    and for converting from formatted strings to specific data types.
    <xref linkend="functions-formatting-table"> lists them.
    These functions all follow a common calling convention: the first
    argument is the value to be formatted and the second argument is a
    template that defines the output or input format.
   </para>

    <table id="functions-formatting-table">
     <title>Formatting Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><function>to_char</function>(<type>timestamp</type>, <type>text</type>)</entry>
	<entry><type>text</type></entry>
	<entry>convert time stamp to string</entry>
	<entry><literal>to_char(timestamp 'now','HH12:MI:SS')</literal></entry>
       </row>
       <row>
	<entry><function>to_char</function>(<type>interval</type>, <type>text</type>)</entry>
	<entry><type>text</type></entry>
	<entry>convert interval to string</entry>
	<entry><literal>to_char(interval '15h 2m 12s','HH24:MI:SS')</literal></entry>
       </row>
       <row>
	<entry><function>to_char</function>(<type>int</type>, <type>text</type>)</entry>
	<entry><type>text</type></entry>
	<entry>convert integer to string</entry>
	<entry><literal>to_char(125, '999')</literal></entry>
       </row>
       <row>
	<entry><function>to_char</function>(<type>double precision</type>,
        <type>text</type>)</entry>
	<entry><type>text</type></entry>
	<entry>convert real/double precision to string</entry>
	<entry><literal>to_char(125.8, '999D9')</literal></entry>
       </row>
       <row>
	<entry><function>to_char</function>(<type>numeric</type>, <type>text</type>)</entry>
	<entry><type>text</type></entry>
	<entry>convert numeric to string</entry>
	<entry><literal>to_char(numeric '-125.8', '999D99S')</literal></entry>
       </row>
       <row>
	<entry><function>to_date</function>(<type>text</type>, <type>text</type>)</entry>
	<entry><type>date</type></entry>
	<entry>convert string to date</entry>
	<entry><literal>to_date('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
       </row>
       <row>
	<entry><function>to_timestamp</function>(<type>text</type>, <type>text</type>)</entry>
	<entry><type>timestamp</type></entry>
	<entry>convert string to time stamp</entry>
	<entry><literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal></entry>
       </row>
       <row>
	<entry><function>to_number</function>(<type>text</type>, <type>text</type>)</entry>
	<entry><type>numeric</type></entry>
	<entry>convert string to numeric</entry>
	<entry><literal>to_number('12,454.8-', '99G999D9S')</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

   <para>
    In an output template string, there are certain patterns that are
    recognized and replaced with appropriately-formatted data from the value
    to be formatted.  Any text that is not a template pattern is simply
    copied verbatim.  Similarly, in an input template string, template patterns
    identify the parts of the input data string to be looked at and the
    values to be found there.
   </para>

  <para>
   <xref linkend="functions-formatting-datetime-table"> shows the
   template patterns available for formatting date and time values.
  </para>

    <table id="functions-formatting-datetime-table">
     <title>Template patterns for date/time conversions</title>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Pattern</entry>
	<entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><literal>HH</literal></entry>
	<entry>hour of day (01-12)</entry>
       </row>
       <row>
	<entry><literal>HH12</literal></entry>
	<entry>hour of day (01-12)</entry>
       </row>       
       <row>
	<entry><literal>HH24</literal></entry>
	<entry>hour of day (00-23)</entry>
       </row>       
       <row>
	<entry><literal>MI</literal></entry>
	<entry>minute (00-59)</entry>
       </row>   
       <row>
	<entry><literal>SS</literal></entry>
	<entry>second (00-59)</entry>
       </row>
       <row>
	<entry><literal>MS</literal></entry>
	<entry>millisecond (000-999)</entry>
       </row>
       <row>
	<entry><literal>US</literal></entry>
	<entry>microsecond (000000-999999)</entry>
       </row>
       <row>
	<entry><literal>SSSS</literal></entry>
	<entry>seconds past midnight (0-86399)</entry>
       </row>
       <row>
	<entry><literal>AM</literal> or <literal>A.M.</literal> or
	<literal>PM</literal> or <literal>P.M.</literal></entry>
	<entry>meridian indicator (upper case)</entry>
       </row>
       <row>
	<entry><literal>am</literal> or <literal>a.m.</literal> or
	<literal>pm</literal> or <literal>p.m.</literal></entry>
	<entry>meridian indicator (lower case)</entry>
       </row>
       <row>
	<entry><literal>Y,YYY</literal></entry>
	<entry>year (4 and more digits) with comma</entry>
       </row>
       <row>
	<entry><literal>YYYY</literal></entry>
	<entry>year (4 and more digits)</entry>
       </row>
       <row>
	<entry><literal>YYY</literal></entry>
	<entry>last 3 digits of year</entry>
       </row>
       <row>
	<entry><literal>YY</literal></entry>
	<entry>last 2 digits of year</entry>
       </row>
       <row>
	<entry><literal>Y</literal></entry>
	<entry>last digit of year</entry>
       </row>
       <row>
	<entry><literal>BC</literal> or <literal>B.C.</literal> or
	<literal>AD</literal> or <literal>A.D.</literal></entry>
	<entry>era indicator (upper case)</entry>
       </row>
       <row>
	<entry><literal>bc</literal> or <literal>b.c.</literal> or
	<literal>ad</literal> or <literal>a.d.</literal></entry>
	<entry>era indicator (lower case)</entry>
       </row>
       <row>
	<entry><literal>MONTH</literal></entry>
	<entry>full upper case month name (blank-padded to 9 chars)</entry>
       </row>
       <row>
	<entry><literal>Month</literal></entry>
	<entry>full mixed case month name (blank-padded to 9 chars)</entry>
       </row>
       <row>
	<entry><literal>month</literal></entry>
	<entry>full lower case month name (blank-padded to 9 chars)</entry>
       </row>
       <row>
	<entry><literal>MON</literal></entry>
	<entry>abbreviated upper case month name (3 chars)</entry>
       </row>
       <row>
	<entry><literal>Mon</literal></entry>
	<entry>abbreviated mixed case month name (3 chars)</entry>
       </row>
       <row>
	<entry><literal>mon</literal></entry>
	<entry>abbreviated lower case month name (3 chars)</entry>
       </row>
       <row>
	<entry><literal>MM</literal></entry>
	<entry>month number (01-12)</entry>
       </row>
       <row>
	<entry><literal>DAY</literal></entry>
	<entry>full upper case day name (blank-padded to 9 chars)</entry>
       </row>
       <row>
	<entry><literal>Day</literal></entry>
	<entry>full mixed case day name (blank-padded to 9 chars)</entry>
       </row>
       <row>
	<entry><literal>day</literal></entry>
	<entry>full lower case day name (blank-padded to 9 chars)</entry>
       </row>
       <row>
	<entry><literal>DY</literal></entry>
	<entry>abbreviated upper case day name (3 chars)</entry>
       </row>
       <row>
	<entry><literal>Dy</literal></entry>
	<entry>abbreviated mixed case day name (3 chars)</entry>
       </row>
       <row>
	<entry><literal>dy</literal></entry>
	<entry>abbreviated lower case day name (3 chars)</entry>
       </row>
       <row>
	<entry><literal>DDD</literal></entry>
	<entry>day of year (001-366)</entry>
       </row>
       <row>
	<entry><literal>DD</literal></entry>
	<entry>day of month (01-31)</entry>
       </row>
       <row>
	<entry><literal>D</literal></entry>
	<entry>day of week (1-7; SUN=1)</entry>
       </row>
       <row>
	<entry><literal>W</literal></entry>
	<entry>week of month (1-5) where first week start on the first day of the month</entry>
       </row> 
       <row>
	<entry><literal>WW</literal></entry>
	<entry>week number of year (1-53) where first week start on the first day of the year</entry>
       </row>
       <row>
	<entry><literal>IW</literal></entry>
	<entry>ISO week number of year (The first Thursday of the new year is in week 1.)</entry>
       </row>
       <row>
	<entry><literal>CC</literal></entry>
	<entry>century (2 digits)</entry>
       </row>
       <row>
	<entry><literal>J</literal></entry>
	<entry>Julian Day (days since January 1, 4712 BC)</entry>
       </row>
       <row>
	<entry><literal>Q</literal></entry>
	<entry>quarter</entry>
       </row>
       <row>
	<entry><literal>RM</literal></entry>
	<entry>month in Roman Numerals (I-XII; I=January) - upper case</entry>
       </row>
       <row>
	<entry><literal>rm</literal></entry>
	<entry>month in Roman Numerals (I-XII; I=January) - lower case</entry>
       </row>
       <row>
	<entry><literal>TZ</literal></entry>
	<entry>time-zone name - upper case</entry>
       </row>
       <row>
	<entry><literal>tz</literal></entry>
	<entry>time-zone name - lower case</entry>
       </row>
      </tbody>
     </tgroup>
    </table>

   <para>
    Certain modifiers may be applied to any template pattern to alter its
    behavior.  For example, <quote><literal>FMMonth</literal></quote>
    is the <quote><literal>Month</literal></quote> pattern with the
    <quote><literal>FM</literal></quote> prefix.
    <xref linkend="functions-formatting-datetimemod-table"> shows the
    modifier patterns for date/time formatting.
   </para>

    <table id="functions-formatting-datetimemod-table">
     <title>Template pattern modifiers for date/time conversions</title>
     <tgroup cols="3">
      <thead>
       <row>
	<entry>Modifier</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><literal>FM</literal> prefix</entry>
	<entry>fill mode (suppress padding blanks and zeroes)</entry>
	<entry><literal>FMMonth</literal></entry>
       </row>
       <row>
	<entry><literal>TH</literal> suffix</entry>
	<entry>add upper-case ordinal number suffix</entry>
	<entry><literal>DDTH</literal></entry>
       </row>	
       <row>
	<entry><literal>th</literal> suffix</entry>
	<entry>add lower-case ordinal number suffix</entry>
	<entry><literal>DDth</literal></entry>
       </row>
       <row>
	<entry><literal>FX</literal> prefix</entry>
	<entry>fixed format global option (see usage notes)</entry>
	<entry><literal>FX Month DD Day</literal></entry>
       </row>	
       <row>
	<entry><literal>SP</literal> suffix</entry>
	<entry>spell mode (not yet implemented)</entry>
	<entry><literal>DDSP</literal></entry>
       </row>       
      </tbody>
     </tgroup>
    </table>

   <para>
    Usage notes for the date/time formatting:

    <itemizedlist>
     <listitem>
      <para>
       <literal>FM</literal> suppresses leading zeroes and trailing blanks
       that would otherwise be added to make the output of a pattern be
       fixed-width.
      </para>
     </listitem>

     <listitem>
      <para>
       <function>to_timestamp</function> and <function>to_date</function>
       skip multiple blank spaces in the input string if the <literal>FX</literal> option 
       is not used. <literal>FX</literal> must be specified as the first item
       in the template; for example 
       <literal>to_timestamp('2000    JUN','YYYY MON')</literal> is right, but
       <literal>to_timestamp('2000    JUN','FXYYYY MON')</literal> returns an error,
       because <function>to_timestamp</function> expects one blank space only.
      </para>
     </listitem>

     <listitem>
      <para>
       If a backslash (<quote><literal>\</literal></quote>) is desired
       in a string constant, a double backslash
       (<quote><literal>\\</literal></quote>) must be entered; for
       example <literal>'\\HH\\MI\\SS'</literal>.  This is true for
       any string constant in <productname>PostgreSQL</productname>.
      </para>
     </listitem>

     <listitem>
      <para>
       Ordinary text is allowed in <function>to_char</function>
       templates and will be output literally.  You can put a substring
       in double quotes to force it to be interpreted as literal text
       even if it contains pattern keywords.  For example, in
       <literal>'"Hello Year "YYYY'</literal>, the <literal>YYYY</literal>
       will be replaced by the year data, but the single <literal>Y</literal> in <quote>Year</quote>
       will not be.
      </para>
     </listitem>

     <listitem>
      <para>
       If you want to have a double quote in the output you must
       precede it with a backslash, for example <literal>'\\"YYYY
       Month\\"'</literal>. <!-- "" font-lock sanity :-) -->
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>YYYY</literal> conversion from string to <type>timestamp</type> or
       <type>date</type> is restricted if you use a year with more than 4 digits. You must
       use some non-digit character or template after <literal>YYYY</literal>,
       otherwise the year is always interpreted as 4 digits. For example
       (with year 20000):
       <literal>to_date('200001131', 'YYYYMMDD')</literal> will be 
       interpreted as a 4-digit year; better is to use a non-digit 
       separator after the year, like
       <literal>to_date('20000-1131', 'YYYY-MMDD')</literal> or
       <literal>to_date('20000Nov31', 'YYYYMonDD')</literal>.
      </para>
     </listitem>

     <listitem>
      <para>
       Millisecond <literal>MS</literal> and microsecond <literal>US</literal>
       values in a conversion from string to time stamp are used as part of the
       seconds after the decimal point. For example 
       <literal>to_timestamp('12:3', 'SS:MS')</literal> is not 3 milliseconds,
       but 300, because the conversion counts it as 12 + 0.3.
       This means for the format <literal>SS:MS</literal>, the input values
       <literal>12:3</literal>, <literal>12:30</literal>, and <literal>12:300</literal> specify the
       same number of milliseconds. To get three milliseconds, one must use
       <literal>12:003</literal>, which the conversion counts as
       12 + 0.003 = 12.003 seconds.
      </para>

      <para>
       Here is a more 
       complex example: 
       <literal>to_timestamp('15:12:02.020.001230','HH:MI:SS.MS.US')</literal>
       is 15 hours, 12 minutes, and 2 seconds + 20 milliseconds +
       1230 microseconds = 2.021230 seconds. 
      </para>
     </listitem>
    </itemizedlist>
   </para>

  <para>
   <xref linkend="functions-formatting-numeric-table"> shows the
   template patterns available for formatting numeric values.
  </para>

    <table id="functions-formatting-numeric-table">
     <title>Template patterns for numeric conversions</title>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Pattern</entry>
	<entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><literal>9</literal></entry>
	<entry>value with the specified number of digits</entry>
       </row>
       <row>
	<entry><literal>0</literal></entry>
	<entry>value with leading zeros</entry>
       </row>
       <row>
	<entry><literal>.</literal> (period)</entry>
	<entry>decimal point</entry>
       </row>       
       <row>
	<entry><literal>,</literal> (comma)</entry>
	<entry>group (thousand) separator</entry>
       </row>
       <row>
	<entry><literal>PR</literal></entry>
	<entry>negative value in angle brackets</entry>
       </row>
       <row>
	<entry><literal>S</literal></entry>
	<entry>negative value with minus sign (uses locale)</entry>
       </row>
       <row>
	<entry><literal>L</literal></entry>
	<entry>currency symbol (uses locale)</entry>
       </row>
       <row>
	<entry><literal>D</literal></entry>
	<entry>decimal point (uses locale)</entry>
       </row>
       <row>
	<entry><literal>G</literal></entry>
	<entry>group separator (uses locale)</entry>
       </row>
       <row>
	<entry><literal>MI</literal></entry>
	<entry>minus sign in specified position (if number &lt; 0)</entry>
       </row>
       <row>
	<entry><literal>PL</literal></entry>
	<entry>plus sign in specified position (if number &gt; 0)</entry>
       </row>
       <row>
	<entry><literal>SG</literal></entry>
	<entry>plus/minus sign in specified position</entry>
       </row>
       <row>
	<entry><literal>RN</literal></entry>
	<entry>roman numeral (input between 1 and 3999)</entry>
       </row>
       <row>
	<entry><literal>TH</literal> or <literal>th</literal></entry>
	<entry>convert to ordinal number</entry>
       </row>
       <row>
	<entry><literal>V</literal></entry>
	<entry>shift <replaceable>n</replaceable> digits (see
	 notes)</entry>
       </row>
       <row>
	<entry><literal>EEEE</literal></entry>
	<entry>scientific notation (not implemented yet)</entry>
       </row>
      </tbody>
     </tgroup>
    </table>

   <para>
    Usage notes for the numeric formatting:

    <itemizedlist>
     <listitem>
      <para>
       A sign formatted using <literal>SG</literal>, <literal>PL</literal>, or
       <literal>MI</literal> is not an anchor in
       the number; for example,
       <literal>to_char(-12, 'S9999')</literal> produces <literal>'  -12'</literal>,
       but <literal>to_char(-12, 'MI9999')</literal> produces <literal>'-  12'</literal>.
       The Oracle implementation does not allow the use of
       <literal>MI</literal> ahead of <literal>9</literal>, but rather
       requires that <literal>9</literal> precede
       <literal>MI</literal>.
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>9</literal> specifies a value with the same number of 
       digits as there are <literal>9</literal>s. If a digit is
       not available it outputs a space.
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>TH</literal> does not convert values less than zero
       and does not convert decimal numbers.
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>PL</literal>, <literal>SG</literal>, and
       <literal>TH</literal> are <productname>PostgreSQL</productname>
       extensions. 
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>V</literal> effectively
       multiplies the input values by
       <literal>10^<replaceable>n</replaceable></literal>, where
       <replaceable>n</replaceable> is the number of digits following
       <literal>V</literal>. 
       <function>to_char</function> does not support the use of
       <literal>V</literal> combined with a decimal point.
       (E.g., <literal>99.9V99</literal> is not allowed.)
      </para>
     </listitem>
    </itemizedlist>
   </para>   

  <para>
   <xref linkend="functions-formatting-examples-table"> shows some
   examples of the use of the <function>to_char</function> function.
  </para>

    <table id="functions-formatting-examples-table">
     <title><function>to_char</function> Examples</title>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Input</entry>
	<entry>Output</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry><literal>to_char(now(),'Day, DD  HH12:MI:SS')</literal></entry>
        <entry><literal>'Tuesday  , 06  05:39:18'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(now(),'FMDay, FMDD  HH12:MI:SS')</literal></entry>
        <entry><literal>'Tuesday, 6  05:39:18'</literal></entry>
       </row>          
       <row>
        <entry><literal>to_char(-0.1,'99.99')</literal></entry>
        <entry><literal>' -.10'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(-0.1,'FM9.99')</literal></entry>
        <entry><literal>'-.1'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(0.1,'0.9')</literal></entry>
        <entry><literal>' 0.1'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(12,'9990999.9')</literal></entry>
        <entry><literal>'    0012.0'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(12,'FM9990999.9')</literal></entry>
        <entry><literal>'0012'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(485,'999')</literal></entry>
        <entry><literal>' 485'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(-485,'999')</literal></entry>
        <entry><literal>'-485'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(485,'9 9 9')</literal></entry>
        <entry><literal>' 4 8 5'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(1485,'9,999')</literal></entry>
        <entry><literal>' 1,485'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(1485,'9G999')</literal></entry>
        <entry><literal>' 1 485'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(148.5,'999.999')</literal></entry>
        <entry><literal>' 148.500'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(148.5,'999D999')</literal></entry>
        <entry><literal>' 148,500'</literal></entry>	 
       </row>
       <row>
        <entry><literal>to_char(3148.5,'9G999D999')</literal></entry>
        <entry><literal>' 3 148,500'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(-485,'999S')</literal></entry>
        <entry><literal>'485-'</literal></entry>
       </row>
       <row>		
        <entry><literal>to_char(-485,'999MI')</literal></entry>
        <entry><literal>'485-'</literal></entry>	
       </row>
       <row>
        <entry><literal>to_char(485,'999MI')</literal></entry>
        <entry><literal>'485'</literal></entry>		
       </row>
       <row>
        <entry><literal>to_char(485,'PL999')</literal></entry>
        <entry><literal>'+485'</literal></entry>	
       </row>
       <row>		
        <entry><literal>to_char(485,'SG999')</literal></entry>
        <entry><literal>'+485'</literal></entry>	
       </row>
       <row>
        <entry><literal>to_char(-485,'SG999')</literal></entry>
        <entry><literal>'-485'</literal></entry>	
       </row>
       <row>
        <entry><literal>to_char(-485,'9SG99')</literal></entry>
        <entry><literal>'4-85'</literal></entry>	
       </row>
       <row>
        <entry><literal>to_char(-485,'999PR')</literal></entry>
        <entry><literal>'&lt;485&gt;'</literal></entry>		
       </row>
       <row>
        <entry><literal>to_char(485,'L999')</literal></entry>
        <entry><literal>'DM 485</literal></entry>	 
       </row>
       <row>
        <entry><literal>to_char(485,'RN')</literal></entry>		
        <entry><literal>'        CDLXXXV'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(485,'FMRN')</literal></entry>	
        <entry><literal>'CDLXXXV'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(5.2,'FMRN')</literal></entry>
        <entry><literal>V</literal></entry>		
       </row>
       <row>
        <entry><literal>to_char(482,'999th')</literal></entry>
        <entry><literal>' 482nd'</literal></entry>				
       </row>
       <row>
        <entry><literal>to_char(485, '"Good number:"999')</literal></entry>
        <entry><literal>'Good number: 485'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(485.8,'"Pre:"999" Post:" .999')</literal></entry>
        <entry><literal>'Pre: 485 Post: .800'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(12,'99V999')</literal></entry>		
        <entry><literal>' 12000'</literal></entry>
       </row>
       <row>
        <entry><literal>to_char(12.4,'99V999')</literal></entry>
        <entry><literal>' 12400'</literal></entry>
       </row>
       <row>		
        <entry><literal>to_char(12.45, '99V9')</literal></entry>
        <entry><literal>' 125'</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

  </sect1>


  <sect1 id="functions-datetime">
   <title>Date/Time Functions and Operators</title>

  <para>
   <xref linkend="functions-datetime-table"> shows the available
   functions for date/time value processing, with details appearing in
   the following subsections.  <xref
   linkend="operators-datetime-table"> illustrates the behaviors of
   the basic arithmetic operators (<literal>+</literal>,
   <literal>*</literal>, etc.).  For formatting functions, refer to
   <xref linkend="functions-formatting">.  You should be familiar with
   the background information on date/time data types (see <xref
   linkend="datatype-datetime">).
  </para>

  <para>
   All the functions and operators described below that take time or timestamp
   inputs actually come in two variants: one that takes time or timestamp
   with time zone, and one that takes time or timestamp without time zone.
   For brevity, these variants are not shown separately.
  </para>

    <table id="operators-datetime-table">
     <title>Date/Time Operators</title>

     <tgroup cols="3">
      <thead>
       <row>
        <entry>Name</entry>
        <entry>Example</entry>
        <entry>Result</entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry> <literal>+</literal> </entry>
        <entry><literal>timestamp '2001-09-28 01:00' + interval '23 hours'</literal></entry>
        <entry><literal>timestamp '2001-09-29 00:00'</literal></entry>
       </row>

       <row>
        <entry> <literal>+</literal> </entry>
        <entry><literal>date '2001-09-28' + interval '1 hour'</literal></entry>
        <entry><literal>timestamp '2001-09-28 01:00'</literal></entry>
       </row>

       <row>
        <entry> <literal>+</literal> </entry>
        <entry><literal>time '01:00' + interval '3 hours'</literal></entry>
        <entry><literal>time '04:00'</literal></entry>
       </row>

       <row>
        <entry> <literal>-</literal> </entry>
        <entry><literal>timestamp '2001-09-28 23:00' - interval '23 hours'</literal></entry>
        <entry><literal>timestamp '2001-09-28'</literal></entry>
       </row>

       <row>
        <entry> <literal>-</literal> </entry>
        <entry><literal>date '2001-09-28' - interval '1 hour'</literal></entry>
        <entry><literal>timestamp '2001-09-27 23:00'</literal></entry>
       </row>

       <row>
        <entry> <literal>-</literal> </entry>
        <entry><literal>time '05:00' - interval '2 hours'</literal></entry>
        <entry><literal>time '03:00'</literal></entry>
       </row>

       <row>
        <entry> <literal>-</literal> </entry>
        <entry><literal>interval '2 hours' - time '05:00'</literal></entry>
        <entry><literal>time '03:00:00'</literal></entry>
       </row>

       <row>
        <entry> <literal>*</literal> </entry>
        <entry><literal>interval '1 hour' * int '3'</literal></entry>
        <entry><literal>interval '03:00'</literal></entry>
       </row>

       <row>
        <entry> <literal>/</literal> </entry>
        <entry><literal>interval '1 hour' / int '3'</literal></entry>
        <entry><literal>interval '00:20'</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

    <table id="functions-datetime-table">
     <title>Date/Time Functions</title>
     <tgroup cols="5">
      <thead>
       <row>
	<entry>Name</entry>
	<entry>Return Type</entry>
	<entry>Description</entry>
	<entry>Example</entry>
	<entry>Result</entry>
       </row>
      </thead>

      <tbody>
       <row>
	<entry><function>age</function>(<type>timestamp</type>)</entry>
	<entry><type>interval</type></entry>
	<entry>Subtract from today</entry>
	<entry><literal>age(timestamp '1957-06-13')</literal></entry>
	<entry><literal>43 years 8 mons 3 days</literal></entry>
       </row>

       <row>
	<entry><function>age</function>(<type>timestamp</type>, <type>timestamp</type>)</entry>
	<entry><type>interval</type></entry>
	<entry>Subtract arguments</entry>
	<entry><literal>age('2001-04-10', timestamp '1957-06-13')</literal></entry>
	<entry><literal>43 years 9 mons 27 days</literal></entry>
       </row>

       <row>
	<entry><function>current_date</function></entry>
	<entry><type>date</type></entry>
	<entry>Today's date; see <xref linkend="functions-datetime-current">
	</entry>
	<entry></entry>
	<entry></entry>
       </row>

       <row>
        <entry><function>current_time</function></entry>
        <entry><type>time with time zone</type></entry>
        <entry>Time of day; see <xref linkend="functions-datetime-current">
        </entry>
        <entry></entry>
        <entry></entry>
       </row>

       <row>
	<entry><function>current_timestamp</function></entry>
	<entry><type>timestamp with time zone</type></entry>
	<entry>Date and time; see <xref linkend="functions-datetime-current">
	</entry>
	<entry></entry>
	<entry></entry>
       </row>

       <row>
	<entry><function>date_part</function>(<type>text</type>, <type>timestamp</type>)</entry>
	<entry><type>double precision</type></entry>
	<entry>Get subfield (equivalent to
	 <function>extract</function>); see also <link
                                                linkend="functions-datetime-datepart">below</link>
        </entry>
	<entry><literal>date_part('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
	<entry><literal>20</literal></entry>
       </row>

       <row>
	<entry><function>date_part</function>(<type>text</type>, <type>interval</type>)</entry>
	<entry><type>double precision</type></entry>
	<entry>Get subfield (equivalent to
	 <function>extract</function>); see also <link
                                                linkend="functions-datetime-datepart">below</link>
        </entry>
	<entry><literal>date_part('month', interval '2 years 3 months')</literal></entry>
	<entry><literal>3</literal></entry>
       </row>

       <row>
	<entry><function>date_trunc</function>(<type>text</type>, <type>timestamp</type>)</entry>
	<entry><type>timestamp</type></entry>
	<entry>Truncate to specified precision; see also <xref
                                                        linkend="functions-datetime-trunc">
        </entry>
	<entry><literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal></entry>
	<entry><literal>2001-02-16 20:00:00+00</literal></entry>
       </row>

       <row>
	<entry><function>extract</function>(<parameter>field</parameter> from
         <type>timestamp</type>)</entry>
	<entry><type>double precision</type></entry>
	<entry>Get subfield; see also <xref
                                     linkend="functions-datetime-extract">
        </entry>
	<entry><literal>extract(hour from timestamp '2001-02-16 20:38:40')</literal></entry>
	<entry><literal>20</literal></entry>
       </row>

       <row>
	<entry><function>extract</function>(<parameter>field</parameter> from
         <type>interval</type>)</entry>
	<entry><type>double precision</type></entry>
	<entry>Get subfield; see also <xref
                                     linkend="functions-datetime-extract">
        </entry>
	<entry><literal>extract(month from interval '2 years 3 months')</literal></entry>
	<entry><literal>3</literal></entry>
       </row>

       <row>
	<entry><function>isfinite</function>(<type>timestamp</type>)</entry>
	<entry><type>boolean</type></entry>
	<entry>Test for finite time stamp (neither invalid nor infinity)</entry>
	<entry><literal>isfinite(timestamp '2001-02-16 21:28:30')</literal></entry>
	<entry><literal>true</literal></entry>
       </row>

       <row>
	<entry><function>isfinite</function>(<type>interval</type>)</entry>
	<entry><type>boolean</type></entry>
	<entry>Test for finite interval</entry>
	<entry><literal>isfinite(interval '4 hours')</literal></entry>
	<entry><literal>true</literal></entry>
       </row>

       <row>
        <entry><function>localtime</function></entry>
        <entry><type>time</type></entry>
        <entry>Time of day; see <xref linkend="functions-datetime-current">
        </entry>
        <entry></entry>
        <entry></entry>
       </row>

       <row>
        <entry><function>localtimestamp</function></entry>
        <entry><type>timestamp</type></entry>
        <entry>Date and time; see <xref linkend="functions-datetime-current">
        </entry>
        <entry></entry>
        <entry></entry>
       </row>

       <row>
	<entry><function>now</function>()</entry>
	<entry><type>timestamp with time zone</type></entry>
	<entry>Current date and time (equivalent to
	 <function>current_timestamp</function>); see <xref
                                                     linkend="functions-datetime-current">
	</entry>
	<entry></entry>
	<entry></entry>
       </row>

       <row>
	<entry><function>timeofday()</function></entry>
	<entry><type>text</type></entry>
	<entry>Current date and time; see <xref
                                         linkend="functions-datetime-current">
	</entry>
	<entry><literal>timeofday()</literal></entry>
	<entry><literal>Wed Feb 21 17:01:13.000126 2001 EST</literal></entry>
       </row>

      </tbody>
     </tgroup>
    </table>

  <sect2 id="functions-datetime-extract">
   <title><function>EXTRACT</function>, <function>date_part</function></title>

<synopsis>
EXTRACT (<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
</synopsis>

   <para>
    The <function>extract</function> function retrieves subfields
    from date/time values, such as year or hour.
    <replaceable>source</replaceable> is a value expression that
    evaluates to type <type>timestamp</type> or <type>interval</type>.
    (Expressions of type <type>date</type> or <type>time</type> will
    be cast to <type>timestamp</type> and can therefore be used as
    well.)  <replaceable>field</replaceable> is an identifier or
    string that selects what field to extract from the source value.
    The <function>extract</function> function returns values of type
    <type>double precision</type>.
    The following are valid values:

    <!-- alphabetical -->
    <variablelist>
     <varlistentry>
      <term><literal>century</literal></term>
      <listitem>
       <para>
        The year field divided by 100
       </para>

<screen>
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
</screen>

       <para>
        Note that the result for the century field is simply the year field
        divided by 100, and not the conventional definition which puts most
        years in the 1900's in the twentieth century.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>day</literal></term>
      <listitem>
       <para>
        The day (of the month) field (1 - 31)
       </para>

<screen>
SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>decade</literal></term>
      <listitem>
       <para>
        The year field divided by 10
       </para>

<screen>
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>dow</literal></term>
      <listitem>
       <para>
        The day of the week (0 - 6; Sunday is 0) (for
        <type>timestamp</type> values only)
       </para>

<screen>
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>doy</literal></term>
      <listitem>
       <para>
        The day of the year (1 - 365/366) (for <type>timestamp</type> values only)
       </para>

<screen>
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>epoch</literal></term>
      <listitem>
       <para>
        For <type>date</type> and <type>timestamp</type> values, the
        number of seconds since 1970-01-01 00:00:00-00 (can be negative);
	for <type>interval</type> values, the total number
        of seconds in the interval
       </para>

<screen>
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>982352320</computeroutput>

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
<lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>hour</literal></term>
      <listitem>
       <para>
        The hour field (0 - 23)
       </para>

<screen>
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>microseconds</literal></term>
      <listitem>
       <para>
        The seconds field, including fractional parts, multiplied by 1
        000 000.  Note that this includes full seconds.
       </para>

<screen>
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
<lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>millennium</literal></term>
      <listitem>
       <para>
        The year field divided by 1000
       </para>

<screen>
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
</screen>

       <para>
        Note that the result for the millennium field is simply the year field
        divided by 1000, and not the conventional definition which puts
        years in the 1900's in the second millennium.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>milliseconds</literal></term>
      <listitem>
       <para>
        The seconds field, including fractional parts, multiplied by
        1000.  Note that this includes full seconds.
       </para>

<screen>
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
<lineannotation>Result: </lineannotation><computeroutput>28500</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>minute</literal></term>
      <listitem>
       <para>
        The minutes field (0 - 59)
       </para>

<screen>
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>month</literal></term>
      <listitem>
       <para>
        For <type>timestamp</type> values, the number of the month
        within the year (1 - 12) ; for <type>interval</type> values
        the number of months, modulo 12 (0 - 11)
       </para>

<screen>
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>quarter</literal></term>
      <listitem>
       <para>
        The quarter of the year (1 - 4) that the day is in (for
        <type>timestamp</type> values only)
       </para>

<screen>
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>second</literal></term>
      <listitem>
       <para>
        The seconds field, including fractional parts (0 -
        59<footnote><simpara>60 if leap seconds are
        implemented by the operating system</simpara></footnote>)
       </para>

<screen>
SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>

SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
<lineannotation>Result: </lineannotation><computeroutput>28.5</computeroutput>
</screen>
      </listitem>
     </varlistentry>
<!--
     <varlistentry>
      <term><literal>timezone</literal></term>
      <listitem>
       <para>
        The time zone offset. XXX But in what units?
       </para>
      </listitem>
     </varlistentry>
-->

     <varlistentry>
      <term><literal>timezone_hour</literal></term>
      <listitem>
       <para>
        The hour component of the time zone offset.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>timezone_minute</literal></term>
      <listitem>
       <para>
        The minute component of the time zone offset.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>week</literal></term>
      <listitem>
       <para>
        From a <type>timestamp</type> value, calculate the number of
        the week of the year that the day is in.  By definition
        (<acronym>ISO</acronym> 8601), the first week of a year
        contains January 4 of that year.  (The <acronym>ISO</acronym>
        week starts on Monday.)  In other words, the first Thursday of
        a year is in week 1 of that year.
       </para>

<screen>
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
</screen>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>year</literal></term>
      <listitem>
       <para>
        The year field
       </para>

<screen>
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>
</screen>
      </listitem>
     </varlistentry>

    </variablelist>

   </para>

   <para>
    The <function>extract</function> function is primarily intended
    for computational processing.  For formatting date/time values for
    display, see <xref linkend="functions-formatting">.
   </para>

   <anchor id="functions-datetime-datepart">
   <para>
    The <function>date_part</function> function is modeled on the traditional
    <productname>Ingres</productname> equivalent to the
    <acronym>SQL</acronym>-standard function <function>extract</function>:
<synopsis>
date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
</synopsis>
    Note that here the <replaceable>field</replaceable> parameter needs to
    be a string value, not a name.  The valid field values for
    <function>date_part</function> are the same as for
    <function>extract</function>.
   </para>

<screen>
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>

SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
<lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
</screen>

  </sect2>

  <sect2 id="functions-datetime-trunc">
   <title><function>date_trunc</function></title>

   <para>
    The function <function>date_trunc</function> is conceptually
    similar to the <function>trunc</function> function for numbers.
   </para>

   <para>
<synopsis>
date_trunc('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
</synopsis>
    <replaceable>source</replaceable> is a value expression of type
    <type>timestamp</type> (values of type <type>date</type> and
    <type>time</type> are cast automatically).
    <replaceable>field</replaceable> selects to which precision to
    truncate the time stamp value.  The return value is of type
    <type>timestamp</type> with all fields that are less than the
    selected one set to zero (or one, for day and month).
   </para>

   <para>
    Valid values for <replaceable>field</replaceable> are:
    <simplelist>
     <member>microseconds</member>
     <member>milliseconds</member>
     <member>second</member>
     <member>minute</member>
     <member>hour</member>
     <member>day</member>
     <member>month</member>
     <member>year</member>
     <member>decade</member>
     <member>century</member>
     <member>millennium</member>
    </simplelist>
   </para>

   <para>
    Examples:
<screen>
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00+00</computeroutput>

SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00+00</computeroutput>
</screen>
   </para>
  </sect2>

  <sect2 id="functions-datetime-zoneconvert">
   <title><function>AT TIME ZONE</function></title>

   <indexterm>
    <primary>timezone</primary>
    <secondary>conversion</secondary>
   </indexterm>

   <para>
    The <function>AT TIME ZONE</function> construct allows conversions
    of timestamps to different timezones.
   </para>

    <table id="functions-datetime-zoneconvert-table">
     <title>AT TIME ZONE Variants</title>
     <tgroup cols="3">
      <thead>
       <row>
	<entry>Expression</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
       </row>
      </thead>

      <tbody>

       <row>
	<entry>
	 <type>timestamp without time zone</type>
	 <literal>AT TIME ZONE</literal>
	 <replaceable>zone</>
	</entry>
	<entry><type>timestamp with time zone</type></entry>
	<entry>Convert local time in given timezone to UTC</entry>
       </row>

       <row>
	<entry>
	 <type>timestamp with time zone</type>
	 <literal>AT TIME ZONE</literal>
	 <replaceable>zone</>
	</entry>
	<entry><type>timestamp without time zone</type></entry>
	<entry>Convert UTC to local time in given timezone</entry>
       </row>

       <row>
	<entry>
	 <type>time with time zone</type>
	 <literal>AT TIME ZONE</literal>
	 <replaceable>zone</>
	</entry>
	<entry><type>time with time zone</type></entry>
	<entry>Convert local time across timezones</entry>
       </row>

      </tbody>
     </tgroup>
    </table>

   <para>
    In these expressions, the desired time <replaceable>zone</> can be
    specified either as a text string (e.g., <literal>'PST'</literal>)
    or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
   </para>

   <para>
    Examples (supposing that <varname>TimeZone</> is <literal>PST8PDT</>):
<screen>
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
</screen>
    The first example takes a zone-less timestamp and interprets it as MST time
    (GMT-7) to produce a UTC timestamp, which is then rotated to PST (GMT-8)
    for display.  The second example takes a timestamp specified in EST
    (GMT-5) and converts it to local time in MST (GMT-7).
   </para>

   <para>
    The function <function>timezone</function>(<replaceable>zone</>,
    <replaceable>timestamp</>) is equivalent to the SQL-compliant construct
    <replaceable>timestamp</> <literal>AT TIME ZONE</literal>
    <replaceable>zone</>. 
   </para>
  </sect2>

  <sect2 id="functions-datetime-current">
   <title>Current Date/Time</title>

   <indexterm>
    <primary>date</primary>
    <secondary>current</secondary>
   </indexterm>

   <indexterm>
    <primary>time</primary>
    <secondary>current</secondary>
   </indexterm>

   <para>
    The following functions are available to obtain the current date and/or
    time:
<synopsis>
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME ( <replaceable>precision</replaceable> )
CURRENT_TIMESTAMP ( <replaceable>precision</replaceable> )
LOCALTIME
LOCALTIMESTAMP
LOCALTIME ( <replaceable>precision</replaceable> )
LOCALTIMESTAMP ( <replaceable>precision</replaceable> )
</synopsis>
    </para>

    <para>
     <function>CURRENT_TIME</function> and
     <function>CURRENT_TIMESTAMP</function> deliver values with time zone;
     <function>LOCALTIME</function> and
     <function>LOCALTIMESTAMP</function> deliver values without time zone.
    </para>

    <para>
     <function>CURRENT_TIME</function>,
     <function>CURRENT_TIMESTAMP</function>,
     <function>LOCALTIME</function>, and
     <function>LOCALTIMESTAMP</function>
     can optionally be given
     a precision parameter, which causes the result to be rounded
     to that many fractional digits.  Without a precision parameter,
     the result is given to the full available precision.
    </para>

    <note>
     <para>
      Prior to <productname>PostgreSQL</productname> 7.2, the precision
      parameters were unimplemented, and the result was always given
      in integer seconds.
     </para>
    </note>

   <para>
    Some examples:
<screen>
SELECT CURRENT_TIME;
<computeroutput>14:39:53.662522-05</computeroutput>

SELECT CURRENT_DATE;
<computeroutput>2001-12-23</computeroutput>

SELECT CURRENT_TIMESTAMP;
<computeroutput>2001-12-23 14:39:53.662522-05</computeroutput>

SELECT CURRENT_TIMESTAMP(2);
<computeroutput>2001-12-23 14:39:53.66-05</computeroutput>

SELECT LOCALTIMESTAMP;
<computeroutput>2001-12-23 14:39:53.662522</computeroutput>
</screen>
   </para>

   <para>
    The function <function>now()</function> is the traditional
    <productname>PostgreSQL</productname> equivalent to
    <function>CURRENT_TIMESTAMP</function>.
   </para>

   <para>
    There is also <function>timeofday()</function>, which for historical
    reasons returns a text string rather than a <type>timestamp</type> value:
<screen>
SELECT timeofday();
 Sat Feb 17 19:07:32.000126 2001 EST
</screen>
   </para>

   <para>
    It is important to realize that
    <function>CURRENT_TIMESTAMP</function> and related functions return
    the start time of the current transaction; their values do not
    change during the transaction. <function>timeofday()</function>
    returns the wall clock time and does advance during transactions.
   </para>

   <note>
    <para>  
     Many other database systems advance these values more
     frequently.
    </para>
   </note>

   <para>
    All the date/time data types also accept the special literal value
    <literal>now</literal> to specify the current date and time.  Thus,
    the following three all return the same result:
<programlisting>
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';
</programlisting>
   </para>

    <note>
     <para>
      You do not want to use the third form when specifying a <literal>DEFAULT</>
      clause while creating a table.  The system will convert <literal>now</literal>
      to a <type>timestamp</type> as soon as the constant is parsed, so that when
      the default value is needed,
      the time of the table creation would be used!  The first two
      forms will not be evaluated until the default value is used,
      because they are function calls.  Thus they will give the desired
      behavior of defaulting to the time of row insertion.
     </para>
    </note>
  </sect2>
 </sect1>

  
 <sect1 id="functions-geometry">
  <title>Geometric Functions and Operators</title>

   <para>
    The geometric types <type>point</type>, <type>box</type>,
    <type>lseg</type>, <type>line</type>, <type>path</type>,
    <type>polygon</type>, and <type>circle</type> have a large set of
    native support functions and operators, shown in <xref
    linkend="functions-geometry-op-table">, <xref
    linkend="functions-geometry-func-table">, and <xref
    linkend="functions-geometry-conv-table">.
   </para>

   <table id="functions-geometry-op-table">
     <title>Geometric Operators</title>
     <tgroup cols="3">
      <thead>
       <row>
	<entry>Operator</entry>
	<entry>Description</entry>
	<entry>Usage</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> + </entry>
	<entry>Translation</entry>
	<entry><literal>box '((0,0),(1,1))' + point '(2.0,0)'</literal></entry>
       </row>
       <row>
	<entry> - </entry>
	<entry>Translation</entry>
	<entry><literal>box '((0,0),(1,1))' - point '(2.0,0)'</literal></entry>
       </row>
       <row>
	<entry> * </entry>
	<entry>Scaling/rotation</entry>
	<entry><literal>box '((0,0),(1,1))' * point '(2.0,0)'</literal></entry>
       </row>
       <row>
	<entry> / </entry>
	<entry>Scaling/rotation</entry>
	<entry><literal>box '((0,0),(2,2))' / point '(2.0,0)'</literal></entry>
       </row>
       <row>
	<entry> # </entry>
	<entry>Intersection</entry>
	<entry><literal>'((1,-1),(-1,1))' # '((1,1),(-1,-1))'</literal></entry>
       </row>
       <row>
	<entry> # </entry>
	<entry>Number of points in path or polygon</entry>
	<entry><literal># '((1,0),(0,1),(-1,0))'</literal></entry>
       </row>
       <row>
	<entry> ## </entry>
	<entry>Point of closest proximity</entry>
	<entry><literal>point '(0,0)' ## lseg '((2,0),(0,2))'</literal></entry>
       </row>
       <row>
	<entry> &amp;&amp; </entry>
	<entry>Overlaps?</entry>
	<entry><literal>box '((0,0),(1,1))' &amp;&amp; box '((0,0),(2,2))'</literal></entry>
       </row>
       <row>
	<entry> &amp;&lt; </entry>
	<entry>Overlaps to left?</entry>
	<entry><literal>box '((0,0),(1,1))' &amp;&lt; box '((0,0),(2,2))'</literal></entry>
       </row>
       <row>
	<entry> &amp;&gt; </entry>
	<entry>Overlaps to right?</entry>
	<entry><literal>box '((0,0),(3,3))' &amp;&gt; box '((0,0),(2,2))'</literal></entry>
       </row>
       <row>
	<entry> &lt;-&gt; </entry>
	<entry>Distance between</entry>
	<entry><literal>circle '((0,0),1)' &lt;-&gt; circle '((5,0),1)'</literal></entry>
       </row>
       <row>
	<entry> &lt;&lt; </entry>
	<entry>Left of?</entry>
	<entry><literal>circle '((0,0),1)' &lt;&lt; circle '((5,0),1)'</literal></entry>
       </row>
       <row>
	<entry> &lt;^ </entry>
	<entry>Is below?</entry>
	<entry><literal>circle '((0,0),1)' &lt;^ circle '((0,5),1)'</literal></entry>
       </row>
       <row>
	<entry> &gt;&gt; </entry>
	<entry>Is right of?</entry>
	<entry><literal>circle '((5,0),1)' &gt;&gt; circle '((0,0),1)'</literal></entry>
       </row>
       <row>
	<entry> &gt;^ </entry>
	<entry>Is above?</entry>
	<entry><literal>circle '((0,5),1)' >^ circle '((0,0),1)'</literal></entry>
       </row>
       <row>
	<entry> ?# </entry>
	<entry>Intersects or overlaps</entry>
	<entry><literal>lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))'</literal></entry>
       </row>
       <row>
	<entry> ?- </entry>
	<entry>Is horizontal?</entry>
	<entry><literal>point '(1,0)' ?- point '(0,0)'</literal></entry>
       </row>
       <row>
	<entry> ?-| </entry>
	<entry>Is perpendicular?</entry>
	<entry><literal>lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))'</literal></entry>
       </row>
       <row>
	<entry> @-@  </entry>
	<entry>Length or circumference</entry>
	<entry><literal>@-@ path '((0,0),(1,0))'</literal></entry>
       </row>
       <row>
	<entry> ?| </entry>
	<entry>Is vertical?</entry>
	<entry><literal>point '(0,1)' ?| point '(0,0)'</literal></entry>
       </row>
       <row>
	<entry> ?|| </entry>
	<entry>Is parallel?</entry>
	<entry><literal>lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))'</literal></entry>
       </row>
       <row>
	<entry> @ </entry>
	<entry>Contained or on</entry>
	<entry><literal>point '(1,1)' @ circle '((0,0),2)'</literal></entry>
       </row>
       <row>
	<entry> @@ </entry>
	<entry>Center of</entry>
	<entry><literal>@@ circle '((0,0),10)'</literal></entry>
       </row>
       <row>
	<entry> ~= </entry>
	<entry>Same as</entry>
	<entry><literal>polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'</literal></entry>
       </row>
      </tbody>
     </tgroup>
   </table>


   <table id="functions-geometry-func-table">
     <title>Geometric Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><function>area</function>(object)</entry>
	<entry><type>double precision</type></entry>
	<entry>area of item</entry>
	<entry><literal>area(box '((0,0),(1,1))')</literal></entry>
       </row>
       <row>
	<entry><function>box</function>(box, box)</entry>
	<entry><type>box</type></entry>
	<entry>intersection box</entry>
	<entry><literal>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</literal></entry>
       </row>
       <row>
	<entry><function>center</function>(object)</entry>
	<entry><type>point</type></entry>
	<entry>center of item</entry>
	<entry><literal>center(box '((0,0),(1,2))')</literal></entry>
       </row>
       <row>
	<entry><function>diameter</function>(circle)</entry>
	<entry><type>double precision</type></entry>
	<entry>diameter of circle</entry>
	<entry><literal>diameter(circle '((0,0),2.0)')</literal></entry>
       </row>
       <row>
	<entry><function>height</function>(box)</entry>
	<entry><type>double precision</type></entry>
	<entry>vertical size of box</entry>
	<entry><literal>height(box '((0,0),(1,1))')</literal></entry>
       </row>
       <row>
	<entry><function>isclosed</function>(path)</entry>
	<entry><type>boolean</type></entry>
	<entry>a closed path?</entry>
	<entry><literal>isclosed(path '((0,0),(1,1),(2,0))')</literal></entry>
       </row>
       <row>
	<entry><function>isopen</function>(path)</entry>
	<entry><type>boolean</type></entry>
	<entry>an open path?</entry>
	<entry><literal>isopen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
       </row>
       <row>
	<entry><function>length</function>(object)</entry>
	<entry><type>double precision</type></entry>
	<entry>length of item</entry>
	<entry><literal>length(path '((-1,0),(1,0))')</literal></entry>
       </row>
       <row>
	<entry><function>npoints</function>(path)</entry>
	<entry><type>integer</type></entry>
	<entry>number of points</entry>
	<entry><literal>npoints(path '[(0,0),(1,1),(2,0)]')</literal></entry>
       </row>
       <row>
	<entry><function>npoints</function>(polygon)</entry>
	<entry><type>integer</type></entry>
	<entry>number of points</entry>
	<entry><literal>npoints(polygon '((1,1),(0,0))')</literal></entry>
       </row>
       <row>
	<entry><function>pclose</function>(path)</entry>
	<entry><type>path</type></entry>
	<entry>convert path to closed</entry>
	<entry><literal>popen(path '[(0,0),(1,1),(2,0)]')</literal></entry>
       </row>
<![IGNORE[
<!-- Not defined by this name. Implements the intersection operator '#' -->
       <row>
	<entry><function>point</function>(lseg,lseg)</entry>
	<entry><type>point</type></entry>
	<entry>intersection</entry>
	<entry><literal>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</literal></entry>
       </row>
]]>
       <row>
	<entry><function>popen</function>(path)</entry>
	<entry><type>path</type></entry>
	<entry>convert path to open path</entry>
	<entry><literal>popen(path '((0,0),(1,1),(2,0))')</literal></entry>
       </row>
       <row>
	<entry><function>radius</function>(circle)</entry>
	<entry><type>double precision</type></entry>
	<entry>radius of circle</entry>
	<entry><literal>radius(circle '((0,0),2.0)')</literal></entry>
       </row>
       <row>
	<entry><function>width</function>(box)</entry>
	<entry><type>double precision</type></entry>
	<entry>horizontal size</entry>
	<entry><literal>width(box '((0,0),(1,1))')</literal></entry>
       </row>
      </tbody>
     </tgroup>
   </table>


   <table id="functions-geometry-conv-table">
     <title>Geometric Type Conversion Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><function>box</function>(<type>circle</type>)</entry>
	<entry><type>box</type></entry>
	<entry>circle to box</entry>
	<entry><literal>box(circle '((0,0),2.0)')</literal></entry>
       </row>
       <row>
	<entry><function>box</function>(<type>point</type>, <type>point</type>)</entry>
	<entry><type>box</type></entry>
	<entry>points to box</entry>
	<entry><literal>box(point '(0,0)', point '(1,1)')</literal></entry>
       </row>
       <row>
	<entry><function>box</function>(<type>polygon</type>)</entry>
	<entry><type>box</type></entry>
	<entry>polygon to box</entry>
	<entry><literal>box(polygon '((0,0),(1,1),(2,0))')</literal></entry>
       </row>
       <row>
	<entry><function>circle</function>(<type>box</type>)</entry>
	<entry><type>circle</type></entry>
	<entry>to circle</entry>
	<entry><literal>circle(box '((0,0),(1,1))')</literal></entry>
       </row>
       <row>
	<entry><function>circle</function>(<type>point</type>, <type>double precision</type>)</entry>
	<entry><type>circle</type></entry>
	<entry>point to circle</entry>
	<entry><literal>circle(point '(0,0)', 2.0)</literal></entry>
       </row>
       <row>
	<entry><function>lseg</function>(<type>box</type>)</entry>
	<entry><type>lseg</type></entry>
	<entry>box diagonal to <type>lseg</type></entry>
	<entry><literal>lseg(box '((-1,0),(1,0))')</literal></entry>
       </row>
       <row>
	<entry><function>lseg</function>(<type>point</type>, <type>point</type>)</entry>
	<entry><type>lseg</type></entry>
	<entry>points to <type>lseg</type></entry>
	<entry><literal>lseg(point '(-1,0)', point '(1,0)')</literal></entry>
       </row>
       <row>
	<entry><function>path</function>(<type>polygon</type>)</entry>
	<entry><type>point</type></entry>
	<entry>polygon to path</entry>
	<entry><literal>path(polygon '((0,0),(1,1),(2,0))')</literal></entry>
       </row>
       <row>
	<entry><function>point</function>(<type>circle</type>)</entry>
	<entry><type>point</type></entry>
	<entry>center</entry>
	<entry><literal>point(circle '((0,0),2.0)')</literal></entry>
       </row>
       <row>
	<entry><function>point</function>(<type>lseg</type>, <type>lseg</type>)</entry>
	<entry><type>point</type></entry>
	<entry>intersection</entry>
	<entry><literal>point(lseg '((-1,0),(1,0))', lseg '((-2,-2),(2,2))')</literal></entry>
       </row>
       <row>
	<entry><function>point</function>(<type>polygon</type>)</entry>
	<entry><type>point</type></entry>
	<entry>center</entry>
	<entry><literal>point(polygon '((0,0),(1,1),(2,0))')</literal></entry>
       </row>
       <row>
	<entry><function>polygon</function>(<type>box</type>)</entry>
	<entry><type>polygon</type></entry>
	<entry>4-point polygon</entry>
	<entry><literal>polygon(box '((0,0),(1,1))')</literal></entry>
       </row>
       <row>
	<entry><function>polygon</function>(<type>circle</type>)</entry>
	<entry><type>polygon</type></entry>
	<entry>12-point polygon</entry>
	<entry><literal>polygon(circle '((0,0),2.0)')</literal></entry>
       </row>
       <row>
	<entry><function>polygon</function>(<replaceable class="parameter">npts</replaceable>, <type>circle</type>)</entry>
	<entry><type>polygon</type></entry>
	<entry><replaceable class="parameter">npts</replaceable> polygon</entry>
	<entry><literal>polygon(12, circle '((0,0),2.0)')</literal></entry>
       </row>
       <row>
	<entry><function>polygon</function>(<type>path</type>)</entry>
	<entry><type>polygon</type></entry>
	<entry>path to polygon</entry>
	<entry><literal>polygon(path '((0,0),(1,1),(2,0))')</literal></entry>
       </row>
      </tbody>
     </tgroup>
   </table>

    <para>
     It is possible to access the two component numbers of a <type>point</>
     as though it were an array with subscripts 0, 1.  For example, if
     <literal>t.p</> is a <type>point</> column then
     <literal>SELECT p[0] FROM t</> retrieves the X coordinate;
     <literal>UPDATE t SET p[1] = ...</> changes the Y coordinate.
     In the same way, a <type>box</> or an <type>lseg</> may be treated
     as an array of two <type>point</>s.
    </para>

  </sect1>


 <sect1 id="functions-net">
  <title>Network Address Type Functions</title>

  <para>
   <xref linkend="cidr-inet-operators-table"> shows the operators
   available for the <type>inet</type> and <type>cidr</type> types.
   The operators <literal>&lt;&lt;</literal>,
   <literal>&lt;&lt;=</literal>, <literal>&gt;&gt;</literal>,
   <literal>&gt;&gt;=</literal> test for subnet inclusion: they
   consider only the network parts of the two addresses, ignoring any
   host part, and determine whether one network part is identical to
   or a subnet of the other.
  </para>

    <table tocentry="1" id="cidr-inet-operators-table">
     <title><type>cidr</type> and <type>inet</type> Operators</title>
     <tgroup cols="3">
      <thead>
       <row>
	<entry>Operator</entry>
	<entry>Description</entry>
	<entry>Usage</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> &lt; </entry>
	<entry>Less than</entry>
	<entry><literal>inet '192.168.1.5' &lt; inet '192.168.1.6'</literal></entry>
       </row>
       <row>
	<entry> &lt;= </entry>
	<entry>Less than or equal</entry>
	<entry><literal>inet '192.168.1.5' &lt;= inet '192.168.1.5'</literal></entry>
       </row>
       <row>
	<entry> = </entry>
	<entry>Equals</entry>
	<entry><literal>inet '192.168.1.5' = inet '192.168.1.5'</literal></entry>
       </row>
       <row>
	<entry> &gt;= </entry>
	<entry>Greater or equal</entry>
	<entry><literal>inet '192.168.1.5' &gt;= inet '192.168.1.5'</literal></entry>
       </row>
       <row>
	<entry> &gt; </entry>
	<entry>Greater</entry>
	<entry><literal>inet '192.168.1.5' &gt; inet '192.168.1.4'</literal></entry>
       </row>
       <row>
	<entry> &lt;&gt; </entry>
	<entry>Not equal</entry>
	<entry><literal>inet '192.168.1.5' &lt;&gt; inet '192.168.1.4'</literal></entry>
       </row>
       <row>
	<entry> &lt;&lt; </entry>
	<entry>is contained within</entry>
	<entry><literal>inet '192.168.1.5' &lt;&lt; inet '192.168.1/24'</literal></entry>
       </row>
       <row>
	<entry> &lt;&lt;= </entry>
	<entry>is contained within or equals</entry>
	<entry><literal>inet '192.168.1/24' &lt;&lt;= inet '192.168.1/24'</literal></entry>
       </row>
       <row>
	<entry> &gt;&gt; </entry>
	<entry>contains</entry>
	<entry><literal>inet'192.168.1/24' &gt;&gt; inet '192.168.1.5'</literal></entry>
       </row>
       <row>
	<entry> &gt;&gt;= </entry>
	<entry>contains or equals</entry>
	<entry><literal>inet '192.168.1/24' &gt;&gt;= inet '192.168.1/24'</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

  <para>
   <xref linkend="cidr-inet-functions-table"> shows the functions
   available for use with the <type>inet</type> and <type>cidr</type>
   types.  The <function>host()</function>,
   <function>text()</function>, and <function>abbrev()</function>
   functions are primarily intended to offer alternative display
   formats. You can cast a text field to inet using normal casting
   syntax: <literal>inet(expression)</literal> or
   <literal>colname::inet</literal>.
  </para>

    <table tocentry="1" id="cidr-inet-functions-table">
     <title><type>cidr</type> and <type>inet</type> Functions</title>
     <tgroup cols="5">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
	<entry>Result</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><function>broadcast</function>(<type>inet</type>)</entry>
	<entry><type>inet</type></entry>
	<entry>broadcast address for network</entry>
	<entry><literal>broadcast('192.168.1.5/24')</literal></entry>
	<entry><literal>192.168.1.255/24</literal></entry>
       </row>
       <row>
	<entry><function>host</function>(<type>inet</type>)</entry>
	<entry><type>text</type></entry>
	<entry>extract IP address as text</entry>
	<entry><literal>host('192.168.1.5/24')</literal></entry>
	<entry><literal>192.168.1.5</literal></entry>
       </row>
       <row>
	<entry><function>masklen</function>(<type>inet</type>)</entry>
	<entry><type>integer</type></entry>
	<entry>extract netmask length</entry>
	<entry><literal>masklen('192.168.1.5/24')</literal></entry>
	<entry><literal>24</literal></entry>
       </row>
       <row>
	<entry><function>set_masklen</function>(<type>inet</type>,<type>integer</type>)</entry>
	<entry><type>inet</type></entry>
	<entry>set netmask length for <type>inet</type> value</entry>
	<entry><literal>set_masklen('192.168.1.5/24',16)</literal></entry>
	<entry><literal>192.168.1.5/16</literal></entry>
       </row>
       <row>
	<entry><function>netmask</function>(<type>inet</type>)</entry>
	<entry><type>inet</type></entry>
	<entry>construct netmask for network</entry>
	<entry><literal>netmask('192.168.1.5/24')</literal></entry>
	<entry><literal>255.255.255.0</literal></entry>
       </row>
       <row>
	<entry><function>network</function>(<type>inet</type>)</entry>
	<entry><type>cidr</type></entry>
	<entry>extract network part of address</entry>
	<entry><literal>network('192.168.1.5/24')</literal></entry>
	<entry><literal>192.168.1.0/24</literal></entry>
       </row>
       <row>
	<entry><function>text</function>(<type>inet</type>)</entry>
	<entry><type>text</type></entry>
	<entry>extract IP address and masklen as text</entry>
	<entry><literal>text(inet '192.168.1.5')</literal></entry>
	<entry><literal>192.168.1.5/32</literal></entry>
       </row>
       <row>
	<entry><function>abbrev</function>(<type>inet</type>)</entry>
	<entry><type>text</type></entry>
	<entry>extract abbreviated display as text</entry>
	<entry><literal>abbrev(cidr '10.1.0.0/16')</literal></entry>
	<entry><literal>10.1/16</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

  <para>
   <xref linkend="macaddr-functions-table"> shows the functions
   available for use with the <type>mac</type> type.  The function
   <function>trunc</function>(<type>macaddr</type>) returns a MAC
   address with the last 3 bytes set to 0.  This can be used to
   associate the remaining prefix with a manufacturer.  The directory
   <filename>contrib/mac</filename> in the source distribution
   contains some utilities to create and maintain such an association
   table.
  </para>

    <table tocentry="1" id="macaddr-functions-table">
     <title><type>macaddr</type> Functions</title>
     <tgroup cols="5">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
	<entry>Result</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><function>trunc</function>(<type>macaddr</type>)</entry>
	<entry><type>macaddr</type></entry>
	<entry>set last 3 bytes to zero</entry>
	<entry><literal>trunc(macaddr '12:34:56:78:90:ab')</literal></entry>
	<entry><literal>12:34:56:00:00:00</literal></entry>
       </row>
      </tbody>
     </tgroup>
    </table>

   <para>
    The <type>macaddr</type> type also supports the standard relational
    operators (<literal>&gt;</literal>, <literal>&lt;=</literal>, etc.) for
    lexicographical ordering.
   </para>

  </sect1>


 <sect1 id="functions-sequence">
  <title>Sequence-Manipulation Functions</title>

  <indexterm>
   <primary>sequences</primary>
  </indexterm>
  <indexterm>
   <primary>nextval</primary>
  </indexterm>
  <indexterm>
   <primary>currval</primary>
  </indexterm>
  <indexterm>
   <primary>setval</primary>
  </indexterm>

  <para>
   This section describes <productname>PostgreSQL</productname>'s functions
   for operating on <firstterm>sequence objects</firstterm>.
   Sequence objects (also called sequence generators or
   just sequences) are special single-row tables created with
   <command>CREATE SEQUENCE</command>.  A sequence object is usually used to
   generate unique identifiers for rows of a table.  The sequence functions,
   listed in <xref linkend="functions-sequence-table">,
   provide simple, multiuser-safe methods for obtaining successive
   sequence values from sequence objects.
  </para>

   <table id="functions-sequence-table">
    <title>Sequence Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Function</entry> <entry>Returns</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
	<entry><function>nextval</function>(<type>text</type>)</entry>
	<entry><type>bigint</type></entry>
	<entry>Advance sequence and return new value</entry>
      </row>
      <row>
	<entry><function>currval</function>(<type>text</type>)</entry>
	<entry><type>bigint</type></entry>
	<entry>Return value most recently obtained with <function>nextval</function></entry>
      </row>
      <row>
	<entry><function>setval</function>(<type>text</type>,<type>bigint</type>)</entry>
	<entry><type>bigint</type></entry>
	<entry>Set sequence's current value</entry>
      </row>
      <row>
	<entry><function>setval</function>(<type>text</type>,<type>bigint</type>,<type>boolean</type>)</entry>
	<entry><type>bigint</type></entry>
	<entry>Set sequence's current value and <literal>is_called</literal> flag</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
   For largely historical reasons, the sequence to be operated on by
   a sequence-function call is specified by a text-string argument.
   To achieve some compatibility with the handling of ordinary SQL
   names, the sequence functions convert their argument to lower case
   unless the string is double-quoted.  Thus
<programlisting>
nextval('foo')      <lineannotation>operates on sequence <literal>foo</literal></>
nextval('FOO')      <lineannotation>operates on sequence <literal>foo</literal></>
nextval('"Foo"')    <lineannotation>operates on sequence <literal>Foo</literal></>
</programlisting>
   The sequence name can be schema-qualified if necessary:
<programlisting>
nextval('myschema.foo')     <lineannotation>operates on <literal>myschema.foo</literal></>
nextval('"myschema".foo')   <lineannotation>same as above</lineannotation>
nextval('foo')              <lineannotation>searches search path for <literal>foo</literal></>
</programlisting>
   Of course, the text argument can be the result of an expression,
   not only a simple literal, which is occasionally useful.
  </para>

  <para>
   The available sequence functions are:

    <variablelist>
     <varlistentry>
      <term><function>nextval</function></term>
      <listitem>
       <para>
        Advance the sequence object to its next value and return that
	value.  This is done atomically: even if multiple sessions
	execute <function>nextval</function> concurrently, each will safely receive
	a distinct sequence value.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><function>currval</function></term>
      <listitem>
       <para>
        Return the value most recently obtained by <function>nextval</function>
	for this sequence in the current session.  (An error is
	reported if <function>nextval</function> has never been called for this
	sequence in this session.)  Notice that because this is returning
	a session-local value, it gives a predictable answer even if other
	sessions are executing <function>nextval</function> meanwhile.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><function>setval</function></term>
      <listitem>
       <para>
        Reset the sequence object's counter value.  The two-parameter
	form sets the sequence's <literal>last_value</literal> field to the specified
	value and sets its <literal>is_called</literal> field to <literal>true</literal>,
	meaning that the next <function>nextval</function> will advance the sequence
	before returning a value.  In the three-parameter form,
	<literal>is_called</literal> may be set either <literal>true</literal> or
	<literal>false</literal>.  If it's set to <literal>false</literal>,
	the next <function>nextval</function> will return exactly the specified
	value, and sequence advancement commences with the following
	<function>nextval</function>.  For example,

<screen>
SELECT setval('foo', 42);           <lineannotation>Next <function>nextval()</> will return 43</lineannotation>
SELECT setval('foo', 42, true);     <lineannotation>Same as above</lineannotation>
SELECT setval('foo', 42, false);    <lineannotation>Next <function>nextval()</> will return 42</lineannotation>
</screen>

        The result returned by <function>setval</function> is just the value of its
	second argument.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
  </para>

  <important>
   <para>
    To avoid blocking of concurrent transactions that obtain numbers from the
    same sequence, a <function>nextval</function> operation is never rolled back;
    that is, once a value has been fetched it is considered used, even if the
    transaction that did the <function>nextval</function> later aborts.  This means
    that aborted transactions may leave unused <quote>holes</quote> in the
    sequence of assigned values.  <function>setval</function> operations are never
    rolled back, either.
   </para>
  </important>

  <para>
   If a sequence object has been created with default parameters,
   <function>nextval()</function> calls on it will return successive values
   beginning with one.  Other behaviors can be obtained by using
   special parameters in the <command>CREATE SEQUENCE</command> command;
   see its command reference page for more information.
  </para>

 </sect1>


 <sect1 id="functions-conditional">
  <title>Conditional Expressions</title>

  <indexterm>
   <primary>case</primary>
  </indexterm>

  <indexterm>
   <primary>conditionals</primary>
  </indexterm>

  <para>
   This section describes the <acronym>SQL</acronym>-compliant conditional expressions
   available in <productname>PostgreSQL</productname>.
  </para>

  <tip>
   <para>
    If your needs go beyond the capabilities of these conditional
    expressions you might want to consider writing a stored procedure
    in a more expressive programming language.
   </para>
  </tip>

  <sect2>
   <title>CASE</title>

<synopsis>
CASE WHEN <replaceable>condition</replaceable> THEN <replaceable>result</replaceable>
     <optional>WHEN ...</optional>
     <optional>ELSE <replaceable>result</replaceable></optional>
END
</synopsis>

  <para>
   The <acronym>SQL</acronym> <token>CASE</token> expression is a
   generic conditional expression, similar to if/else statements in
   other languages.  <token>CASE</token> clauses can be used wherever
   an expression is valid.  <replaceable>condition</replaceable> is an
   expression that returns a <type>boolean</type> result.  If the result is true
   then the value of the <token>CASE</token> expression is
   <replaceable>result</replaceable>.  If the result is false any
   subsequent <token>WHEN</token> clauses are searched in the same
   manner.  If no <token>WHEN</token>
   <replaceable>condition</replaceable> is true then the value of the
   case expression is the <replaceable>result</replaceable> in the
   <token>ELSE</token> clause.  If the <token>ELSE</token> clause is
   omitted and no condition matches, the result is null.
  </para>

   <para>
    An example:
<screen>
<prompt>=&gt;</prompt> <userinput>SELECT * FROM test;</userinput>
<computeroutput>
 a
---
 1
 2
 3
</computeroutput>

<prompt>=&gt;</prompt> <userinput>SELECT a,
          CASE WHEN a=1 THEN 'one'
               WHEN a=2 THEN 'two'
               ELSE 'other'
          END
    FROM test;</userinput>
<computeroutput>
 a | case
---+-------
 1 | one
 2 | two
 3 | other
</computeroutput>
</screen>
   </para>

  <para>
   The data types of all the <replaceable>result</replaceable>
   expressions must be coercible to a single output type.
   See <xref linkend="typeconv-union-case"> for more detail.
  </para>

<synopsis>
CASE <replaceable>expression</replaceable>
    WHEN <replaceable>value</replaceable> THEN <replaceable>result</replaceable>
    <optional>WHEN ...</optional>
    <optional>ELSE <replaceable>result</replaceable></optional>
END
</synopsis>

  <para>
   This <quote>simple</quote> <token>CASE</token> expression is a
   specialized variant of the general form above.  The
   <replaceable>expression</replaceable> is computed and compared to
   all the <replaceable>value</replaceable>s in the
   <token>WHEN</token> clauses until one is found that is equal.  If
   no match is found, the <replaceable>result</replaceable> in the
   <token>ELSE</token> clause (or a null value) is returned.  This is similar
   to the <function>switch</function> statement in C.
  </para>

   <para>
    The example above can be written using the simple
    <token>CASE</token> syntax:
<screen>
<prompt>=&gt;</prompt> <userinput>SELECT a,
          CASE a WHEN 1 THEN 'one'
                 WHEN 2 THEN 'two'
                 ELSE 'other'
          END
    FROM test;</userinput>
<computeroutput>
 a | case
---+-------
 1 | one
 2 | two
 3 | other
</computeroutput>
</screen>
    </para>
  </sect2>

  <sect2>
   <title>COALESCE</title>

<synopsis>
<function>COALESCE</function>(<replaceable>value</replaceable> <optional>, ...</optional>)
</synopsis>

  <para>
   The <function>COALESCE</function> function returns the first of its
   arguments that is not null.  This is often useful to substitute a
   default value for null values when data is retrieved for display,
   for example:
<programlisting>
SELECT COALESCE(description, short_description, '(none)') ...
</programlisting>
  </para>
  </sect2>

  <sect2>
   <title>NULLIF</title>

  <indexterm>
   <primary>nullif</primary>
  </indexterm>

<synopsis>
<function>NULLIF</function>(<replaceable>value1</replaceable>, <replaceable>value2</replaceable>)
</synopsis>

  <para>
   The <function>NULLIF</function> function returns a null value if and only
   if <replaceable>value1</replaceable> and
   <replaceable>value2</replaceable> are equal.  Otherwise it returns
   <replaceable>value1</replaceable>.  This can be used to perform the
   inverse operation of the <function>COALESCE</function> example
   given above:
<programlisting>
SELECT NULLIF(value, '(none)') ...
</programlisting>
  </para>

  <tip>
   <para>
    <function>COALESCE</function> and <function>NULLIF</function> are
    just shorthand for <token>CASE</token> expressions.  They are actually
    converted into <token>CASE</token> expressions at a very early stage
    of processing, and subsequent processing thinks it is dealing with
    <token>CASE</token>.  Thus an incorrect <function>COALESCE</function> or
    <function>NULLIF</function> usage may draw an error message that
    refers to <token>CASE</token>.
   </para>
  </tip>
  </sect2>

 </sect1>


 <sect1 id="functions-misc">
  <title>Miscellaneous Functions</title>

  <para>
   <xref linkend="functions-misc-session-table"> shows several
   functions that extract session and system information.
  </para>

   <table id="functions-misc-session-table">
    <title>Session Information Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
       <entry><function>current_database()</function></entry>
       <entry><type>name</type></entry>
       <entry>name of current database</entry>
      </row>

      <row>
       <entry><function>current_schema()</function></entry>
       <entry><type>name</type></entry>
       <entry>name of current schema</entry>
      </row>

      <row>
       <entry><function>current_schemas(boolean)</function></entry>
       <entry><type>name[]</type></entry>
       <entry>names of schemas in search path optionally including implicit schemas</entry>
      </row>

      <row>
       <entry><function>current_user</function></entry>
       <entry><type>name</type></entry>
       <entry>user name of current execution context</entry>
      </row>

      <row>
       <entry><function>session_user</function></entry>
       <entry><type>name</type></entry>
       <entry>session user name</entry>
      </row>

      <row>
       <entry><function>user</function></entry>
       <entry><type>name</type></entry>
       <entry>equivalent to <function>current_user</function></entry>
      </row>

      <row>
       <entry><function>version()</function></entry>
       <entry><type>text</type></entry>
       <entry>PostgreSQL version information</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>user</primary>
    <secondary>current</secondary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>schema</primary>
    <secondary>current</secondary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>search path</primary>
    <secondary>current</secondary>
   </indexterm>

   <para>
    The <function>session_user</function> is the user that initiated a
    database connection; it is fixed for the duration of that
    connection. The <function>current_user</function> is the user identifier
    that is applicable for permission checking. Normally, it is equal
    to the session user, but it changes during the execution of
    functions with the attribute <literal>SECURITY DEFINER</literal>.
    In Unix parlance, the session user is the <quote>real user</quote> and
    the current user is the <quote>effective user</quote>.
   </para>

   <note>
    <para>
     <function>current_user</function>, <function>session_user</function>, and
     <function>user</function> have special syntactic status in <acronym>SQL</acronym>:
     they must be called without trailing parentheses.
    </para>
   </note>

   <para>
    <function>current_schema</function> returns the name of the schema that is
    at the front of the search path (or a null value if the search path is
    empty).  This is the schema that will be used for any tables or
    other named objects that are created without specifying a target schema.
    <function>current_schemas(boolean)</function> returns an array of the names of all
    schemas presently in the search path.  The boolean option determines whether or not
    implicitly included system schemas such as pg_catalog are included in the search 
    path returned.
   </para>

   <para>
    <indexterm>
     <primary>search path</primary>
     <secondary>changing at runtime</secondary>
    </indexterm>
    The search path may be altered by a run-time setting.  The
    command to use is <command>
     SET SEARCH_PATH '<varname>schema</varname>'[,'<varname>schema</varname>']...
    </command>
   </para>

   <indexterm zone="functions-misc">
    <primary>version</primary>
   </indexterm>

   <para>
    <function>version()</function> returns a string describing the
    <productname>PostgreSQL</productname> server's version.
   </para>

  <para>
   <xref linkend="functions-misc-set-table"> shows the functions
   available to query and alter run-time configuration parameters.
  </para>

   <table id="functions-misc-set-table">
    <title>Configuration Settings Information Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
       <entry>
        <function>current_setting</function>(<parameter>setting_name</parameter>)
       </entry>
       <entry><type>text</type></entry>
       <entry>value of current setting</entry>
      </row>
      <row>
       <entry>
        <function>set_config(<parameter>setting_name</parameter>,
                             <parameter>new_value</parameter>,
                             <parameter>is_local</parameter>)</function>
       </entry>
       <entry><type>text</type></entry>
       <entry>new value of current setting</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>setting</primary>
    <secondary>current</secondary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>setting</primary>
    <secondary>set</secondary>
   </indexterm>

   <para>
    The <function>current_setting</function> is used to obtain the current
    value of the <parameter>setting_name</parameter> setting, as a query
    result. It is the equivalent to the SQL <command>SHOW</command> command.
    For example:
<programlisting>
select current_setting('DateStyle');
            current_setting
---------------------------------------
 ISO with US (NonEuropean) conventions
(1 row)
</programlisting>
   </para>

   <para>
    <function>set_config</function> allows the <parameter>setting_name
    </parameter> setting to be changed to <parameter>new_value</parameter>.
    If <parameter>is_local</parameter> is set to <literal>true</literal>,
    the new value will only apply to the current transaction. If you want
    the new value to apply for the current session, use
    <literal>false</literal> instead. It is the equivalent to the SQL
    <command>SET</command> command. For example:
<programlisting>
select set_config('show_statement_stats','off','f');
 set_config
------------
 off
(1 row)
</programlisting>
   </para>

  <para>
   <xref linkend="functions-misc-access-table"> lists functions that
   allow the user to query object access privileges programmatically.
   See <xref linkend="ddl-priv"> for more information about
   privileges.
  </para>

   <table id="functions-misc-access-table">
    <title>Access Privilege Inquiry Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
       <entry><function>has_table_privilege</function>(<parameter>user</parameter>,
                                  <parameter>table</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to table</entry>
      </row>
      <row>
       <entry><function>has_table_privilege</function>(<parameter>table</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to table</entry>
      </row>
      <row>
       <entry><function>has_database_privilege</function>(<parameter>user</parameter>,
                                  <parameter>database</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to database</entry>
      </row>
      <row>
       <entry><function>has_database_privilege</function>(<parameter>database</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to database</entry>
      </row>
      <row>
       <entry><function>has_function_privilege</function>(<parameter>user</parameter>,
                                  <parameter>function</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to function</entry>
      </row>
      <row>
       <entry><function>has_function_privilege</function>(<parameter>function</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to function</entry>
      </row>
      <row>
       <entry><function>has_language_privilege</function>(<parameter>user</parameter>,
                                  <parameter>language</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to language</entry>
      </row>
      <row>
       <entry><function>has_language_privilege</function>(<parameter>language</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to language</entry>
      </row>
      <row>
       <entry><function>has_schema_privilege</function>(<parameter>user</parameter>,
                                  <parameter>schema</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does user have access to schema</entry>
      </row>
      <row>
       <entry><function>has_schema_privilege</function>(<parameter>schema</parameter>,
                                  <parameter>access</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>does current user have access to schema</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>has_table_privilege</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>has_database_privilege</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>has_function_privilege</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>has_language_privilege</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>has_schema_privilege</primary>
   </indexterm>

   <para>
    <function>has_table_privilege</function> checks whether a user
    can access a table in a particular way.  The user can be
    specified by name or by ID
    (<classname>pg_user</classname>.<structfield>usesysid</structfield>), or if the argument is
    omitted
    <function>current_user</function> is assumed.  The table can be specified
    by name or by OID.  (Thus, there are actually six variants of
    <function>has_table_privilege</function>, which can be distinguished by
    the number and types of their arguments.)  When specifying by name,
    the name can be schema-qualified if necessary.
    The desired access type
    is specified by a text string, which must evaluate to one of the
    values <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>,
    <literal>DELETE</literal>, <literal>RULE</literal>, <literal>REFERENCES</literal>, or
    <literal>TRIGGER</literal>.  (Case of the string is not significant, however.)
    An example is:
<programlisting>
SELECT has_table_privilege('myschema.mytable', 'select');
</programlisting>
   </para>

   <para>
    <function>has_database_privilege</function> checks whether a user
    can access a database in a particular way.  The possibilities for its
    arguments are analogous to <function>has_table_privilege</function>.
    The desired access type must evaluate to
    <literal>CREATE</literal>,
    <literal>TEMPORARY</literal>, or
    <literal>TEMP</literal> (which is equivalent to
    <literal>TEMPORARY</literal>).
   </para>

   <para>
    <function>has_function_privilege</function> checks whether a user
    can access a function in a particular way.  The possibilities for its
    arguments are analogous to <function>has_table_privilege</function>.
    When specifying a function by a text string rather than by OID,
    the allowed input is the same as for the <type>regprocedure</> data type.
    The desired access type must currently evaluate to
    <literal>EXECUTE</literal>.
   </para>

   <para>
    <function>has_language_privilege</function> checks whether a user
    can access a procedural language in a particular way.  The possibilities
    for its arguments are analogous to <function>has_table_privilege</function>.
    The desired access type must currently evaluate to
    <literal>USAGE</literal>.
   </para>

   <para>
    <function>has_schema_privilege</function> checks whether a user
    can access a schema in a particular way.  The possibilities for its
    arguments are analogous to <function>has_table_privilege</function>.
    The desired access type must evaluate to
    <literal>CREATE</literal> or
    <literal>USAGE</literal>.
   </para>

  <para>
   <xref linkend="functions-misc-schema-table"> shows functions that
   determine whether a certain object is <firstterm>visible</> in the
   current schema search path.  A table is said to be visible if its
   containing schema is in the search path and no table of the same
   name appears earlier in the search path.  This is equivalent to the
   statement that the table can be referenced by name without explicit
   schema qualification.  For example, to list the names of all
   visible tables:
<programlisting>
SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
</programlisting>
  </para>

   <table id="functions-misc-schema-table">
    <title>Schema Visibility Inquiry Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
       <entry><function>pg_table_is_visible</function>(<parameter>tableOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is table visible in search path</entry>
      </row>
      <row>
       <entry><function>pg_type_is_visible</function>(<parameter>typeOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is type visible in search path</entry>
      </row>
      <row>
       <entry><function>pg_function_is_visible</function>(<parameter>functionOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is function visible in search path</entry>
      </row>
      <row>
       <entry><function>pg_operator_is_visible</function>(<parameter>operatorOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is operator visible in search path</entry>
      </row>
      <row>
       <entry><function>pg_opclass_is_visible</function>(<parameter>opclassOID</parameter>)
       </entry>
       <entry><type>boolean</type></entry>
       <entry>is operator class visible in search path</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>pg_table_is_visible</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>pg_type_is_visible</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>pg_function_is_visible</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>pg_operator_is_visible</primary>
   </indexterm>
   <indexterm zone="functions-misc">
    <primary>pg_opclass_is_visible</primary>
   </indexterm>

   <para>
   <function>pg_table_is_visible</function> performs the check for
   tables (or views, or any other kind of <literal>pg_class</> entry).
   <function>pg_type_is_visible</function>,
   <function>pg_function_is_visible</function>,
   <function>pg_operator_is_visible</function>, and
   <function>pg_opclass_is_visible</function> perform the same sort of
   visibility check for types, functions, operators, and operator
   classes, respectively.  For functions and operators, an object in
   the search path is visible if there is no object of the same name
   <emphasis>and argument data type(s)</> earlier in the path.  For
   operator classes, both name and associated index access method are
   considered.
   </para>

   <para>
    All these functions require object OIDs to identify the object to be
    checked.  If you want to test an object by name, it is convenient to use
    the OID alias types (<type>regclass</>, <type>regtype</>,
    <type>regprocedure</>, or <type>regoperator</>), for example
<programlisting>
SELECT pg_type_is_visible('myschema.widget'::regtype);
</programlisting>
    Note that it would not make much sense to test an unqualified name in
    this way --- if the name can be recognized at all, it must be visible.
   </para>

   <indexterm zone="functions-misc">
    <primary>pg_get_viewdef</primary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>pg_get_ruledef</primary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>pg_get_indexdef</primary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>pg_get_constraintdef</primary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>pg_get_userbyid</primary>
   </indexterm>

  <para>
   <xref linkend="functions-misc-catalog-table"> lists functions that
   extract information from the system catalogs.
   <function>pg_get_viewdef()</function>,
   <function>pg_get_ruledef()</function>,
   <function>pg_get_indexdef()</function>, and
   <function>pg_get_constraintdef()</function> respectively
   reconstruct the creating command for a view, rule, index, or
   constraint.  (Note that this is a decompiled reconstruction, not
   the verbatim text of the command.)  At present
   <function>pg_get_constraintdef()</function> only works for
   foreign-key constraints.  <function>pg_get_userbyid()</function>
   extracts a user's name given a <structfield>usesysid</structfield>
   value.
  </para>

   <table id="functions-misc-catalog-table">
    <title>Catalog Information Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
       <entry><function>pg_get_viewdef</function>(<parameter>viewname</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get <command>CREATE VIEW</> command for view (<emphasis>deprecated</emphasis>)</entry>
      </row>
      <row>
       <entry><function>pg_get_viewdef</function>(<parameter>viewOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get <command>CREATE VIEW</> command for view</entry>
      </row>
      <row>
       <entry><function>pg_get_ruledef</function>(<parameter>ruleOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get <command>CREATE RULE</> command for rule</entry>
      </row>
      <row>
       <entry><function>pg_get_indexdef</function>(<parameter>indexOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get <command>CREATE INDEX</> command for index</entry>
      </row>
      <row>
       <entry><function>pg_get_constraintdef</function>(<parameter>constraintOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get definition of a constraint</entry>
      </row>
      <row>
       <entry><function>pg_get_userbyid</function>(<parameter>userid</parameter>)</entry>
       <entry><type>name</type></entry>
       <entry>Get user name with given ID</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <indexterm zone="functions-misc">
    <primary>obj_description</primary>
   </indexterm>

   <indexterm zone="functions-misc">
    <primary>col_description</primary>
   </indexterm>

   <para>
    The function shown in <xref
    linkend="functions-misc-comment-table"> extract comments
    previously stored with the <command>COMMENT</command> command.  A
    null value is returned if no comment can be found matching the
    specified parameters.
   </para>

   <table id="functions-misc-comment-table">
    <title>Comment Information Functions</title>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry></row>
     </thead>

     <tbody>
      <row>
       <entry><function>obj_description</function>(<parameter>objectOID</parameter>, <parameter>tablename</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get comment for a database object</entry>
      </row>
      <row>
       <entry><function>obj_description</function>(<parameter>objectOID</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get comment for a database object (<emphasis>deprecated</emphasis>)</entry>
      </row>
      <row>
       <entry><function>col_description</function>(<parameter>tableOID</parameter>, <parameter>columnnumber</parameter>)</entry>
       <entry><type>text</type></entry>
       <entry>Get comment for a table column</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    The two-parameter form of <function>obj_description()</function> returns the
    comment for a database object specified by its OID and the name of the
    containing system catalog.  For example,
    <literal>obj_description(123456,'pg_class')</literal>
    would retrieve the comment for a table with OID 123456.
    The one-parameter form of <function>obj_description()</function> requires only
    the object OID.  It is now deprecated since there is no guarantee that
    OIDs are unique across different system catalogs; therefore, the wrong
    comment could be returned.
   </para>

   <para>
    <function>col_description()</function> returns the comment for a table column,
    which is specified by the OID of its table and its column number.
    <function>obj_description()</function> cannot be used for table columns since
    columns do not have OIDs of their own.
   </para>

  </sect1>


 <sect1 id="functions-aggregate">
  <title>Aggregate Functions</title>

  <para>
   <firstterm>Aggregate functions</firstterm> compute a single result
   value from a set of input values.  <xref
   linkend="functions-aggregate-table"> show the built-in aggregate
   functions.  The special syntax considerations for aggregate
   functions are explained in <xref linkend="syntax-aggregates">.
   Consult the &cite-tutorial; for additional introductory
   information.
  </para>

  <table id="functions-aggregate-table">
   <title>Aggregate Functions</title>

   <tgroup cols="5">
    <thead>
     <row>
      <entry>Function</entry>
      <entry>Argument Type</entry>
      <entry>Return Type</entry>
      <entry>Description</entry>
     </row>
    </thead>

    <tbody>
     <row>
      <entry>
       <indexterm>
        <primary>average</primary>
        <secondary>function</secondary>
       </indexterm>
       <function>avg(<replaceable class="parameter">expression</replaceable>)</function>
      </entry>
      <entry>
       <type>smallint</type>, <type>integer</type>,
       <type>bigint</type>, <type>real</type>, <type>double
       precision</type>, <type>numeric</type>, or <type>interval</type>.
      </entry>
      <entry>
       <type>numeric</type> for any integer type argument,
       <type>double precision</type> for a floating-point argument,
       otherwise the same as the argument data type
      </entry>
      <entry>the average (arithmetic mean) of all input values</entry>
     </row>

     <row>
      <entry><function>count(*)</function></entry>
      <entry></entry>
      <entry><type>bigint</type></entry>
      <entry>number of input values</entry>
     </row>

     <row>
      <entry><function>count(<replaceable class="parameter">expression</replaceable>)</function></entry>
      <entry>any</entry>
      <entry><type>bigint</type></entry>
      <entry>
       number of input values for which the value of <replaceable
       class="parameter">expression</replaceable> is not null
      </entry>
     </row>

     <row>
      <entry><function>max(<replaceable class="parameter">expression</replaceable>)</function></entry>
      <entry>any numeric, string, or date/time type</entry>
      <entry>same as argument type</entry>
      <entry>
       maximum value of <replaceable
       class="parameter">expression</replaceable> across all input
       values
      </entry>
     </row>

     <row>
      <entry><function>min(<replaceable class="parameter">expression</replaceable>)</function></entry>
      <entry>any numeric, string, or date/time type</entry>
      <entry>same as argument type</entry>
      <entry>
       minimum value of <replaceable
       class="parameter">expression</replaceable> across all input
       values
      </entry>
     </row>

     <row>
      <entry>
       <indexterm>
        <primary>standard deviation</primary>
       </indexterm>
       <function>stddev(<replaceable class="parameter">expression</replaceable>)</function>
      </entry>
      <entry>
       <type>smallint</type>, <type>integer</type>,
       <type>bigint</type>, <type>real</type>, <type>double
       precision</type>, or <type>numeric</type>.
      </entry>
      <entry>
       <type>double precision</type> for floating-point arguments,
       otherwise <type>numeric</type>.
      </entry>
      <entry>sample standard deviation of the input values</entry>
     </row>

     <row>
      <entry><function>sum(<replaceable class="parameter">expression</replaceable>)</function></entry>
      <entry>
       <type>smallint</type>, <type>integer</type>,
       <type>bigint</type>, <type>real</type>, <type>double
       precision</type>, <type>numeric</type>, or
       <type>interval</type>
      </entry>
      <entry>
       <type>bigint</type> for <type>smallint</type> or
       <type>integer</type> arguments, <type>numeric</type> for
       <type>bigint</type> arguments, <type>double precision</type>
       for floating-point arguments, otherwise the same as the
       argument data type
      </entry>
      <entry>sum of <replaceable class="parameter">expression</replaceable> across all input values</entry>
     </row>

     <row>
      <entry>
       <indexterm>
        <primary>variance</primary>
       </indexterm>
       <function>variance</function>(<replaceable class="parameter">expression</replaceable>)
      </entry>
      <entry>
       <type>smallint</type>, <type>integer</type>,
       <type>bigint</type>, <type>real</type>, <type>double
       precision</type>, or <type>numeric</type>.
      </entry>
      <entry>
       <type>double precision</type> for floating-point arguments,
       otherwise <type>numeric</type>.
      </entry>
      <entry>sample variance of the input values (square of the sample standard deviation)</entry>
     </row>

    </tbody>
   </tgroup>
  </table>

  <para>
   It should be noted that except for <function>count</function>,
   these functions return a null value when no rows are selected.  In
   particular, <function>sum</function> of no rows returns null, not
   zero as one might expect.  The function <function>coalesce</function> may be
   used to substitute zero for null when necessary.
  </para>

 </sect1>


 <sect1 id="functions-subquery">
  <title>Subquery Expressions</title>

  <indexterm>
   <primary>exists</primary>
  </indexterm>

  <indexterm>
   <primary>in</primary>
  </indexterm>

  <indexterm>
   <primary>not in</primary>
  </indexterm>

  <indexterm>
   <primary>any</primary>
  </indexterm>

  <indexterm>
   <primary>all</primary>
  </indexterm>

  <indexterm>
   <primary>some</primary>
  </indexterm>

  <indexterm>
   <primary>subqueries</primary>
  </indexterm>

  <para>
   This section describes the <acronym>SQL</acronym>-compliant subquery
   expressions available in <productname>PostgreSQL</productname>.
   All of the expression forms documented in this section return
   Boolean (true/false) results.
  </para>

  <sect2>
   <title>EXISTS</title>

<synopsis>
EXISTS ( <replaceable>subquery</replaceable> )
</synopsis>

  <para>
   The argument of <token>EXISTS</token> is an arbitrary <command>SELECT</> statement,
   or <firstterm>subquery</firstterm>.  The
   subquery is evaluated to determine whether it returns any rows.
   If it returns at least one row, the result of <token>EXISTS</token> is
   <quote>true</>; if the subquery returns no rows, the result of <token>EXISTS</token> 
   is <quote>false</>.
  </para>

  <para>
   The subquery can refer to variables from the surrounding query,
   which will act as constants during any one evaluation of the subquery.
  </para>

  <para>
   The subquery will generally only be executed far enough to determine
   whether at least one row is returned, not all the way to completion.
   It is unwise to write a subquery that has any side effects (such as
   calling sequence functions); whether the side effects occur or not
   may be difficult to predict.
  </para>

  <para>
   Since the result depends only on whether any rows are returned,
   and not on the contents of those rows, the output list of the
   subquery is normally uninteresting.  A common coding convention is
   to write all <literal>EXISTS</> tests in the form
   <literal>EXISTS(SELECT 1 WHERE ...)</literal>.  There are exceptions to
   this rule however, such as subqueries that use <token>INTERSECT</token>.
  </para>

  <para>
   This simple example is like an inner join on <literal>col2</>, but
   it produces at most one output row for each <literal>tab1</> row,
   even if there are multiple matching <literal>tab2</> rows:
<screen>
SELECT col1 FROM tab1
    WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);
</screen>
  </para>
  </sect2>

  <sect2>
   <title>IN (scalar form)</title>

<synopsis>
<replaceable>expression</replaceable> IN (<replaceable>value</replaceable><optional>, ...</optional>)
</synopsis>

  <para>
   The right-hand side of this form of <token>IN</token> is a parenthesized list
   of scalar expressions.  The result is <quote>true</> if the left-hand expression's
   result is equal to any of the right-hand expressions.  This is a shorthand
   notation for

<synopsis>
<replaceable>expression</replaceable> = <replaceable>value1</replaceable>
OR
<replaceable>expression</replaceable> = <replaceable>value2</replaceable>
OR
...
</synopsis>

   Note that if the left-hand expression yields null, or if there are
   no equal right-hand values and at least one right-hand expression yields
   null, the result of the <token>IN</token> construct will be null, not false.
   This is in accordance with SQL's normal rules for Boolean combinations
   of null values.
  </para>

  <note>
  <para>
   This form of <token>IN</token> is not truly a subquery expression, but it
   seems best to document it in the same place as subquery <token>IN</token>.
  </para>
  </note>
  </sect2>

  <sect2>
   <title>IN (subquery form)</title>

<synopsis>
<replaceable>expression</replaceable> IN (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
   The right-hand side of this form of <token>IN</token> is a parenthesized
   subquery, which must return exactly one column.  The left-hand expression
   is evaluated and compared to each row of the subquery result.
   The result of <token>IN</token> is <quote>true</> if any equal subquery row is found.
   The result is <quote>false</> if no equal row is found (including the special
   case where the subquery returns no rows).
  </para>

  <para>
   Note that if the left-hand expression yields null, or if there are
   no equal right-hand values and at least one right-hand row yields
   null, the result of the <token>IN</token> construct will be null, not false.
   This is in accordance with SQL's normal rules for Boolean combinations
   of null values.
  </para>

  <para>
   As with <token>EXISTS</token>, it's unwise to assume that the subquery will
   be evaluated completely.
  </para>

<synopsis>
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) IN (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
   The right-hand side of this form of <token>IN</token> is a parenthesized
   subquery, which must return exactly as many columns as there are
   expressions in the left-hand list.  The left-hand expressions are
   evaluated and compared row-wise to each row of the subquery result.
   The result of <token>IN</token> is <quote>true</> if any equal subquery row is found.
   The result is <quote>false</> if no equal row is found (including the special
   case where the subquery returns no rows).
  </para>

  <para>
   As usual, null values in the expressions or subquery rows are combined per
   the normal rules of SQL Boolean expressions.  Two rows are considered
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of that row comparison is unknown (null).
   If all the row results are either unequal or null, with at least one null,
   then the result of <token>IN</token> is null.
  </para>
  </sect2>

  <sect2>
   <title>NOT IN (scalar form)</title>

<synopsis>
<replaceable>expression</replaceable> NOT IN (<replaceable>value</replaceable><optional>, ...</optional>)
</synopsis>

  <para>
   The right-hand side of this form of <token>NOT IN</token> is a parenthesized list
   of scalar expressions.  The result is <quote>true</quote> if the left-hand expression's
   result is unequal to all of the right-hand expressions.  This is a shorthand
   notation for

<synopsis>
<replaceable>expression</replaceable> &lt;&gt; <replaceable>value1</replaceable>
AND
<replaceable>expression</replaceable> &lt;&gt; <replaceable>value2</replaceable>
AND
...
</synopsis>

   Note that if the left-hand expression yields null, or if there are
   no equal right-hand values and at least one right-hand expression yields
   null, the result of the <token>NOT IN</token> construct will be null, not true
   as one might naively expect.
   This is in accordance with SQL's normal rules for Boolean combinations
   of null values.
  </para>

  <tip>
  <para>
   <literal>x NOT IN y</literal> is equivalent to <literal>NOT (x IN y)</literal> in all
   cases.  However, null values are much more likely to trip up the novice when
   working with <token>NOT IN</token> than when working with <token>IN</token>.
   It's best to express your condition positively if possible.
  </para>
  </tip>
  </sect2>

  <sect2>
   <title>NOT IN (subquery form)</title>

<synopsis>
<replaceable>expression</replaceable> NOT IN (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
   The right-hand side of this form of <token>NOT IN</token> is a parenthesized
   subquery, which must return exactly one column.  The left-hand expression
   is evaluated and compared to each row of the subquery result.
   The result of <token>NOT IN</token> is <quote>true</> if only unequal subquery rows
   are found (including the special case where the subquery returns no rows).
   The result is <quote>false</> if any equal row is found.
  </para>

  <para>
   Note that if the left-hand expression yields null, or if there are
   no equal right-hand values and at least one right-hand row yields
   null, the result of the <token>NOT IN</token> construct will be null, not true.
   This is in accordance with SQL's normal rules for Boolean combinations
   of null values.
  </para>

  <para>
   As with <token>EXISTS</token>, it's unwise to assume that the subquery will
   be evaluated completely.
  </para>

<synopsis>
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) NOT IN (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
   The right-hand side of this form of <token>NOT IN</token> is a parenthesized
   subquery, which must return exactly as many columns as there are
   expressions in the left-hand list.  The left-hand expressions are
   evaluated and compared row-wise to each row of the subquery result.
   The result of <token>NOT IN</token> is <quote>true</> if only unequal subquery rows
   are found (including the special case where the subquery returns no rows).
   The result is <quote>false</> if any equal row is found.
  </para>

  <para>
   As usual, null values in the expressions or subquery rows are combined per
   the normal rules of SQL Boolean expressions.  Two rows are considered
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of that row comparison is unknown (null).
   If all the row results are either unequal or null, with at least one null,
   then the result of <token>NOT IN</token> is null.
  </para>
  </sect2>

  <sect2>
   <title>ANY/SOME</title>

<synopsis>
<replaceable>expression</replaceable> <replaceable>operator</replaceable> ANY (<replaceable>subquery</replaceable>)
<replaceable>expression</replaceable> <replaceable>operator</replaceable> SOME (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
   The right-hand side of this form of <token>ANY</token> is a parenthesized
   subquery, which must return exactly one column.  The left-hand expression
   is evaluated and compared to each row of the subquery result using the
   given <replaceable>operator</replaceable>, which must yield a Boolean
   result.
   The result of <token>ANY</token> is <quote>true</> if any true result is obtained.
   The result is <quote>false</> if no true result is found (including the special
   case where the subquery returns no rows).
  </para>

  <para>
   <token>SOME</token> is a synonym for <token>ANY</token>.
   <token>IN</token> is equivalent to <literal>= ANY</literal>.
  </para>

  <para>
   Note that if there are no successes and at least one right-hand row yields
   null for the operator's result, the result of the <token>ANY</token> construct
   will be null, not false.
   This is in accordance with SQL's normal rules for Boolean combinations
   of null values.
  </para>

  <para>
   As with <token>EXISTS</token>, it's unwise to assume that the subquery will
   be evaluated completely.
  </para>

<synopsis>
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</> ANY (<replaceable>subquery</replaceable>)
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</> SOME (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
   The right-hand side of this form of <token>ANY</token> is a parenthesized
   subquery, which must return exactly as many columns as there are
   expressions in the left-hand list.  The left-hand expressions are
   evaluated and compared row-wise to each row of the subquery result,
   using the given <replaceable>operator</replaceable>.  Presently,
   only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
   in row-wise <token>ANY</token> queries.
   The result of <token>ANY</token> is <quote>true</> if any equal or unequal row is
   found, respectively.
   The result is <quote>false</> if no such row is found (including the special
   case where the subquery returns no rows).
  </para>

  <para>
   As usual, null values in the expressions or subquery rows are combined per
   the normal rules of SQL Boolean expressions.  Two rows are considered
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of that row comparison is unknown (null).
   If there is at least one null row result, then the result of <token>ANY</token>
   cannot be false; it will be true or null. 
  </para>
  </sect2>

  <sect2>
   <title>ALL</title>

<synopsis>
<replaceable>expression</replaceable> <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)
</synopsis>

  <para>
   The right-hand side of this form of <token>ALL</token> is a parenthesized
   subquery, which must return exactly one column.  The left-hand expression
   is evaluated and compared to each row of the subquery result using the
   given <replaceable>operator</replaceable>, which must yield a Boolean
   result.
   The result of <token>ALL</token> is <quote>true</> if all rows yield true
   (including the special case where the subquery returns no rows).
   The result is <quote>false</> if any false result is found.
  </para>

  <para>
   <token>NOT IN</token> is equivalent to <literal>&lt;&gt; ALL</literal>.
  </para>

  <para>
   Note that if there are no failures but at least one right-hand row yields
   null for the operator's result, the result of the <token>ALL</token> construct
   will be null, not true.
   This is in accordance with SQL's normal rules for Boolean combinations
   of null values.
  </para>

  <para>
   As with <token>EXISTS</token>, it's unwise to assume that the subquery will
   be evaluated completely.
  </para>

   <synopsis>
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)
   </synopsis>

  <para>
   The right-hand side of this form of <token>ALL</token> is a parenthesized
   subquery, which must return exactly as many columns as there are
   expressions in the left-hand list.  The left-hand expressions are
   evaluated and compared row-wise to each row of the subquery result,
   using the given <replaceable>operator</replaceable>.  Presently,
   only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
   in row-wise <token>ALL</token> queries.
   The result of <token>ALL</token> is <quote>true</> if all subquery rows are equal
   or unequal, respectively (including the special
   case where the subquery returns no rows).
   The result is <quote>false</> if any row is found to be unequal or equal,
   respectively.
  </para>

  <para>
   As usual, null values in the expressions or subquery rows are combined per
   the normal rules of SQL Boolean expressions.  Two rows are considered
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of that row comparison is unknown (null).
   If there is at least one null row result, then the result of <token>ALL</token>
   cannot be true; it will be false or null. 
  </para>
  </sect2>

  <sect2>
   <title>Row-wise Comparison</title>

   <synopsis>
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>)
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable> (<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>)
   </synopsis>

  <para>
   The left-hand side is a list of scalar expressions.  The right-hand side
   can be either a list of scalar expressions of the same length, or a
   parenthesized subquery, which must return exactly as many columns as there
   are expressions on the left-hand side.  Furthermore, the subquery cannot
   return more than one row.  (If it returns zero rows, the result is taken to
   be null.)  The left-hand side is evaluated and compared row-wise to the
   single subquery result row, or to the right-hand expression list.
   Presently, only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
   in row-wise comparisons.
   The result is <quote>true</> if the two rows are equal or unequal, respectively.
  </para>

  <para>
   As usual, null values in the expressions or subquery rows are combined per
   the normal rules of SQL Boolean expressions.  Two rows are considered
   equal if all their corresponding members are non-null and equal; the rows
   are unequal if any corresponding members are non-null and unequal;
   otherwise the result of the row comparison is unknown (null).
  </para>
  </sect2>
 </sect1>

</chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->