~ubuntu-branches/ubuntu/trusty/postgresql-9.3/trusty-proposed

<!-- 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.  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>

   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.  This chapter is also

   not exhaustive;  additional functions appear in relevant sections of

   the manual.

  </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>

    <simplelist>

     <member><literal>AND</></member>

     <member><literal>OR</></member>

     <member><literal>NOT</></member>

    </simplelist>

    <acronym>SQL</acronym> uses a three-valued logic system with true,

    false, and <literal>null</>, 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 operand

    without affecting the result.  But see <xref

    linkend="syntax-express-eval"> for more information about the

    order of evaluation of subexpressions.

   </para>

  </sect1>

  <sect1 id="functions-comparison">

   <title>Comparison Operators</title>

   <indexterm zone="functions-comparison">

    <primary>comparison</primary>

    <secondary>operators</secondary>

   </indexterm>

   <para>

    The usual comparison operators are available, shown in <xref

    linkend="functions-comparison-table">.

   </para>

   <table id="functions-comparison-table">

    <title>Comparison Operators</title>

    <tgroup cols="2">

     <thead>

      <row>

       <entry>Operator</entry>

       <entry>Description</entry>

      </row>

     </thead>

     <tbody>

      <row>

       <entry> <literal>&lt;</literal> </entry>

       <entry>less than</entry>

      </row>

      <row>

       <entry> <literal>&gt;</literal> </entry>

       <entry>greater than</entry>

      </row>

      <row>

       <entry> <literal>&lt;=</literal> </entry>

       <entry>less than or equal to</entry>

      </row>

      <row>

       <entry> <literal>&gt;=</literal> </entry>

       <entry>greater than or equal to</entry>

      </row>

      <row>

       <entry> <literal>=</literal> </entry>

       <entry>equal</entry>

      </row>

      <row>

       <entry> <literal>&lt;&gt;</literal> or <literal>!=</literal> </entry>

       <entry>not equal</entry>

      </row>

     </tbody>

    </tgroup>

   </table>

   <note>

    <para>

     The <literal>!=</literal> operator is converted to

     <literal>&lt;&gt;</literal> in the parser stage.  It is not

     possible to implement <literal>!=</literal> and

     <literal>&lt;&gt;</literal> operators that do different things.

    </para>

   </note>

   <para>

    Comparison operators are available for all relevant data types.

    All comparison operators are binary operators that

    return values of type <type>boolean</type>; expressions like

    <literal>1 &lt; 2 &lt; 3</literal> are not valid (because there is

    no <literal>&lt;</literal> operator to compare a Boolean value with

    <literal>3</literal>).

   </para>

   <para>

    <indexterm>

     <primary>BETWEEN</primary>

    </indexterm>

    In addition to the comparison operators, the special

    <token>BETWEEN</token> construct is available:

<synopsis>

<replaceable>a</replaceable> BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>

</synopsis>

    is equivalent to

<synopsis>

<replaceable>a</replaceable> &gt;= <replaceable>x</replaceable> AND <replaceable>a</replaceable> &lt;= <replaceable>y</replaceable>

</synopsis>

    Notice that <token>BETWEEN</token> treats the endpoint values as included

    in the range.

    <literal>NOT BETWEEN</literal> does the opposite comparison:

<synopsis>

<replaceable>a</replaceable> NOT BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>

</synopsis>

    is equivalent to

<synopsis>

<replaceable>a</replaceable> &lt; <replaceable>x</replaceable> OR <replaceable>a</replaceable> &gt; <replaceable>y</replaceable>

</synopsis>

    <indexterm>

     <primary>BETWEEN SYMMETRIC</primary>

    </indexterm>

    <literal>BETWEEN SYMMETRIC</> is the same as <literal>BETWEEN</>

    except there is no requirement that the argument to the left of

    <literal>AND</> 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>

    <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 constructs:

<synopsis>

<replaceable>expression</replaceable> IS NULL

<replaceable>expression</replaceable> IS NOT NULL

</synopsis>

    or the equivalent, but nonstandard, constructs:

<synopsis>

<replaceable>expression</replaceable> ISNULL

