path: root/doc/src/sgml/func.sgml

<!-- doc/src/sgml/func.sgml -->

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

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

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

  <para>
   <productname>PostgreSQL</productname> provides a large number of
   functions and operators for the built-in data types.  This chapter
   describes most of them, although additional special-purpose functions
   appear in relevant sections of the manual.  Users can also
   define their own functions and operators, as described in
   <xref linkend="server-programming"/>.  The
   <application>psql</application> commands <command>\df</command> and
   <command>\do</command> can be used to list all
   available functions and operators, respectively.
  </para>

  <para>
   The notation used throughout this chapter to describe the argument and
   result data types of a function or operator is like this:
<synopsis>
<function>repeat</function> ( <type>text</type>, <type>integer</type> ) <returnvalue>text</returnvalue>
</synopsis>
   which says that the function <function>repeat</function> takes one text and
   one integer argument and returns a result of type text.  The right arrow
   is also used to indicate the result of an example, thus:
<programlisting>
repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
</programlisting>
  </para>

  <para>
   If you are concerned about portability then 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> database management
   systems, and in many cases this functionality is compatible and
   consistent between the various implementations.
  </para>


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

   <indexterm zone="functions-logical">
    <primary>operator</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 (operator)</primary>
    </indexterm>

    <indexterm>
     <primary>OR (operator)</primary>
    </indexterm>

    <indexterm>
     <primary>NOT (operator)</primary>
    </indexterm>

    <indexterm>
     <primary>conjunction</primary>
    </indexterm>

    <indexterm>
     <primary>disjunction</primary>
    </indexterm>

    <indexterm>
     <primary>negation</primary>
    </indexterm>

<synopsis>
<type>boolean</type> <literal>AND</literal> <type>boolean</type> <returnvalue>boolean</returnvalue>
<type>boolean</type> <literal>OR</literal> <type>boolean</type> <returnvalue>boolean</returnvalue>
<literal>NOT</literal> <type>boolean</type> <returnvalue>boolean</returnvalue>
</synopsis>

    <acronym>SQL</acronym> uses a three-valued logic system with true,
    false, and <literal>null</literal>, which 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>

   <para>
    The operators <literal>AND</literal> and <literal>OR</literal> are
    commutative, that is, you can switch the left and right operands
    without affecting the result.  (However, it is not guaranteed that
    the left operand is evaluated before the right operand.  See <xref
    linkend="syntax-express-eval"/> for more information about the
    order of evaluation of subexpressions.)
   </para>
  </sect1>

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

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

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

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

     <tbody>
      <row>
       <entry>
        <replaceable>datatype</replaceable> <literal>&lt;</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </entry>
       <entry>Less than</entry>
      </row>

      <row>
       <entry>
        <replaceable>datatype</replaceable> <literal>&gt;</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </entry>
       <entry>Greater than</entry>
      </row>

      <row>
       <entry>
        <replaceable>datatype</replaceable> <literal>&lt;=</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </entry>
       <entry>Less than or equal to</entry>
      </row>

      <row>
       <entry>
        <replaceable>datatype</replaceable> <literal>&gt;=</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </entry>
       <entry>Greater than or equal to</entry>
      </row>

      <row>
       <entry>
        <replaceable>datatype</replaceable> <literal>=</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </entry>
       <entry>Equal</entry>
      </row>

      <row>
       <entry>
        <replaceable>datatype</replaceable> <literal>&lt;&gt;</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </entry>
       <entry>Not equal</entry>
      </row>

      <row>
       <entry>
        <replaceable>datatype</replaceable> <literal>!=</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </entry>
       <entry>Not equal</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <note>
    <para>
     <literal>&lt;&gt;</literal> is the standard SQL notation for <quote>not
     equal</quote>.  <literal>!=</literal> is an alias, which is converted
     to <literal>&lt;&gt;</literal> at a very early stage of parsing.
     Hence, it is not possible to implement <literal>!=</literal>
     and <literal>&lt;&gt;</literal> operators that do different things.
    </para>
   </note>

   <para>
    These comparison operators are available for all built-in data types
    that have a natural ordering, including numeric, string, and date/time
    types.  In addition, arrays, composite types, and ranges can be compared
    if their component data types are comparable.
   </para>

   <para>
    It is usually possible to compare values of related data
    types as well; for example <type>integer</type> <literal>&gt;</literal>
    <type>bigint</type> will work.  Some cases of this sort are implemented
    directly by <quote>cross-type</quote> comparison operators, but if no
    such operator is available, the parser will coerce the less-general type
    to the more-general type and apply the latter's comparison operator.
   </para>

   <para>
    As shown above, all comparison operators are binary operators that
    return values of type <type>boolean</type>.  Thus, 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>).  Use the <literal>BETWEEN</literal> predicates
    shown below to perform range tests.
   </para>

   <para>
    There are also some comparison predicates, as shown in <xref
    linkend="functions-comparison-pred-table"/>.  These behave much like
    operators, but have special syntax mandated by the SQL standard.
   </para>

   <table id="functions-comparison-pred-table">
    <title>Comparison Predicates</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Predicate
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>BETWEEN</literal> <replaceable>datatype</replaceable> <literal>AND</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Between (inclusive of the range endpoints).
       </para>
       <para>
        <literal>2 BETWEEN 1 AND 3</literal>
        <returnvalue>t</returnvalue>
       </para>
       <para>
        <literal>2 BETWEEN 3 AND 1</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>NOT BETWEEN</literal> <replaceable>datatype</replaceable> <literal>AND</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Not between (the negation of <literal>BETWEEN</literal>).
       </para>
       <para>
        <literal>2 NOT BETWEEN 1 AND 3</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>BETWEEN SYMMETRIC</literal> <replaceable>datatype</replaceable> <literal>AND</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Between, after sorting the two endpoint values.
       </para>
       <para>
        <literal>2 BETWEEN SYMMETRIC 3 AND 1</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>NOT BETWEEN SYMMETRIC</literal> <replaceable>datatype</replaceable> <literal>AND</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Not between, after sorting the two endpoint values.
       </para>
       <para>
        <literal>2 NOT BETWEEN SYMMETRIC 3 AND 1</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>IS DISTINCT FROM</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Not equal, treating null as a comparable value.
       </para>
       <para>
        <literal>1 IS DISTINCT FROM NULL</literal>
        <returnvalue>t</returnvalue> (rather than <literal>NULL</literal>)
       </para>
       <para>
        <literal>NULL IS DISTINCT FROM NULL</literal>
        <returnvalue>f</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>IS NOT DISTINCT FROM</literal> <replaceable>datatype</replaceable>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Equal, treating null as a comparable value.
       </para>
       <para>
        <literal>1 IS NOT DISTINCT FROM NULL</literal>
        <returnvalue>f</returnvalue> (rather than <literal>NULL</literal>)
       </para>
       <para>
        <literal>NULL IS NOT DISTINCT FROM NULL</literal>
        <returnvalue>t</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>IS NULL</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether value is null.
       </para>
       <para>
        <literal>1.5 IS NULL</literal>
        <returnvalue>f</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>IS NOT NULL</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether value is not null.
       </para>
       <para>
        <literal>'null' IS NOT NULL</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>ISNULL</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether value is null (nonstandard syntax).
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>datatype</replaceable> <literal>NOTNULL</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether value is not null (nonstandard syntax).
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>boolean</type> <literal>IS TRUE</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether boolean expression yields true.
       </para>
       <para>
        <literal>true IS TRUE</literal>
        <returnvalue>t</returnvalue>
       </para>
       <para>
        <literal>NULL::boolean IS TRUE</literal>
        <returnvalue>f</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>boolean</type> <literal>IS NOT TRUE</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether boolean expression yields false or unknown.
       </para>
       <para>
        <literal>true IS NOT TRUE</literal>
        <returnvalue>f</returnvalue>
       </para>
       <para>
        <literal>NULL::boolean IS NOT TRUE</literal>
        <returnvalue>t</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>boolean</type> <literal>IS FALSE</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether boolean expression yields false.
       </para>
       <para>
        <literal>true IS FALSE</literal>
        <returnvalue>f</returnvalue>
       </para>
       <para>
        <literal>NULL::boolean IS FALSE</literal>
        <returnvalue>f</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>boolean</type> <literal>IS NOT FALSE</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether boolean expression yields true or unknown.
       </para>
       <para>
        <literal>true IS NOT FALSE</literal>
        <returnvalue>t</returnvalue>
       </para>
       <para>
        <literal>NULL::boolean IS NOT FALSE</literal>
        <returnvalue>t</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>boolean</type> <literal>IS UNKNOWN</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether boolean expression yields unknown.
       </para>
       <para>
        <literal>true IS UNKNOWN</literal>
        <returnvalue>f</returnvalue>
       </para>
       <para>
        <literal>NULL::boolean IS UNKNOWN</literal>
        <returnvalue>t</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>boolean</type> <literal>IS NOT UNKNOWN</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Test whether boolean expression yields true or false.
       </para>
       <para>
        <literal>true IS NOT UNKNOWN</literal>
        <returnvalue>t</returnvalue>
       </para>
       <para>
        <literal>NULL::boolean IS NOT UNKNOWN</literal>
        <returnvalue>f</returnvalue> (rather than <literal>NULL</literal>)
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    <indexterm>
     <primary>BETWEEN</primary>
    </indexterm>
    <indexterm>
     <primary>BETWEEN SYMMETRIC</primary>
    </indexterm>
    The <token>BETWEEN</token> predicate simplifies range tests:
