<!-- 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</literal></member>
<member><literal>OR</literal></member>
<member><literal>NOT</literal></member>
</simplelist>
<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 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 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> <literal><</literal> </entry>
<entry>less than</entry>
</row>
<row>
<entry> <literal>></literal> </entry>
<entry>greater than</entry>
</row>
<row>
<entry> <literal><=</literal> </entry>
<entry>less than or equal to</entry>
</row>
<row>
<entry> <literal>>=</literal> </entry>
<entry>greater than or equal to</entry>
</row>
<row>
<entry> <literal>=</literal> </entry>
<entry>equal</entry>
</row>
<row>
<entry> <literal><></literal> or <literal>!=</literal> </entry>
<entry>not equal</entry>
</row>
</tbody>
</tgroup>
</table>
<note>
<para>
The <literal>!=</literal> operator is converted to
<literal><></literal> in the parser stage. It is not
possible to implement <literal>!=</literal> and
<literal><></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 < 2 < 3</literal> are not valid (because there is
no <literal><</literal> operator to compare a Boolean value with
<literal>3</literal>).
</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="2">
<thead>
<row>
<entry>Predicate</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry> <replaceable>a</replaceable> <literal>BETWEEN</literal> <replaceable>x</replaceable> <literal>AND</literal> <replaceable>y</replaceable> </entry>
<entry>between</entry>
</row>
<row>
<entry> <replaceable>a</replaceable> <literal>NOT BETWEEN</literal> <replaceable>x</replaceable> <literal>AND</literal> <replaceable>y</replaceable> </entry>
<entry>not between</entry>
</row>
<row>
<entry> <replaceable>a</replaceable> <literal>BETWEEN SYMMETRIC</literal> <replaceable>x</replaceable> <literal>AND</literal> <replaceable>y</replaceable> </entry>
<entry>between, after sorting the comparison values</entry>
</row>
<row>
<entry> <replaceable>a</replaceable> <literal>NOT BETWEEN SYMMETRIC</literal> <replaceable>x</replaceable> <literal>AND</literal> <replaceable>y</replaceable> </entry>
<entry>not between, after sorting the comparison values</entry>
</row>
<row>
<entry> <replaceable>a</replaceable> <literal>IS DISTINCT FROM</literal> <replaceable>b</replaceable> </entry>
<entry>not equal, treating null like an ordinary value</entry>
</row>
<row>
<entry><replaceable>a</replaceable> <literal>IS NOT DISTINCT FROM</literal> <replaceable>b</replaceable></entry>
<entry>equal, treating null like an ordinary value</entry>
</row>
<row>
<entry> <replaceable>expression</replaceable> <literal>IS NULL</literal> </entry>
<entry>is null</entry>
</row>
<row>
<entry> <replaceable>expression</replaceable> <literal>IS NOT NULL</literal> </entry>
<entry>is not null</entry>
</row>
<row>
<entry> <replaceable>expression</replaceable> <literal>ISNULL</literal> </entry>
<entry>is null (nonstandard syntax)</entry>
</row>
<row>
<entry> <replaceable>expression</replaceable> <literal>NOTNULL</literal> </entry>
<entry>is not null (nonstandard syntax)</entry>
</row>
<row>
<entry> <replaceable>boolean_expression</replaceable> <literal>IS TRUE</literal> </entry>
<entry>is true</entry>
</row>
<row>
<entry> <replaceable>boolean_expression</replaceable> <literal>IS NOT TRUE</literal> </entry>
<entry>is false or unknown</entry>
</row>
<row>
<entry> <replaceable>boolean_expression</replaceable> <literal>IS FALSE</literal> </entry>
<entry>is false</entry>
</row>
<row>
<entry> <replaceable>boolean_expression</replaceable> <literal>IS NOT FALSE</literal> </entry>
<entry>is true or unknown</entry>
</row>
<row>
<entry> <replaceable>boolean_expression</replaceable> <literal>IS UNKNOWN</literal> </entry>
<entry>is unknown</entry>
</row>
<row>
<entry> <replaceable>boolean_expression</replaceable> <literal>IS NOT UNKNOWN</literal> </entry>
<entry>is true or false</entry>
</row>
</tbody>
</tgroup>
</table>
<para>
<indexterm>
<primary>BETWEEN</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> >= <replaceable>x</replaceable> AND <replaceable>a</replaceable> <= <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> < <replaceable>x</replaceable> OR <replaceable>a</replaceable> > <replaceable>y</replaceable>
</synopsis>
<indexterm>
<primary>BETWEEN SYMMETRIC</primary>
</indexterm>
<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>
<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 <> 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><></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>
<!-- 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
predicates
<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>
-->
<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="4">
<thead>
<row>
<entry>Function</entry>
<entry>Description</entry>
<entry>Example</entry>
<entry>Example Result</entry>
</row>
</thead>
<tbody>
<row>
<entry>
<indexterm>
<primary>num_nonnulls</primary>
</indexterm>
<literal>num_nonnulls(VARIADIC "any")</literal>
</entry>
<entry>returns the number of non-null arguments</entry>
<entry><literal>num_nonnulls(1, NULL, 2)</literal></entry>
<entry><literal>2</literal></entry>
</row>
<row>
<entry>
<indexterm>
<primary>num_nulls</primary>
</indexterm>
<literal>num_nulls(VARIADIC "any")</literal>
</entry>
<entry>returns the number of null arguments</entry>
<entry><literal>num_nulls(1, NULL, 2)</literal></entry>
<entry><literal>1</literal></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 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>&</literal> </entry>
<entry>bitwise AND</entry>
<entry><literal>91 & 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><<</literal> </entry>
<entry>bitwise shift left</entry>
<entry><literal>1 << 4</literal></entry>
<entry><literal>16</literal></entry>
</row>
<row>
<entry> <literal>>></literal> </entry>
<entry>bitwise shift right</entry>
<entry><literal>8 >> 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>nearest integer greater than or equal to 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>nearest integer greater than or equal to argument (same as <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</type>,
<parameter>x</parameter> <type>numeric</type>)</function></literal>
</entry>
<entry><type>numeric</type></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>nearest integer less than or equal to argument</entry>
<entry><literal>floor(-42.8)</literal></entry>
<entry><literal>-43</literal></entry>
</row>
<row>
<entry>
<indexterm>
<primary>gcd</primary>
</indexterm>
<literal><function>gcd(<replaceable>a</replaceable>, <replaceable>b</replaceable>)</function></literal>
</entry>
<entry>(same as argument types)</entry>
<entry>
greatest common divisor (the largest positive number that divides both
inputs with no remainder); returns <literal>0</literal> if both inputs
are zero
</entry>
<entry><literal>gcd(1071, 462)</literal></entry>
<entry><literal>21</literal></entry>
</row>
<row>
<entry>
<indexterm>
<primary>lcm</primary>
</indexterm>
<literal><function>lcm(<replaceable>a</replaceable>, <replaceable>b</replaceable>)</function></literal>
</entry>
<entry>(same as argument types)</entry>
<entry>
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
</entry>
<entry><literal>lcm(1071, 462)</literal></entry>
<entry><literal>23562</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>
<indexterm>
<primary>log10</primary>
</indexterm>
<literal><function>log10(<type>dp</type> or <type>numeric</type>)</function></literal>
</entry>
<entry>(same as input)</entry>
<entry>base 10 logarithm</entry>
<entry><literal>log10(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>min_scale</primary>
</indexterm>
<literal><function>min_scale(<type>numeric</type>)</function></literal>
</entry>
<entry><type>integer</type></entry>
<entry>minimum scale (number of fractional decimal digits) needed
to represent the supplied value</entry>
<entry><literal>min_scale(8.4100)</literal></entry>
<entry><literal>2</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>π</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</parameter> 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</parameter> 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>scale</primary>
</indexterm>
<literal><function>scale(<type>numeric</type>)</function></literal>
</entry>
<entry><type>integer</type></entry>
<entry>scale of the argument (the number of decimal digits in the fractional part)</entry>
<entry><literal>scale(8.4100)</literal></entry>
<entry><literal>4</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>trim_scale</primary>
</indexterm>
<literal><function>trim_scale(<type>numeric</type>)</function></literal>
</entry>
<entry><type>numeric</type></entry>
<entry>reduce the scale (number of fractional decimal digits) by
removing trailing zeroes</entry>
<entry><literal>trim_scale(8.4100)</literal></entry>
<entry><literal>8.41</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>operand</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 number to which <parameter>operand</parameter> would
be assigned in a histogram having <parameter>count</parameter> equal-width
buckets spanning the range <parameter>b1</parameter> to <parameter>b2</parameter>;
returns <literal>0</literal> or <literal><parameter>count</parameter>+1</literal> for
an input outside the range</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>operand</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 number to which <parameter>operand</parameter> would
be assigned in a histogram having <parameter>count</parameter> equal-width
buckets spanning the range <parameter>b1</parameter> to <parameter>b2</parameter>;
returns <literal>0</literal> or <literal><parameter>count</parameter>+1</literal> for
an input outside the range</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>operand</parameter> <type>anyelement</type>, <parameter>thresholds</parameter> <type>anyarray</type>)</function></literal></entry>
<entry><type>int</type></entry>
<entry>return the bucket number to which <parameter>operand</parameter> would
be assigned given an array listing the lower bounds of the buckets;
returns <literal>0</literal> for an input less than the first lower bound;
the <parameter>thresholds</parameter> array <emphasis>must be sorted</emphasis>,
smallest first, or unexpected results will be obtained</entry>
<entry><literal>width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])</literal></entry>
<entry><literal>2</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 <= x < 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 <function>random()</function> function uses a simple linear
congruential algorithm. 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 results of
subsequent <function>random()</function> calls in the current session are
repeatable by re-issuing <function>setseed()</function> with the same
argument.
</para>
<para>
<xref linkend="functions-math-trig-table"/> shows the
available trigonometric functions. All these functions
take arguments and return values of type <type>double
precision</type>. Each of the trigonometric 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="3">
<thead>
<row>
<entry>Function (radians)</entry>
<entry>Function (degrees)</entry>
<entry>Description</entry>
</row>
</thead>
<tbody>
<row>
<entry>
<indexterm>
<primary>acos</primary>
</indexterm><literal><function>acos(<replaceable>x</replaceable>)
| 