<replaceable>expression</replaceable> NOTNULL

</synopsis>

    <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</> is not <quote>equal to</quote>

    <literal>NULL</>.  (The null value represents an unknown value,

    and it is not known whether two unknown values are equal.) This

    behavior conforms to the SQL standard.

   </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>

  <note>

   <para>

    If the <replaceable>expression</replaceable> is row-valued, then

    <literal>IS NULL</> is true when the row expression itself is null

    or when all the row's fields are null, while

    <literal>IS NOT NULL</> 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</> and <literal>IS NOT NULL</> do not always return

    inverse results for row-valued expressions, i.e., a row-valued

    expression that contains both NULL and non-null values will return false

    for both tests.

    This definition conforms to the SQL standard, and is a change from the

    inconsistent behavior exhibited by <productname>PostgreSQL</productname>

    versions prior to 8.2.

   </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</>),

    not true or false, when either input is null.  For example,

    <literal>7 = NULL</> yields null, as does <literal>7 &lt;&gt; NULL</>.  When

    this behavior is not suitable, use the

    <literal>IS <optional> NOT </> DISTINCT FROM</literal> constructs:

<synopsis>

<replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable>

<replaceable>expression</replaceable> IS NOT DISTINCT FROM <replaceable>expression</replaceable>

</synopsis>

    For non-null inputs, <literal>IS DISTINCT FROM</literal> is

    the same as the <literal>&lt;&gt;</> 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 constructs effectively act as though null

    were a normal data value, rather than <quote>unknown</>.

   </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 constructs

<synopsis>

<replaceable>expression</replaceable> IS TRUE

<replaceable>expression</replaceable> IS NOT TRUE

<replaceable>expression</replaceable> IS FALSE

<replaceable>expression</replaceable> IS NOT FALSE

<replaceable>expression</replaceable> IS UNKNOWN

<replaceable>expression</replaceable> IS NOT UNKNOWN

</synopsis>

    These 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</>.

    Notice that <literal>IS UNKNOWN</> and <literal>IS NOT UNKNOWN</> 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>

<!-- IS OF does not conform to the ISO SQL behavior, so it is undocumented here

   <para>

    <indexterm>

     <primary>IS OF</primary>

    </indexterm>

    <indexterm>

     <primary>IS NOT OF</primary>

    </indexterm>

    It is possible to check the data type of an expression using the

    constructs

<synopsis>

<replaceable>expression</replaceable> IS OF (typename, ...)

<replaceable>expression</replaceable> IS NOT OF (typename, ...)

</synopsis>

    They return a boolean value based on whether the expression's data

    type is one of the listed data types.

   </para>