<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>
    Notice that <token>BETWEEN</token> treats the endpoint values as included
    in the range.
    <literal>BETWEEN SYMMETRIC</literal> is like <literal>BETWEEN</literal>
    except there is no requirement that the argument to the left of
    <literal>AND</literal> be less than or equal to the argument on the right.
    If it is not, those two arguments are automatically swapped, so that
    a nonempty range is always implied.
   </para>

   <para>
    The various variants of <literal>BETWEEN</literal> are implemented in
    terms of the ordinary comparison operators, and therefore will work for
    any data type(s) that can be compared.
   </para>

   <note>
    <para>
     The use of <literal>AND</literal> in the <literal>BETWEEN</literal>
     syntax creates an ambiguity with the use of <literal>AND</literal> as a
     logical operator.  To resolve this, only a limited set of expression
     types are allowed as the second argument of a <literal>BETWEEN</literal>
     clause.  If you need to write a more complex sub-expression
     in <literal>BETWEEN</literal>, write parentheses around the
     sub-expression.
    </para>
   </note>

   <para>
    <indexterm>
     <primary>IS DISTINCT FROM</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT DISTINCT FROM</primary>
    </indexterm>
    Ordinary comparison operators yield null (signifying <quote>unknown</quote>),
    not true or false, when either input is null.  For example,
    <literal>7 = NULL</literal> yields null, as does <literal>7 &lt;&gt; NULL</literal>.  When
    this behavior is not suitable, use the
    <literal>IS <optional> NOT </optional> DISTINCT FROM</literal> predicates:
<synopsis>
<replaceable>a</replaceable> IS DISTINCT FROM <replaceable>b</replaceable>
<replaceable>a</replaceable> IS NOT DISTINCT FROM <replaceable>b</replaceable>
</synopsis>
    For non-null inputs, <literal>IS DISTINCT FROM</literal> is
    the same as the <literal>&lt;&gt;</literal> operator.  However, if both
    inputs are null it returns false, and if only one input is
    null it returns true.  Similarly, <literal>IS NOT DISTINCT
    FROM</literal> is identical to <literal>=</literal> for non-null
    inputs, but it returns true when both inputs are null, and false when only
    one input is null. Thus, these predicates effectively act as though null
    were a normal data value, rather than <quote>unknown</quote>.
   </para>

   <para>
    <indexterm>
     <primary>IS NULL</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT NULL</primary>
    </indexterm>
    <indexterm>
     <primary>ISNULL</primary>
    </indexterm>
    <indexterm>
     <primary>NOTNULL</primary>
    </indexterm>
    To check whether a value is or is not null, use the predicates:
<synopsis>
<replaceable>expression</replaceable> IS NULL
<replaceable>expression</replaceable> IS NOT NULL
</synopsis>
    or the equivalent, but nonstandard, predicates:
<synopsis>
<replaceable>expression</replaceable> ISNULL
<replaceable>expression</replaceable> NOTNULL
</synopsis>
    <indexterm><primary>null value</primary><secondary>comparing</secondary></indexterm>
   </para>

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

  <tip>
   <para>
    Some applications might expect that
    <literal><replaceable>expression</replaceable> = NULL</literal>
    returns true if <replaceable>expression</replaceable> evaluates to
    the null value.  It is highly recommended that these applications
    be modified to comply with the SQL standard. However, if that
    cannot be done the <xref linkend="guc-transform-null-equals"/>
    configuration variable is available. If it is enabled,
    <productname>PostgreSQL</productname> will convert <literal>x =
    NULL</literal> clauses to <literal>x IS NULL</literal>.
   </para>
  </tip>

   <para>
    If the <replaceable>expression</replaceable> is row-valued, then
    <literal>IS NULL</literal> is true when the row expression itself is null
    or when all the row's fields are null, while
    <literal>IS NOT NULL</literal> is true when the row expression itself is non-null
    and all the row's fields are non-null.  Because of this behavior,
    <literal>IS NULL</literal> and <literal>IS NOT NULL</literal> do not always return
    inverse results for row-valued expressions; in particular, a row-valued
    expression that contains both null and non-null fields will return false
    for both tests.  In some cases, it may be preferable to
    write <replaceable>row</replaceable> <literal>IS DISTINCT FROM NULL</literal>
    or <replaceable>row</replaceable> <literal>IS NOT DISTINCT FROM NULL</literal>,
    which will simply check whether the overall row value is null without any
    additional tests on the row fields.
   </para>

   <para>
    <indexterm>
     <primary>IS TRUE</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT TRUE</primary>
    </indexterm>
    <indexterm>
     <primary>IS FALSE</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT FALSE</primary>
    </indexterm>
    <indexterm>
     <primary>IS UNKNOWN</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT UNKNOWN</primary>
    </indexterm>
    Boolean values can also be tested using the predicates