-->

  </sect1>

  <sect1 id="functions-math">

   <title>Mathematical Functions and Operators</title>

   <para>

    Mathematical operators are provided for many

    <productname>PostgreSQL</productname> types. For types without

    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 available mathematical operators.

   </para>

   <table id="functions-math-op-table">

    <title>Mathematical Operators</title>

    <tgroup cols="4">

     <thead>

      <row>

       <entry>Operator</entry>

       <entry>Description</entry>

       <entry>Example</entry>

       <entry>Result</entry>

      </row>

     </thead>

     <tbody>

      <row>

       <entry> <literal>+</literal> </entry>

       <entry>addition</entry>

       <entry><literal>2 + 3</literal></entry>

       <entry><literal>5</literal></entry>

      </row>

      <row>

       <entry> <literal>-</literal> </entry>

       <entry>subtraction</entry>

       <entry><literal>2 - 3</literal></entry>

       <entry><literal>-1</literal></entry>

      </row>

      <row>

       <entry> <literal>*</literal> </entry>

       <entry>multiplication</entry>

       <entry><literal>2 * 3</literal></entry>

       <entry><literal>6</literal></entry>

      </row>

      <row>

       <entry> <literal>/</literal> </entry>

       <entry>division (integer division truncates the result)</entry>

       <entry><literal>4 / 2</literal></entry>

       <entry><literal>2</literal></entry>

      </row>

      <row>

       <entry> <literal>%</literal> </entry>

       <entry>modulo (remainder)</entry>

       <entry><literal>5 % 4</literal></entry>

       <entry><literal>1</literal></entry>

      </row>

      <row>

       <entry> <literal>^</literal> </entry>

       <entry>exponentiation (associates left to right)</entry>

       <entry><literal>2.0 ^ 3.0</literal></entry>

       <entry><literal>8</literal></entry>

      </row>

      <row>

       <entry> <literal>|/</literal> </entry>

       <entry>square root</entry>

       <entry><literal>|/ 25.0</literal></entry>

       <entry><literal>5</literal></entry>

      </row>

      <row>

       <entry> <literal>||/</literal> </entry>

       <entry>cube root</entry>

       <entry><literal>||/ 27.0</literal></entry>

       <entry><literal>3</literal></entry>

      </row>

      <row>

       <entry> <literal>!</literal> </entry>

       <entry>factorial</entry>

       <entry><literal>5 !</literal></entry>

       <entry><literal>120</literal></entry>

      </row>

      <row>

       <entry> <literal>!!</literal> </entry>

       <entry>factorial (prefix operator)</entry>

       <entry><literal>!! 5</literal></entry>

       <entry><literal>120</literal></entry>

      </row>

      <row>

       <entry> <literal>@</literal> </entry>

       <entry>absolute value</entry>

       <entry><literal>@ -5.0</literal></entry>

       <entry><literal>5</literal></entry>

      </row>

      <row>

       <entry> <literal>&amp;</literal> </entry>

       <entry>bitwise AND</entry>

       <entry><literal>91 &amp; 15</literal></entry>

       <entry><literal>11</literal></entry>

      </row>

      <row>

       <entry> <literal>|</literal> </entry>

       <entry>bitwise OR</entry>

       <entry><literal>32 | 3</literal></entry>

       <entry><literal>35</literal></entry>

      </row>

      <row>

       <entry> <literal>#</literal> </entry>

       <entry>bitwise XOR</entry>

       <entry><literal>17 # 5</literal></entry>

       <entry><literal>20</literal></entry>

      </row>

      <row>

       <entry> <literal>~</literal> </entry>

       <entry>bitwise NOT</entry>

       <entry><literal>~1</literal></entry>

       <entry><literal>-2</literal></entry>

      </row>

      <row>

       <entry> <literal>&lt;&lt;</literal> </entry>

       <entry>bitwise shift left</entry>

       <entry><literal>1 &lt;&lt; 4</literal></entry>

       <entry><literal>16</literal></entry>

      </row>

      <row>

       <entry> <literal>&gt;&gt;</literal> </entry>

       <entry>bitwise shift right</entry>

       <entry><literal>8 &gt;&gt; 2</literal></entry>

       <entry><literal>2</literal></entry>

      </row>

     </tbody>

    </tgroup>

   </table>

   <para>

    The bitwise operators work only on integral data types, whereas

    the others are available for all numeric data types.  The bitwise

    operators are also available for the bit

    string types <type>bit</type> and <type>bit varying</type>, as

    shown in <xref linkend="functions-bit-string-op-table">.

   </para>

  <para>

   <xref linkend="functions-math-func-table"> shows the available

   mathematical functions.  In the table, <literal>dp</literal>

   indicates <type>double precision</type>.  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.

   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="5">

     <thead>

      <row>

       <entry>Function</entry>

       <entry>Return Type</entry>

       <entry>Description</entry>

       <entry>Example</entry>

       <entry>Result</entry>

      </row>

     </thead>

     <tbody>

      <row>

       <entry>

        <indexterm>

         <primary>abs</primary>

        </indexterm>

        <literal><function>abs(<replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>absolute value</entry>

       <entry><literal>abs(-17.4)</literal></entry>

       <entry><literal>17.4</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>cbrt</primary>

        </indexterm>

        <literal><function>cbrt(<type>dp</type>)</function></literal>

       </entry>

       <entry><type>dp</type></entry>

       <entry>cube root</entry>

       <entry><literal>cbrt(27.0)</literal></entry>

       <entry><literal>3</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>ceil</primary>

        </indexterm>

        <literal><function>ceil(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>smallest integer not less than argument</entry>

       <entry><literal>ceil(-42.8)</literal></entry>

       <entry><literal>-42</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>ceiling</primary>

        </indexterm>

        <literal><function>ceiling(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>smallest integer not less than argument (alias for <function>ceil</function>)</entry>

       <entry><literal>ceiling(-95.3)</literal></entry>

       <entry><literal>-95</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>degrees</primary>

        </indexterm>

        <literal><function>degrees(<type>dp</type>)</function></literal>

       </entry>

       <entry><type>dp</type></entry>

       <entry>radians to degrees</entry>

       <entry><literal>degrees(0.5)</literal></entry>

       <entry><literal>28.6478897565412</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>div</primary>

        </indexterm>

        <literal><function>div(<parameter>y</parameter> <type>numeric</>,

         <parameter>x</parameter> <type>numeric</>)</function></literal>

       </entry>

       <entry><type>numeric</></entry>

       <entry>integer quotient of <parameter>y</parameter>/<parameter>x</parameter></entry>

       <entry><literal>div(9,4)</literal></entry>

       <entry><literal>2</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>exp</primary>

        </indexterm>

        <literal><function>exp(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>exponential</entry>

       <entry><literal>exp(1.0)</literal></entry>

       <entry><literal>2.71828182845905</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>floor</primary>

        </indexterm>

        <literal><function>floor(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>largest integer not greater than argument</entry>

       <entry><literal>floor(-42.8)</literal></entry>

       <entry><literal>-43</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>ln</primary>

        </indexterm>

        <literal><function>ln(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>natural logarithm</entry>

       <entry><literal>ln(2.0)</literal></entry>

       <entry><literal>0.693147180559945</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>log</primary>

        </indexterm>

        <literal><function>log(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>base 10 logarithm</entry>

       <entry><literal>log(100.0)</literal></entry>

       <entry><literal>2</literal></entry>

      </row>

      <row>

       <entry><literal><function>log(<parameter>b</parameter> <type>numeric</type>,

        <parameter>x</parameter> <type>numeric</type>)</function></literal></entry>

       <entry><type>numeric</type></entry>

       <entry>logarithm to base <parameter>b</parameter></entry>

       <entry><literal>log(2.0, 64.0)</literal></entry>

       <entry><literal>6.0000000000</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>mod</primary>

        </indexterm>

        <literal><function>mod(<parameter>y</parameter>,

         <parameter>x</parameter>)</function></literal>

       </entry>

       <entry>(same as argument types)</entry>

       <entry>remainder of <parameter>y</parameter>/<parameter>x</parameter></entry>

       <entry><literal>mod(9,4)</literal></entry>

       <entry><literal>1</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>pi</primary>

        </indexterm>

        <literal><function>pi()</function></literal>

       </entry>

       <entry><type>dp</type></entry>

       <entry><quote>&pi;</quote> constant</entry>

       <entry><literal>pi()</literal></entry>

       <entry><literal>3.14159265358979</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>power</primary>

        </indexterm>

        <literal><function>power(<parameter>a</parameter> <type>dp</type>,

        <parameter>b</parameter> <type>dp</type>)</function></literal>

       </entry>

       <entry><type>dp</type></entry>

       <entry><parameter>a</> raised to the power of <parameter>b</parameter></entry>

       <entry><literal>power(9.0, 3.0)</literal></entry>

       <entry><literal>729</literal></entry>

      </row>

      <row>

       <entry><literal><function>power(<parameter>a</parameter> <type>numeric</type>,

        <parameter>b</parameter> <type>numeric</type>)</function></literal></entry>

       <entry><type>numeric</type></entry>

       <entry><parameter>a</> raised to the power of <parameter>b</parameter></entry>

       <entry><literal>power(9.0, 3.0)</literal></entry>

       <entry><literal>729</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>radians</primary>

        </indexterm>

        <literal><function>radians(<type>dp</type>)</function></literal>

       </entry>

       <entry><type>dp</type></entry>

       <entry>degrees to radians</entry>

       <entry><literal>radians(45.0)</literal></entry>

       <entry><literal>0.785398163397448</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>round</primary>

        </indexterm>

        <literal><function>round(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>round to nearest integer</entry>

       <entry><literal>round(42.4)</literal></entry>

       <entry><literal>42</literal></entry>

      </row>

      <row>

       <entry><literal><function>round(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>int</type>)</function></literal></entry>

       <entry><type>numeric</type></entry>

       <entry>round to <parameter>s</parameter> decimal places</entry>

       <entry><literal>round(42.4382, 2)</literal></entry>

       <entry><literal>42.44</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>sign</primary>

        </indexterm>

        <literal><function>sign(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>sign of the argument (-1, 0, +1)</entry>

       <entry><literal>sign(-8.4)</literal></entry>

       <entry><literal>-1</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>sqrt</primary>

        </indexterm>

        <literal><function>sqrt(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>square root</entry>

       <entry><literal>sqrt(2.0)</literal></entry>

       <entry><literal>1.4142135623731</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>trunc</primary>

        </indexterm>

        <literal><function>trunc(<type>dp</type> or <type>numeric</type>)</function></literal>

       </entry>

       <entry>(same as input)</entry>

       <entry>truncate toward zero</entry>

       <entry><literal>trunc(42.8)</literal></entry>

       <entry><literal>42</literal></entry>

      </row>

      <row>

       <entry><literal><function>trunc(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>int</type>)</function></literal></entry>

       <entry><type>numeric</type></entry>

       <entry>truncate to <parameter>s</parameter> decimal places</entry>

       <entry><literal>trunc(42.4382, 2)</literal></entry>

       <entry><literal>42.43</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>width_bucket</primary>

        </indexterm>

        <literal><function>width_bucket(<parameter>op</parameter> <type>numeric</type>, <parameter>b1</parameter> <type>numeric</type>, <parameter>b2</parameter> <type>numeric</type>, <parameter>count</parameter> <type>int</type>)</function></literal>

       </entry>

       <entry><type>int</type></entry>

       <entry>return the bucket to which <parameter>operand</> would

       be assigned in an equidepth histogram with <parameter>count</>

       buckets, in the range <parameter>b1</> to <parameter>b2</></entry>

       <entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>

       <entry><literal>3</literal></entry>

      </row>

      <row>

       <entry><literal><function>width_bucket(<parameter>op</parameter> <type>dp</type>, <parameter>b1</parameter> <type>dp</type>, <parameter>b2</parameter> <type>dp</type>, <parameter>count</parameter> <type>int</type>)</function></literal></entry>

       <entry><type>int</type></entry>

       <entry>return the bucket to which <parameter>operand</> would

       be assigned in an equidepth histogram with <parameter>count</>

       buckets, in the range <parameter>b1</> to <parameter>b2</></entry>

       <entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>

       <entry><literal>3</literal></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="3">

     <thead>

      <row>

       <entry>Function</entry>

       <entry>Return Type</entry>

       <entry>Description</entry>

      </row>

     </thead>

     <tbody>

      <row>

       <entry>

        <indexterm>

         <primary>random</primary>

        </indexterm>

        <literal><function>random()</function></literal>

       </entry>

       <entry><type>dp</type></entry>

       <entry>random value in the range 0.0 &lt;= x &lt; 1.0</entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>setseed</primary>

        </indexterm>

        <literal><function>setseed(<type>dp</type>)</function></literal>

       </entry>

       <entry><type>void</type></entry>

       <entry>set seed for subsequent <literal>random()</literal> calls (value between -1.0 and

       1.0, inclusive)</entry>

      </row>

     </tbody>

    </tgroup>

   </table>

  <para>

   The characteristics of the values returned by

   <literal><function>random()</function></literal> depend

   on the system implementation. It is not suitable for cryptographic

   applications; see <xref linkend="pgcrypto"> module for an alternative.

   </para>

  <para>

   Finally, <xref linkend="functions-math-trig-table"> shows the

   available trigonometric functions.  All trigonometric functions

   take arguments and return values of type <type>double

   precision</type>. Trigonometric functions arguments are expressed

   in radians. Inverse functions return values are expressed in

   radians.  See unit transformation functions

   <literal><function>radians()</function></literal> and

   <literal><function>degrees()</function></literal> above.

  </para>

   <table id="functions-math-trig-table">

    <title>Trigonometric Functions</title>

    <tgroup cols="2">

     <thead>

      <row>

       <entry>Function</entry>

       <entry>Description</entry>

      </row>

     </thead>

     <tbody>

      <row>

       <entry>

        <indexterm>

         <primary>acos</primary>

        </indexterm><literal><function>acos(<replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>inverse cosine</entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>asin</primary>

        </indexterm>

        <literal><function>asin(<replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>inverse sine</entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>atan</primary>

        </indexterm>

        <literal><function>atan(<replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>inverse tangent</entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>atan2</primary>

        </indexterm>

        <literal><function>atan2(<replaceable>y</replaceable>,

        <replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>inverse tangent of

        <literal><replaceable>y</replaceable>/<replaceable>x</replaceable></literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>cos</primary>

        </indexterm>

        <literal><function>cos(<replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>cosine</entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>cot</primary>

        </indexterm>

        <literal><function>cot(<replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>cotangent</entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>sin</primary>

        </indexterm>

        <literal><function>sin(<replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>sine</entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>tan</primary>

        </indexterm>

        <literal><function>tan(<replaceable>x</replaceable>)</function></literal>

       </entry>

       <entry>tangent</entry>

      </row>

     </tbody>

    </tgroup>

   </table>

  </sect1>

  <sect1 id="functions-string">

   <title>String Functions and Operators</title>

   <para>

    This section describes functions and operators for examining and

    manipulating string values.  Strings in this context include values

    of the types <type>character</type>, <type>character varying</type>,

    and <type>text</type>.  Unless otherwise noted, all

    of the functions listed below work on all of these types, but be

    wary of potential effects of automatic space-padding when using the

    <type>character</type> type.  Some functions also exist

    natively for the bit-string types.

   </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</> also provides versions of these functions

    that use the regular function invocation syntax

    (see <xref linkend="functions-string-other">).

   </para>

   <note>

    <para>

     Before <productname>PostgreSQL</productname> 8.3, these functions would

     silently accept values of several non-string data types as well, due to

     the presence of implicit coercions from those data types to

     <type>text</>.  Those coercions have been removed because they frequently

     caused surprising behaviors.  However, the string concatenation operator

     (<literal>||</>) still accepts non-string input, so long as at least one

     input is of a string type, as shown in <xref

     linkend="functions-string-sql">.  For other cases, insert an explicit

     coercion to <type>text</> if you need to duplicate the previous behavior.

    </para>

   </note>

   <table id="functions-string-sql">

    <title><acronym>SQL</acronym> String Functions and Operators</title>

    <tgroup cols="5">

     <thead>

      <row>

       <entry>Function</entry>

       <entry>Return Type</entry>

       <entry>Description</entry>

       <entry>Example</entry>

       <entry>Result</entry>

      </row>

     </thead>

     <tbody>

      <row>

       <entry><literal><parameter>string</parameter> <literal>||</literal>

        <parameter>string</parameter></literal></entry>

       <entry> <type>text</type> </entry>

       <entry>

        String concatenation

        <indexterm>

         <primary>character string</primary>

         <secondary>concatenation</secondary>

        </indexterm>

       </entry>

       <entry><literal>'Post' || 'greSQL'</literal></entry>

       <entry><literal>PostgreSQL</literal></entry>

      </row>

      <row>

       <entry>

        <literal><parameter>string</parameter> <literal>||</literal>

        <parameter>non-string</parameter></literal>

        or

        <literal><parameter>non-string</parameter> <literal>||</literal>

        <parameter>string</parameter></literal>

       </entry>

       <entry> <type>text</type> </entry>

       <entry>

        String concatenation with one non-string input

       </entry>

       <entry><literal>'Value: ' || 42</literal></entry>

       <entry><literal>Value: 42</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>bit_length</primary>

        </indexterm>

        <literal><function>bit_length(<parameter>string</parameter>)</function></literal>

       </entry>

       <entry><type>int</type></entry>

       <entry>Number of bits in string</entry>

       <entry><literal>bit_length('jose')</literal></entry>

       <entry><literal>32</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>char_length</primary>

        </indexterm>

        <literal><function>char_length(<parameter>string</parameter>)</function></literal> or <literal><function>character_length(<parameter>string</parameter>)</function></literal>

       </entry>

       <entry><type>int</type></entry>

       <entry>

        Number of characters in string

        <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>

       </entry>

       <entry><literal>char_length('jose')</literal></entry>

       <entry><literal>4</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>lower</primary>

        </indexterm>

        <literal><function>lower(<parameter>string</parameter>)</function></literal>

       </entry>

       <entry><type>text</type></entry>

       <entry>Convert string to lower case</entry>

       <entry><literal>lower('TOM')</literal></entry>

       <entry><literal>tom</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>octet_length</primary>

        </indexterm>

        <literal><function>octet_length(<parameter>string</parameter>)</function></literal>

       </entry>

       <entry><type>int</type></entry>

       <entry>Number of bytes in string</entry>

       <entry><literal>octet_length('jose')</literal></entry>

       <entry><literal>4</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>overlay</primary>

        </indexterm>

        <literal><function>overlay(<parameter>string</parameter> placing <parameter>string</parameter> from <type>int</type> <optional>for <type>int</type></optional>)</function></literal>

       </entry>

       <entry><type>text</type></entry>

       <entry>

        Replace substring

       </entry>

       <entry><literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal></entry>

       <entry><literal>Thomas</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>position</primary>

        </indexterm>

        <literal><function>position(<parameter>substring</parameter> in <parameter>string</parameter>)</function></literal>

       </entry>

       <entry><type>int</type></entry>

       <entry>Location of specified substring</entry>

       <entry><literal>position('om' in 'Thomas')</literal></entry>

       <entry><literal>3</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>substring</primary>

        </indexterm>

        <literal><function>substring(<parameter>string</parameter> <optional>from <type>int</type></optional> <optional>for <type>int</type></optional>)</function></literal>

       </entry>

       <entry><type>text</type></entry>

       <entry>

        Extract substring

       </entry>

       <entry><literal>substring('Thomas' from 2 for 3)</literal></entry>

       <entry><literal>hom</literal></entry>

      </row>

      <row>

       <entry><literal><function>substring(<parameter>string</parameter> from <replaceable>pattern</replaceable>)</function></literal></entry>

       <entry><type>text</type></entry>

       <entry>

        Extract substring matching POSIX regular expression. See

        <xref linkend="functions-matching"> for more information on pattern

        matching.

       </entry>

       <entry><literal>substring('Thomas' from '...$')</literal></entry>

       <entry><literal>mas</literal></entry>

      </row>

      <row>

       <entry><literal><function>substring(<parameter>string</parameter> from <replaceable>pattern</replaceable> for <replaceable>escape</replaceable>)</function></literal></entry>

       <entry><type>text</type></entry>

       <entry>

        Extract substring matching <acronym>SQL</acronym> regular expression.

        See <xref linkend="functions-matching"> for more information on

        pattern matching.

       </entry>

       <entry><literal>substring('Thomas' from '%#"o_a#"_' for '#')</literal></entry>

       <entry><literal>oma</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>trim</primary>

        </indexterm>

        <literal><function>trim(<optional>leading | trailing | both</optional>

        <optional><parameter>characters</parameter></optional> from

        <parameter>string</parameter>)</function></literal>

       </entry>

       <entry><type>text</type></entry>

       <entry>

        Remove the longest string containing only the

        <parameter>characters</parameter> (a space by default) from the

        start/end/both ends of the <parameter>string</parameter>

       </entry>

       <entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>

       <entry><literal>Tom</literal></entry>

      </row>

      <row>

       <entry>

        <indexterm>

         <primary>upper</primary>

        </indexterm>