<synopsis>
<replaceable>boolean_expression</replaceable> IS TRUE
<replaceable>boolean_expression</replaceable> IS NOT TRUE
<replaceable>boolean_expression</replaceable> IS FALSE
<replaceable>boolean_expression</replaceable> IS NOT FALSE
<replaceable>boolean_expression</replaceable> IS UNKNOWN
<replaceable>boolean_expression</replaceable> IS NOT UNKNOWN
</synopsis>
    These 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</quote>.
    Notice that <literal>IS UNKNOWN</literal> and <literal>IS NOT UNKNOWN</literal> are
    effectively the same as <literal>IS NULL</literal> and
    <literal>IS NOT NULL</literal>, respectively, except that the input
    expression must be of Boolean type.
   </para>

   <para>
    Some comparison-related functions are also available, as shown in <xref
    linkend="functions-comparison-func-table"/>.
   </para>

  <table id="functions-comparison-func-table">
    <title>Comparison Functions</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>num_nonnulls</primary>
        </indexterm>
        <function>num_nonnulls</function> ( <literal>VARIADIC</literal> <type>"any"</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the number of non-null arguments.
       </para>
       <para>
        <literal>num_nonnulls(1, NULL, 2)</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>num_nulls</primary>
        </indexterm>
        <function>num_nulls</function> ( <literal>VARIADIC</literal> <type>"any"</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the number of null arguments.
       </para>
       <para>
        <literal>num_nulls(1, NULL, 2)</literal>
        <returnvalue>1</returnvalue>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  </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
    standard mathematical conventions
    (e.g., date/time types) we
    describe the actual behavior in subsequent sections.
   </para>

   <para>
    <xref linkend="functions-math-op-table"/> shows the mathematical
    operators that are available for the standard numeric types.
    Unless otherwise noted, operators shown as
    accepting <replaceable>numeric_type</replaceable> are available for all
    the types <type>smallint</type>, <type>integer</type>,
    <type>bigint</type>, <type>numeric</type>, <type>real</type>,
    and <type>double precision</type>.
    Operators shown as accepting <replaceable>integral_type</replaceable>
    are available for the types <type>smallint</type>, <type>integer</type>,
    and <type>bigint</type>.
    Except where noted, each form of an operator returns the same data type
    as its argument(s).  Calls involving multiple argument data types, such
    as <type>integer</type> <literal>+</literal> <type>numeric</type>,
    are resolved by using the type appearing later in these lists.
   </para>

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

    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Operator
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>numeric_type</replaceable> <literal>+</literal> <replaceable>numeric_type</replaceable>
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Addition
       </para>
       <para>
        <literal>2 + 3</literal>
        <returnvalue>5</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <literal>+</literal> <replaceable>numeric_type</replaceable>
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Unary plus (no operation)
       </para>
       <para>
        <literal>+ 3.5</literal>
        <returnvalue>3.5</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>numeric_type</replaceable> <literal>-</literal> <replaceable>numeric_type</replaceable>
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Subtraction
       </para>
       <para>
        <literal>2 - 3</literal>
        <returnvalue>-1</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <literal>-</literal> <replaceable>numeric_type</replaceable>
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Negation
       </para>
       <para>
        <literal>- (-4)</literal>
        <returnvalue>4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>numeric_type</replaceable> <literal>*</literal> <replaceable>numeric_type</replaceable>
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Multiplication
       </para>
       <para>
        <literal>2 * 3</literal>
        <returnvalue>6</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>numeric_type</replaceable> <literal>/</literal> <replaceable>numeric_type</replaceable>
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Division (for integral types, division truncates the result towards
        zero)
       </para>
       <para>
        <literal>5.0 / 2</literal>
        <returnvalue>2.5000000000000000</returnvalue>
       </para>
       <para>
        <literal>5 / 2</literal>
        <returnvalue>2</returnvalue>
       </para>
       <para>
        <literal>(-5) / 2</literal>
        <returnvalue>-2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>numeric_type</replaceable> <literal>%</literal> <replaceable>numeric_type</replaceable>
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Modulo (remainder); available for <type>smallint</type>,
        <type>integer</type>, <type>bigint</type>, and <type>numeric</type>
       </para>
       <para>
        <literal>5 % 4</literal>
        <returnvalue>1</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>numeric</type> <literal>^</literal> <type>numeric</type>
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <type>double precision</type> <literal>^</literal> <type>double precision</type>
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Exponentiation
       </para>
       <para>
        <literal>2 ^ 3</literal>
        <returnvalue>8</returnvalue>
       </para>
       <para>
        Unlike typical mathematical practice, multiple uses of
        <literal>^</literal> will associate left to right by default:
       </para>
       <para>
        <literal>2 ^ 3 ^ 3</literal>
        <returnvalue>512</returnvalue>
       </para>
       <para>
        <literal>2 ^ (3 ^ 3)</literal>
        <returnvalue>134217728</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <literal>|/</literal> <type>double precision</type>
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Square root
       </para>
       <para>
        <literal>|/ 25.0</literal>
        <returnvalue>5</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <literal>||/</literal> <type>double precision</type>
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Cube root
       </para>
       <para>
        <literal>||/ 64.0</literal>
        <returnvalue>4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <literal>@</literal> <replaceable>numeric_type</replaceable>
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Absolute value
       </para>
       <para>
        <literal>@ -5.0</literal>
        <returnvalue>5.0</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>integral_type</replaceable> <literal>&amp;</literal> <replaceable>integral_type</replaceable>
        <returnvalue><replaceable>integral_type</replaceable></returnvalue>
       </para>
       <para>
        Bitwise AND
       </para>
       <para>
        <literal>91 &amp; 15</literal>
        <returnvalue>11</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>integral_type</replaceable> <literal>|</literal> <replaceable>integral_type</replaceable>
        <returnvalue><replaceable>integral_type</replaceable></returnvalue>
       </para>
       <para>
        Bitwise OR
       </para>
       <para>
        <literal>32 | 3</literal>
        <returnvalue>35</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>integral_type</replaceable> <literal>#</literal> <replaceable>integral_type</replaceable>
        <returnvalue><replaceable>integral_type</replaceable></returnvalue>
       </para>
       <para>
        Bitwise exclusive OR
       </para>
       <para>
        <literal>17 # 5</literal>
        <returnvalue>20</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <literal>~</literal> <replaceable>integral_type</replaceable>
        <returnvalue><replaceable>integral_type</replaceable></returnvalue>
       </para>
       <para>
        Bitwise NOT
       </para>
       <para>
        <literal>~1</literal>
        <returnvalue>-2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>integral_type</replaceable> <literal>&lt;&lt;</literal> <type>integer</type>
        <returnvalue><replaceable>integral_type</replaceable></returnvalue>
       </para>
       <para>
        Bitwise shift left
       </para>
       <para>
        <literal>1 &lt;&lt; 4</literal>
        <returnvalue>16</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <replaceable>integral_type</replaceable> <literal>&gt;&gt;</literal> <type>integer</type>
        <returnvalue><replaceable>integral_type</replaceable></returnvalue>
       </para>
       <para>
        Bitwise shift right
       </para>
       <para>
        <literal>8 &gt;&gt; 2</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>

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

  <para>
   <xref linkend="functions-math-func-table"/> shows the available
   mathematical functions.
   Many of these functions are provided in multiple forms with different
   argument types.
   Except where noted, any given form of a function returns the same
   data type as its argument(s); cross-type cases are resolved in the
   same way as explained above for operators.
   The functions working with <type>double precision</type> data are mostly
   implemented on top of the host system's C library; accuracy and behavior in
   boundary cases can therefore vary depending on the host system.
  </para>

   <table id="functions-math-func-table">
    <title>Mathematical Functions</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>abs</primary>
        </indexterm>
        <function>abs</function> ( <replaceable>numeric_type</replaceable> )
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Absolute value
       </para>
       <para>
        <literal>abs(-17.4)</literal>
        <returnvalue>17.4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>cbrt</primary>
        </indexterm>
        <function>cbrt</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Cube root
       </para>
       <para>
        <literal>cbrt(64.0)</literal>
        <returnvalue>4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>ceil</primary>
        </indexterm>
        <function>ceil</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>ceil</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Nearest integer greater than or equal to argument
       </para>
       <para>
        <literal>ceil(42.2)</literal>
        <returnvalue>43</returnvalue>
       </para>
       <para>
        <literal>ceil(-42.8)</literal>
        <returnvalue>-42</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>ceiling</primary>
        </indexterm>
        <function>ceiling</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>ceiling</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Nearest integer greater than or equal to argument (same
        as <function>ceil</function>)
       </para>
       <para>
        <literal>ceiling(95.3)</literal>
        <returnvalue>96</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>degrees</primary>
        </indexterm>
        <function>degrees</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Converts radians to degrees
       </para>
       <para>
        <literal>degrees(0.5)</literal>
        <returnvalue>28.64788975654116</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>div</primary>
        </indexterm>
        <function>div</function> ( <parameter>y</parameter> <type>numeric</type>,
        <parameter>x</parameter> <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para>
        Integer quotient of <parameter>y</parameter>/<parameter>x</parameter>
        (truncates towards zero)
       </para>
       <para>
        <literal>div(9, 4)</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>exp</primary>
        </indexterm>
        <function>exp</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>exp</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Exponential (<literal>e</literal> raised to the given power)
       </para>
       <para>
        <literal>exp(1.0)</literal>
        <returnvalue>2.7182818284590452</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm id="function-factorial">
         <primary>factorial</primary>
        </indexterm>
        <function>factorial</function> ( <type>bigint</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para>
        Factorial
       </para>
       <para>
        <literal>factorial(5)</literal>
        <returnvalue>120</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>floor</primary>
        </indexterm>
        <function>floor</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>floor</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Nearest integer less than or equal to argument
       </para>
       <para>
        <literal>floor(42.8)</literal>
        <returnvalue>42</returnvalue>
       </para>
       <para>
        <literal>floor(-42.8)</literal>
        <returnvalue>-43</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>gcd</primary>
        </indexterm>
        <function>gcd</function> ( <replaceable>numeric_type</replaceable>, <replaceable>numeric_type</replaceable> )
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Greatest common divisor (the largest positive number that divides both
        inputs with no remainder); returns <literal>0</literal> if both inputs
        are zero; available for <type>integer</type>, <type>bigint</type>,
        and <type>numeric</type>
       </para>
       <para>
        <literal>gcd(1071, 462)</literal>
        <returnvalue>21</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>lcm</primary>
        </indexterm>
        <function>lcm</function> ( <replaceable>numeric_type</replaceable>, <replaceable>numeric_type</replaceable> )
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Least common multiple (the smallest strictly positive number that is
        an integral multiple of both inputs); returns <literal>0</literal> if
        either input is zero; available for <type>integer</type>,
        <type>bigint</type>, and <type>numeric</type>
       </para>
       <para>
        <literal>lcm(1071, 462)</literal>
        <returnvalue>23562</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>ln</primary>
        </indexterm>
        <function>ln</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>ln</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Natural logarithm
       </para>
       <para>
        <literal>ln(2.0)</literal>
        <returnvalue>0.6931471805599453</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>log</primary>
        </indexterm>
        <function>log</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>log</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Base 10 logarithm
       </para>
       <para>
        <literal>log(100)</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>log10</primary>
        </indexterm>
        <function>log10</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>log10</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Base 10 logarithm (same as <function>log</function>)
       </para>
       <para>
        <literal>log10(1000)</literal>
        <returnvalue>3</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>log</function> ( <parameter>b</parameter> <type>numeric</type>,
        <parameter>x</parameter> <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para>
        Logarithm of <parameter>x</parameter> to base <parameter>b</parameter>
       </para>
       <para>
       <literal>log(2.0, 64.0)</literal>
       <returnvalue>6.0000000000000000</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>min_scale</primary>
        </indexterm>
        <function>min_scale</function> ( <type>numeric</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Minimum scale (number of fractional decimal digits) needed
        to represent the supplied value precisely
       </para>
       <para>
        <literal>min_scale(8.4100)</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>mod</primary>
        </indexterm>
        <function>mod</function> ( <parameter>y</parameter> <replaceable>numeric_type</replaceable>,
        <parameter>x</parameter> <replaceable>numeric_type</replaceable> )
        <returnvalue><replaceable>numeric_type</replaceable></returnvalue>
       </para>
       <para>
        Remainder of <parameter>y</parameter>/<parameter>x</parameter>;
        available for <type>smallint</type>, <type>integer</type>,
        <type>bigint</type>, and <type>numeric</type>
       </para>
       <para>
        <literal>mod(9, 4)</literal>
        <returnvalue>1</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>pi</primary>
        </indexterm>
        <function>pi</function> (  )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Approximate value of <phrase role="symbol_font">&pi;</phrase>
       </para>
       <para>
        <literal>pi()</literal>
        <returnvalue>3.141592653589793</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>power</primary>
        </indexterm>
        <function>power</function> ( <parameter>a</parameter> <type>numeric</type>,
        <parameter>b</parameter> <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>power</function> ( <parameter>a</parameter> <type>double precision</type>,
        <parameter>b</parameter> <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        <parameter>a</parameter> raised to the power of <parameter>b</parameter>
       </para>
       <para>
        <literal>power(9, 3)</literal>
        <returnvalue>729</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>radians</primary>
        </indexterm>
        <function>radians</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Converts degrees to radians
       </para>
       <para>
        <literal>radians(45.0)</literal>
        <returnvalue>0.7853981633974483</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>round</primary>
        </indexterm>
        <function>round</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>round</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Rounds to nearest integer.  For <type>numeric</type>, ties are
        broken by rounding away from zero.  For <type>double precision</type>,
        the tie-breaking behavior is platform dependent, but
        <quote>round to nearest even</quote> is the most common rule.
       </para>
       <para>
        <literal>round(42.4)</literal>
        <returnvalue>42</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>round</function> ( <parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para>
        Rounds <parameter>v</parameter> to <parameter>s</parameter> decimal
        places.  Ties are broken by rounding away from zero.
       </para>
       <para>
        <literal>round(42.4382, 2)</literal>
        <returnvalue>42.44</returnvalue>
       </para>
       <para>
        <literal>round(1234.56, -1)</literal>
        <returnvalue>1230</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>scale</primary>
        </indexterm>
        <function>scale</function> ( <type>numeric</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Scale of the argument (the number of decimal digits in the fractional part)
       </para>
       <para>
        <literal>scale(8.4100)</literal>
        <returnvalue>4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>sign</primary>
        </indexterm>
        <function>sign</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>sign</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Sign of the argument (-1, 0, or +1)
       </para>
       <para>
        <literal>sign(-8.4)</literal>
        <returnvalue>-1</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>sqrt</primary>
        </indexterm>
         <function>sqrt</function> ( <type>numeric</type> )
         <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
         <function>sqrt</function> ( <type>double precision</type> )
         <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Square root
       </para>
       <para>
        <literal>sqrt(2)</literal>
        <returnvalue>1.4142135623730951</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>trim_scale</primary>
        </indexterm>
        <function>trim_scale</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para>
        Reduces the value's scale (number of fractional decimal digits) by
        removing trailing zeroes
       </para>
       <para>
        <literal>trim_scale(8.4100)</literal>
        <returnvalue>8.41</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>trunc</primary>
        </indexterm>
        <function>trunc</function> ( <type>numeric</type> )
        <returnvalue>numeric</returnvalue>
       </para>
       <para role="func_signature">
        <function>trunc</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Truncates to integer (towards zero)
       </para>
       <para>
        <literal>trunc(42.8)</literal>
        <returnvalue>42</returnvalue>
       </para>
       <para>
        <literal>trunc(-42.8)</literal>
        <returnvalue>-42</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>trunc</function> ( <parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>integer</type> )
       <returnvalue>numeric</returnvalue>
       </para>
       <para>
        Truncates <parameter>v</parameter> to <parameter>s</parameter>
        decimal places
       </para>
       <para>
        <literal>trunc(42.4382, 2)</literal>
        <returnvalue>42.43</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>width_bucket</primary>
        </indexterm>
        <function>width_bucket</function> ( <parameter>operand</parameter> <type>numeric</type>, <parameter>low</parameter> <type>numeric</type>, <parameter>high</parameter> <type>numeric</type>, <parameter>count</parameter> <type>integer</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para role="func_signature">
        <function>width_bucket</function> ( <parameter>operand</parameter> <type>double precision</type>, <parameter>low</parameter> <type>double precision</type>, <parameter>high</parameter> <type>double precision</type>, <parameter>count</parameter> <type>integer</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the number of the bucket in
        which <parameter>operand</parameter> falls in a histogram
        having <parameter>count</parameter> equal-width buckets spanning the
        range <parameter>low</parameter> to <parameter>high</parameter>.
        Returns <literal>0</literal>
        or <literal><parameter>count</parameter>+1</literal> for an input
        outside that range.
       </para>
       <para>
        <literal>width_bucket(5.35, 0.024, 10.06, 5)</literal>
        <returnvalue>3</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>width_bucket</function> ( <parameter>operand</parameter> <type>anycompatible</type>, <parameter>thresholds</parameter> <type>anycompatiblearray</type> )
       <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the number of the bucket in
        which <parameter>operand</parameter> falls given an array listing the
        lower bounds of the buckets.  Returns <literal>0</literal> for an
        input less than the first lower
        bound.  <parameter>operand</parameter> and the array elements can be
        of any type having standard comparison operators.
        The <parameter>thresholds</parameter> array <emphasis>must be
        sorted</emphasis>, smallest first, or unexpected results will be
        obtained.
       </para>
       <para>
        <literal>width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])</literal>
        <returnvalue>2</returnvalue>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
    <xref linkend="functions-math-random-table"/> shows functions for
    generating random numbers.
  </para>

   <table id="functions-math-random-table">
    <title>Random Functions</title>

    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>random</primary>
        </indexterm>
        <function>random</function> ( )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Returns a random value in the range 0.0 &lt;= x &lt; 1.0
       </para>
       <para>
        <literal>random()</literal>
        <returnvalue>0.897124072839091</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>setseed</primary>
        </indexterm>
        <function>setseed</function> ( <type>double precision</type> )
        <returnvalue>void</returnvalue>
       </para>
       <para>
        Sets the seed for subsequent <literal>random()</literal> calls;
        argument must be between -1.0 and 1.0, inclusive
       </para>
       <para>
        <literal>setseed(0.12345)</literal>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
   The <function>random()</function> function uses a deterministic
   pseudo-random number generator.
   It is fast but not suitable for cryptographic
   applications; see the <xref linkend="pgcrypto"/> module for a more
   secure alternative.
   If <function>setseed()</function> is called, the series of results of
   subsequent <function>random()</function> calls in the current session
   can be repeated by re-issuing <function>setseed()</function> with the same
   argument.
   Without any prior <function>setseed()</function> call in the same
   session, the first <function>random()</function> call obtains a seed
   from a platform-dependent source of random bits.
  </para>

  <para>
   <xref linkend="functions-math-trig-table"/> shows the
   available trigonometric functions.  Each of these functions comes in
   two variants, one that measures angles in radians and one that
   measures angles in degrees.
  </para>

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

    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>acos</primary>
        </indexterm>
        <function>acos</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse cosine, result in radians
       </para>
       <para>
        <literal>acos(1)</literal>
        <returnvalue>0</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>acosd</primary>
        </indexterm>
        <function>acosd</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse cosine, result in degrees
       </para>
       <para>
        <literal>acosd(0.5)</literal>
        <returnvalue>60</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>asin</primary>
        </indexterm>
        <function>asin</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse sine, result in radians
       </para>
       <para>
        <literal>asin(1)</literal>
        <returnvalue>1.5707963267948966</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>asind</primary>
        </indexterm>
        <function>asind</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse sine, result in degrees
       </para>
       <para>
        <literal>asind(0.5)</literal>
        <returnvalue>30</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>atan</primary>
        </indexterm>
        <function>atan</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse tangent, result in radians
       </para>
       <para>
        <literal>atan(1)</literal>
        <returnvalue>0.7853981633974483</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>atand</primary>
        </indexterm>
        <function>atand</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse tangent, result in degrees
       </para>
       <para>
        <literal>atand(1)</literal>
        <returnvalue>45</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>atan2</primary>
        </indexterm>
        <function>atan2</function> ( <parameter>y</parameter> <type>double precision</type>,
        <parameter>x</parameter> <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse tangent of
        <parameter>y</parameter>/<parameter>x</parameter>,
        result in radians
       </para>
       <para>
        <literal>atan2(1, 0)</literal>
        <returnvalue>1.5707963267948966</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>atan2d</primary>
        </indexterm>
        <function>atan2d</function> ( <parameter>y</parameter> <type>double precision</type>,
        <parameter>x</parameter> <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse tangent of
        <parameter>y</parameter>/<parameter>x</parameter>,
        result in degrees
       </para>
       <para>
        <literal>atan2d(1, 0)</literal>
        <returnvalue>90</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>cos</primary>
        </indexterm>
        <function>cos</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Cosine, argument in radians
       </para>
       <para>
        <literal>cos(0)</literal>
        <returnvalue>1</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>cosd</primary>
        </indexterm>
        <function>cosd</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Cosine, argument in degrees
       </para>
       <para>
        <literal>cosd(60)</literal>
        <returnvalue>0.5</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>cot</primary>
        </indexterm>
        <function>cot</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Cotangent, argument in radians
       </para>
       <para>
        <literal>cot(0.5)</literal>
        <returnvalue>1.830487721712452</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>cotd</primary>
        </indexterm>
        <function>cotd</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Cotangent, argument in degrees
       </para>
       <para>
        <literal>cotd(45)</literal>
        <returnvalue>1</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>sin</primary>
        </indexterm>
        <function>sin</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Sine, argument in radians
       </para>
       <para>
        <literal>sin(1)</literal>
        <returnvalue>0.8414709848078965</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>sind</primary>
        </indexterm>
        <function>sind</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Sine, argument in degrees
       </para>
       <para>
        <literal>sind(30)</literal>
        <returnvalue>0.5</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>tan</primary>
        </indexterm>
        <function>tan</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Tangent, argument in radians
       </para>
       <para>
        <literal>tan(1)</literal>
        <returnvalue>1.5574077246549023</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>tand</primary>
        </indexterm>
        <function>tand</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Tangent, argument in degrees
       </para>
       <para>
        <literal>tand(45)</literal>
        <returnvalue>1</returnvalue>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <note>
   <para>
    Another way to work with angles measured in degrees is to use the unit
    transformation functions <literal><function>radians()</function></literal>
    and <literal><function>degrees()</function></literal> shown earlier.
    However, using the degree-based trigonometric functions is preferred,
    as that way avoids round-off error for special cases such
    as <literal>sind(30)</literal>.
   </para>
  </note>

  <para>
   <xref linkend="functions-math-hyp-table"/> shows the
   available hyperbolic functions.
  </para>

  <table id="functions-math-hyp-table">
    <title>Hyperbolic Functions</title>

    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>sinh</primary>
        </indexterm>
        <function>sinh</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Hyperbolic sine
       </para>
       <para>
        <literal>sinh(1)</literal>
        <returnvalue>1.1752011936438014</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>cosh</primary>
        </indexterm>
        <function>cosh</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Hyperbolic cosine
       </para>
       <para>
        <literal>cosh(0)</literal>
        <returnvalue>1</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>tanh</primary>
        </indexterm>
        <function>tanh</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Hyperbolic tangent
       </para>
       <para>
        <literal>tanh(1)</literal>
        <returnvalue>0.7615941559557649</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>asinh</primary>
        </indexterm>
        <function>asinh</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse hyperbolic sine
       </para>
       <para>
        <literal>asinh(1)</literal>
        <returnvalue>0.881373587019543</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>acosh</primary>
        </indexterm>
        <function>acosh</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse hyperbolic cosine
       </para>
       <para>
        <literal>acosh(1)</literal>
        <returnvalue>0</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>atanh</primary>
        </indexterm>
        <function>atanh</function> ( <type>double precision</type> )
        <returnvalue>double precision</returnvalue>
       </para>
       <para>
        Inverse hyperbolic tangent
       </para>
       <para>
        <literal>atanh(0.5)</literal>
        <returnvalue>0.5493061443340548</returnvalue>
       </para></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 the types <type>character</type>, <type>character varying</type>,
    and <type>text</type>.  Except where noted, these functions and operators
    are declared to accept and return type <type>text</type>.  They will
    interchangeably accept <type>character varying</type> arguments.
    Values of type <type>character</type> will be converted
    to <type>text</type> before the function or operator is applied, resulting
    in stripping any trailing spaces in the <type>character</type> value.
   </para>

   <para>
    <acronym>SQL</acronym> defines some string functions that use
    key words, rather than commas, to separate
    arguments.  Details are in
    <xref linkend="functions-string-sql"/>.
    <productname>PostgreSQL</productname> also provides versions of these functions
    that use the regular function invocation syntax
    (see <xref linkend="functions-string-other"/>).
   </para>

   <note>
    <para>
     The string concatenation operator (<literal>||</literal>) will accept
     non-string input, so long as at least one input is of string type, as shown
     in <xref linkend="functions-string-sql"/>.  For other cases, inserting an
     explicit coercion to <type>text</type> can be used to have non-string input
     accepted.
    </para>
   </note>

   <table id="functions-string-sql">
    <title><acronym>SQL</acronym> String Functions and Operators</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function/Operator
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>character string</primary>
         <secondary>concatenation</secondary>
        </indexterm>
        <type>text</type> <literal>||</literal> <type>text</type>
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Concatenates the two strings.
       </para>
       <para>
        <literal>'Post' || 'greSQL'</literal>
        <returnvalue>PostgreSQL</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <type>text</type> <literal>||</literal> <type>anynonarray</type>
        <returnvalue>text</returnvalue>
       </para>
       <para role="func_signature">
        <type>anynonarray</type> <literal>||</literal> <type>text</type>
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Converts the non-string input to text, then concatenates the two
        strings.  (The non-string input cannot be of an array type, because
        that would create ambiguity with the array <literal>||</literal>
        operators.  If you want to concatenate an array's text equivalent,
        cast it to <type>text</type> explicitly.)
       </para>
       <para>
        <literal>'Value: ' || 42</literal>
        <returnvalue>Value: 42</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>normalized</primary>
        </indexterm>
        <indexterm>
         <primary>Unicode normalization</primary>
        </indexterm>
         <type>text</type> <literal>IS</literal> <optional><literal>NOT</literal></optional> <optional><parameter>form</parameter></optional> <literal>NORMALIZED</literal>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Checks whether the string is in the specified Unicode normalization
        form.  The optional <parameter>form</parameter> key word specifies the
        form: <literal>NFC</literal> (the default), <literal>NFD</literal>,
        <literal>NFKC</literal>, or <literal>NFKD</literal>.  This expression can
        only be used when the server encoding is <literal>UTF8</literal>.  Note
        that checking for normalization using this expression is often faster
        than normalizing possibly already normalized strings.
       </para>
       <para>
        <literal>U&amp;'\0061\0308bc' IS NFD NORMALIZED</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>bit_length</primary>
        </indexterm>
        <function>bit_length</function> ( <type>text</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns number of bits in the string (8
        times the <function>octet_length</function>).
       </para>
       <para>
        <literal>bit_length('jose')</literal>
        <returnvalue>32</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>char_length</primary>
        </indexterm>
        <indexterm>
         <primary>character string</primary>
         <secondary>length</secondary>
        </indexterm>
        <indexterm>
         <primary>length</primary>
         <secondary sortas="character string">of a character string</secondary>
         <see>character string, length</see>
        </indexterm>
        <function>char_length</function> ( <type>text</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para role="func_signature">
        <indexterm>
         <primary>character_length</primary>
        </indexterm>
        <function>character_length</function> ( <type>text</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns number of characters in the string.
       </para>
       <para>
        <literal>char_length('jos&eacute;')</literal>
        <returnvalue>4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>lower</primary>
        </indexterm>
        <function>lower</function> ( <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Converts the string to all lower case, according to the rules of the
        database's locale.
       </para>
       <para>
        <literal>lower('TOM')</literal>
        <returnvalue>tom</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>normalize</primary>
        </indexterm>
        <indexterm>
         <primary>Unicode normalization</primary>
        </indexterm>
        <function>normalize</function> ( <type>text</type>
        <optional>, <parameter>form</parameter> </optional> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Converts the string to the specified Unicode
        normalization form.  The optional <parameter>form</parameter> key word
        specifies the form: <literal>NFC</literal> (the default),
        <literal>NFD</literal>, <literal>NFKC</literal>, or
        <literal>NFKD</literal>.  This function can only be used when the
        server encoding is <literal>UTF8</literal>.
       </para>
       <para>
        <literal>normalize(U&amp;'\0061\0308bc', NFC)</literal>
        <returnvalue>U&amp;'\00E4bc'</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>octet_length</primary>
        </indexterm>
        <function>octet_length</function> ( <type>text</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns number of bytes in the string.
       </para>
       <para>
        <literal>octet_length('jos&eacute;')</literal>
        <returnvalue>5</returnvalue> (if server encoding is UTF8)
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>octet_length</primary>
        </indexterm>
        <function>octet_length</function> ( <type>character</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns number of bytes in the string.  Since this version of the
        function accepts type <type>character</type> directly, it will not
        strip trailing spaces.
       </para>
       <para>
        <literal>octet_length('abc '::character(4))</literal>
        <returnvalue>4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>overlay</primary>
        </indexterm>
        <function>overlay</function> ( <parameter>string</parameter> <type>text</type> <literal>PLACING</literal> <parameter>newsubstring</parameter> <type>text</type> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Replaces the substring of <parameter>string</parameter> that starts at
        the <parameter>start</parameter>'th character and extends
        for <parameter>count</parameter> characters
        with <parameter>newsubstring</parameter>.
        If <parameter>count</parameter> is omitted, it defaults to the length
        of <parameter>newsubstring</parameter>.
       </para>
       <para>
        <literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal>
        <returnvalue>Thomas</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>position</primary>
        </indexterm>
        <function>position</function> ( <parameter>substring</parameter> <type>text</type> <literal>IN</literal> <parameter>string</parameter> <type>text</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns first starting index of the specified
        <parameter>substring</parameter> within
        <parameter>string</parameter>, or zero if it's not present.
       </para>
       <para>
        <literal>position('om' in 'Thomas')</literal>
        <returnvalue>3</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>substring</primary>
        </indexterm>
        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Extracts the substring of <parameter>string</parameter> starting at
        the <parameter>start</parameter>'th character if that is specified,
        and stopping after <parameter>count</parameter> characters if that is
        specified.  Provide at least one of <parameter>start</parameter>
        and <parameter>count</parameter>.
       </para>
       <para>
        <literal>substring('Thomas' from 2 for 3)</literal>
        <returnvalue>hom</returnvalue>
       </para>
       <para>
        <literal>substring('Thomas' from 3)</literal>
        <returnvalue>omas</returnvalue>
       </para>
       <para>
        <literal>substring('Thomas' for 2)</literal>
        <returnvalue>Th</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Extracts the first substring matching POSIX regular expression; see
        <xref linkend="functions-posix-regexp"/>.
       </para>
       <para>
        <literal>substring('Thomas' from '...$')</literal>
        <returnvalue>mas</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>SIMILAR</literal> <parameter>pattern</parameter> <type>text</type> <literal>ESCAPE</literal> <parameter>escape</parameter> <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para role="func_signature">
        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> <literal>FOR</literal> <parameter>escape</parameter> <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Extracts the first substring matching <acronym>SQL</acronym> regular expression;
        see <xref linkend="functions-similarto-regexp"/>.  The first form has
        been specified since SQL:2003; the second form was only in SQL:1999
        and should be considered obsolete.
       </para>
       <para>
        <literal>substring('Thomas' similar '%#"o_a#"_' escape '#')</literal>
        <returnvalue>oma</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>trim</primary>
        </indexterm>
        <function>trim</function> ( <optional> <literal>LEADING</literal> | <literal>TRAILING</literal> | <literal>BOTH</literal> </optional>
        <optional> <parameter>characters</parameter> <type>text</type> </optional> <literal>FROM</literal>
        <parameter>string</parameter> <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Removes the longest string containing only characters in
        <parameter>characters</parameter> (a space by default) from the
        start, end, or both ends (<literal>BOTH</literal> is the default)
        of <parameter>string</parameter>.
       </para>
       <para>
        <literal>trim(both 'xyz' from 'yxTomxx')</literal>
        <returnvalue>Tom</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>trim</function> ( <optional> <literal>LEADING</literal> | <literal>TRAILING</literal> | <literal>BOTH</literal> </optional> <optional> <literal>FROM</literal> </optional>
        <parameter>string</parameter> <type>text</type> <optional>,
        <parameter>characters</parameter> <type>text</type> </optional> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        This is a non-standard syntax for <function>trim()</function>.
       </para>
       <para>
        <literal>trim(both from 'yxTomxx', 'xyz')</literal>
        <returnvalue>Tom</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>upper</primary>
        </indexterm>
        <function>upper</function> ( <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Converts the string to all upper case, according to the rules of the
        database's locale.
       </para>
       <para>
        <literal>upper('tom')</literal>
        <returnvalue>TOM</returnvalue>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Additional string manipulation functions and operators are available
    and are listed in <xref linkend="functions-string-other"/>.  (Some of
    these are used internally to implement
    the <acronym>SQL</acronym>-standard string functions listed in
    <xref linkend="functions-string-sql"/>.)
    There are also pattern-matching operators, which are described in
    <xref linkend="functions-matching"/>, and operators for full-text
    search, which are described in <xref linkend="textsearch"/>.
   </para>

   <table id="functions-string-other">
    <title>Other String Functions and Operators</title>
    <tgroup cols="1">
     <thead>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        Function/Operator
       </para>
       <para>
        Description
       </para>
       <para>
        Example(s)
       </para></entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>character string</primary>
         <secondary>prefix test</secondary>
        </indexterm>
        <type>text</type> <literal>^@</literal> <type>text</type>
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Returns true if the first string starts with the second string
        (equivalent to the <function>starts_with()</function> function).
       </para>
       <para>
        <literal>'alphabet' ^@ 'alph'</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>ascii</primary>
        </indexterm>
        <function>ascii</function> ( <type>text</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the numeric code of the first character of the argument.
        In <acronym>UTF8</acronym> encoding, returns the Unicode code point
        of the character.  In other multibyte encodings, the argument must
        be an <acronym>ASCII</acronym> character.
       </para>
       <para>
        <literal>ascii('x')</literal>
        <returnvalue>120</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>btrim</primary>
        </indexterm>
        <function>btrim</function> ( <parameter>string</parameter> <type>text</type>
        <optional>, <parameter>characters</parameter> <type>text</type> </optional> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Removes the longest string containing only characters
        in <parameter>characters</parameter> (a space by default)
        from the start and end of <parameter>string</parameter>.
       </para>
       <para>
        <literal>btrim('xyxtrimyyx', 'xyz')</literal>
        <returnvalue>trim</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>chr</primary>
        </indexterm>
        <function>chr</function> ( <type>integer</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Returns the character with the given code. In <acronym>UTF8</acronym>
        encoding the argument is treated as a Unicode code point. In other
        multibyte encodings the argument must designate
        an <acronym>ASCII</acronym> character.  <literal>chr(0)</literal> is
        disallowed because text data types cannot store that character.
      </para>
      <para>
        <literal>chr(65)</literal>
        <returnvalue>A</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>concat</primary>
        </indexterm>
        <function>concat</function> ( <parameter>val1</parameter> <type>"any"</type>
         [, <parameter>val2</parameter> <type>"any"</type> [, ...] ] )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Concatenates the text representations of all the arguments.
        NULL arguments are ignored.
       </para>
       <para>
        <literal>concat('abcde', 2, NULL, 22)</literal>
        <returnvalue>abcde222</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>concat_ws</primary>
        </indexterm>
        <function>concat_ws</function> ( <parameter>sep</parameter> <type>text</type>,
        <parameter>val1</parameter> <type>"any"</type>
        [, <parameter>val2</parameter> <type>"any"</type> [, ...] ] )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Concatenates all but the first argument, with separators. The first
        argument is used as the separator string, and should not be NULL.
        Other NULL arguments are ignored.
       </para>
       <para>
        <literal>concat_ws(',', 'abcde', 2, NULL, 22)</literal>
        <returnvalue>abcde,2,22</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>format</primary>
        </indexterm>
        <function>format</function> ( <parameter>formatstr</parameter> <type>text</type>
        [, <parameter>formatarg</parameter> <type>"any"</type> [, ...] ] )
        <returnvalue>text</returnvalue>
       </para>
       <para>
         Formats arguments according to a format string;
         see <xref linkend="functions-string-format"/>.
         This function is similar to the C function <function>sprintf</function>.
       </para>
       <para>
        <literal>format('Hello %s, %1$s', 'World')</literal>
        <returnvalue>Hello World, World</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>initcap</primary>
        </indexterm>
        <function>initcap</function> ( <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Converts the first letter of each word to upper case and the
        rest to lower case. Words are sequences of alphanumeric
        characters separated by non-alphanumeric characters.
       </para>
       <para>
        <literal>initcap('hi THOMAS')</literal>
        <returnvalue>Hi Thomas</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>left</primary>
        </indexterm>
        <function>left</function> ( <parameter>string</parameter> <type>text</type>,
        <parameter>n</parameter> <type>integer</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Returns first <parameter>n</parameter> characters in the
        string, or when <parameter>n</parameter> is negative, returns
        all but last |<parameter>n</parameter>| characters.
       </para>
       <para>
        <literal>left('abcde', 2)</literal>
        <returnvalue>ab</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>length</primary>
        </indexterm>
        <function>length</function> ( <type>text</type> )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the number of characters in the string.
       </para>
       <para>
        <literal>length('jose')</literal>
        <returnvalue>4</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>lpad</primary>
        </indexterm>
        <function>lpad</function> ( <parameter>string</parameter> <type>text</type>,
        <parameter>length</parameter> <type>integer</type>
        <optional>, <parameter>fill</parameter> <type>text</type> </optional> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Extends 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).
       </para>
       <para>
        <literal>lpad('hi', 5, 'xy')</literal>
        <returnvalue>xyxhi</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>ltrim</primary>
        </indexterm>
        <function>ltrim</function> ( <parameter>string</parameter> <type>text</type>
        <optional>, <parameter>characters</parameter> <type>text</type> </optional> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Removes the longest string containing only characters in
        <parameter>characters</parameter> (a space by default) from the start of
        <parameter>string</parameter>.
       </para>
       <para>
        <literal>ltrim('zzzytest', 'xyz')</literal>
        <returnvalue>test</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>md5</primary>
        </indexterm>
        <function>md5</function> ( <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Computes the MD5 <link linkend="functions-hash-note">hash</link> of
        the argument, with the result written in hexadecimal.
       </para>
       <para>
        <literal>md5('abc')</literal>
        <returnvalue>900150983cd24fb0&zwsp;d6963f7d28e17f72</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>parse_ident</primary>
        </indexterm>
        <function>parse_ident</function> ( <parameter>qualified_identifier</parameter> <type>text</type>
        [, <parameter>strict_mode</parameter> <type>boolean</type> <literal>DEFAULT</literal> <literal>true</literal> ] )
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Splits <parameter>qualified_identifier</parameter> into an array of
        identifiers, removing any quoting of individual identifiers.  By
        default, extra characters after the last identifier are considered an
        error; but if the second parameter is <literal>false</literal>, then such
        extra characters are ignored. (This behavior is useful for parsing
        names for objects like functions.) Note that this function does not
        truncate over-length identifiers. If you want truncation you can cast
        the result to <type>name[]</type>.
       </para>
       <para>
        <literal>parse_ident('"SomeSchema".someTable')</literal>
        <returnvalue>{SomeSchema,sometable}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>pg_client_encoding</primary>
        </indexterm>
        <function>pg_client_encoding</function> ( )
        <returnvalue>name</returnvalue>
       </para>
       <para>
        Returns current client encoding name.
       </para>
       <para>
        <literal>pg_client_encoding()</literal>
        <returnvalue>UTF8</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>quote_ident</primary>
        </indexterm>
        <function>quote_ident</function> ( <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Returns the given string suitably quoted to be used as an identifier
        in an <acronym>SQL</acronym> statement 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.
        See also <xref linkend="plpgsql-quote-literal-example"/>.
       </para>
       <para>
        <literal>quote_ident('Foo bar')</literal>
        <returnvalue>"Foo bar"</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>quote_literal</primary>
        </indexterm>
        <function>quote_literal</function> ( <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Returns the given string suitably quoted to be used as a string literal
        in an <acronym>SQL</acronym> statement string.
        Embedded single-quotes and backslashes are properly doubled.
        Note that <function>quote_literal</function> returns null on null
        input; if the argument might be null,
        <function>quote_nullable</function> is often more suitable.
        See also <xref linkend="plpgsql-quote-literal-example"/>.
       </para>
       <para>
        <literal>quote_literal(E'O\'Reilly')</literal>
        <returnvalue>'O''Reilly'</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>quote_literal</function> ( <type>anyelement</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Converts the given value to text and then quotes it as a literal.
        Embedded single-quotes and backslashes are properly doubled.
       </para>
       <para>
        <literal>quote_literal(42.5)</literal>
        <returnvalue>'42.5'</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>quote_nullable</primary>
        </indexterm>
        <function>quote_nullable</function> ( <type>text</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Returns the given string suitably quoted to be used as a string literal
        in an <acronym>SQL</acronym> statement string; or, if the argument
        is null, returns <literal>NULL</literal>.
        Embedded single-quotes and backslashes are properly doubled.
        See also <xref linkend="plpgsql-quote-literal-example"/>.
       </para>
       <para>
        <literal>quote_nullable(NULL)</literal>
        <returnvalue>NULL</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>quote_nullable</function> ( <type>anyelement</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Converts the given value to text and then quotes it as a literal;
        or, if the argument is null, returns <literal>NULL</literal>.
        Embedded single-quotes and backslashes are properly doubled.
       </para>
       <para>
        <literal>quote_nullable(42.5)</literal>
        <returnvalue>'42.5'</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>regexp_count</primary>
        </indexterm>
        <function>regexp_count</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
         [, <parameter>start</parameter> <type>integer</type>
         [, <parameter>flags</parameter> <type>text</type> ] ] )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the number of times the POSIX regular
        expression <parameter>pattern</parameter> matches in
        the <parameter>string</parameter>; see
        <xref linkend="functions-posix-regexp"/>.
       </para>
       <para>
        <literal>regexp_count('123456789012', '\d\d\d', 2)</literal>
        <returnvalue>3</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>regexp_instr</primary>
        </indexterm>
        <function>regexp_instr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
         [, <parameter>start</parameter> <type>integer</type>
         [, <parameter>N</parameter> <type>integer</type>
         [, <parameter>endoption</parameter> <type>integer</type>
         [, <parameter>flags</parameter> <type>text</type>
         [, <parameter>subexpr</parameter> <type>integer</type> ] ] ] ] ] )
        <returnvalue>integer</returnvalue>
       </para>
       <para>
        Returns the position within <parameter>string</parameter> where
        the <parameter>N</parameter>'th match of the POSIX regular
        expression <parameter>pattern</parameter> occurs, or zero if there is
        no such match; see <xref linkend="functions-posix-regexp"/>.
       </para>
       <para>
        <literal>regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i')</literal>
        <returnvalue>3</returnvalue>
       </para>
       <para>
        <literal>regexp_instr('ABCDEF', 'c(.)(..)', 1, 1, 0, 'i', 2)</literal>
        <returnvalue>5</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>regexp_like</primary>
        </indexterm>
        <function>regexp_like</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
         [, <parameter>flags</parameter> <type>text</type> ] )
        <returnvalue>boolean</returnvalue>
       </para>
       <para>
        Checks whether a match of the POSIX regular
        expression <parameter>pattern</parameter> occurs
        within <parameter>string</parameter>; see
        <xref linkend="functions-posix-regexp"/>.
       </para>
       <para>
        <literal>regexp_like('Hello World', 'world$', 'i')</literal>
        <returnvalue>t</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>regexp_match</primary>
        </indexterm>
        <function>regexp_match</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> [, <parameter>flags</parameter> <type>text</type> ] )
        <returnvalue>text[]</returnvalue>
       </para>
       <para>
        Returns substrings within the first match of the POSIX regular
        expression <parameter>pattern</parameter> to
        the <parameter>string</parameter>; see
        <xref linkend="functions-posix-regexp"/>.
       </para>
       <para>
        <literal>regexp_match('foobarbequebaz', '(bar)(beque)')</literal>
        <returnvalue>{bar,beque}</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>regexp_matches</primary>
        </indexterm>
        <function>regexp_matches</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> [, <parameter>flags</parameter> <type>text</type> ] )
        <returnvalue>setof text[]</returnvalue>
       </para>
       <para>
        Returns substrings within the first match of the POSIX regular
        expression <parameter>pattern</parameter> to
        the <parameter>string</parameter>, or substrings within all
        such matches if the <literal>g</literal> flag is used;
        see <xref linkend="functions-posix-regexp"/>.
       </para>
       <para>
        <literal>regexp_matches('foobarbequebaz', 'ba.', 'g')</literal>
        <returnvalue></returnvalue>
<programlisting>
 {bar}
 {baz}
</programlisting>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm>
         <primary>regexp_replace</primary>
        </indexterm>
        <function>regexp_replace</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type>
         [, <parameter>start</parameter> <type>integer</type> ]
         [, <parameter>flags</parameter> <type>text</type> ] )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Replaces the substring that is the first match to the POSIX
        regular expression <parameter>pattern</parameter>, or all such
        matches if the <literal>g</literal> flag is used; see
        <xref linkend="functions-posix-regexp"/>.
       </para>
       <para>
        <literal>regexp_replace('Thomas', '.[mN]a.', 'M')</literal>
        <returnvalue>ThM</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>regexp_replace</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type>,
         <parameter>start</parameter> <type>integer</type>,
         <paramet