diff options
Diffstat (limited to 'doc/src/sgml/func/func-math.sgml')
| -rw-r--r-- | doc/src/sgml/func/func-math.sgml | 1615 |
1 files changed, 1615 insertions, 0 deletions
diff --git a/doc/src/sgml/func/func-math.sgml b/doc/src/sgml/func/func-math.sgml new file mode 100644 index 00000000000..7528dc4cea4 --- /dev/null +++ b/doc/src/sgml/func/func-math.sgml @@ -0,0 +1,1615 @@ + <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>&</literal> <replaceable>integral_type</replaceable> + <returnvalue><replaceable>integral_type</replaceable></returnvalue> + </para> + <para> + Bitwise AND + </para> + <para> + <literal>91 & 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><<</literal> <type>integer</type> + <returnvalue><replaceable>integral_type</replaceable></returnvalue> + </para> + <para> + Bitwise shift left + </para> + <para> + <literal>1 << 4</literal> + <returnvalue>16</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <replaceable>integral_type</replaceable> <literal>>></literal> <type>integer</type> + <returnvalue><replaceable>integral_type</replaceable></returnvalue> + </para> + <para> + Bitwise shift right + </para> + <para> + <literal>8 >> 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>erf</primary> + </indexterm> + <function>erf</function> ( <type>double precision</type> ) + <returnvalue>double precision</returnvalue> + </para> + <para> + Error function + </para> + <para> + <literal>erf(1.0)</literal> + <returnvalue>0.8427007929497149</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>erfc</primary> + </indexterm> + <function>erfc</function> ( <type>double precision</type> ) + <returnvalue>double precision</returnvalue> + </para> + <para> + Complementary error function (<literal>1 - erf(x)</literal>, without + loss of precision for large inputs) + </para> + <para> + <literal>erfc(1.0)</literal> + <returnvalue>0.15729920705028513</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>gamma</primary> + </indexterm> + <function>gamma</function> ( <type>double precision</type> ) + <returnvalue>double precision</returnvalue> + </para> + <para> + Gamma function + </para> + <para> + <literal>gamma(0.5)</literal> + <returnvalue>1.772453850905516</returnvalue> + </para> + <para> + <literal>gamma(6)</literal> + <returnvalue>120</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>lgamma</primary> + </indexterm> + <function>lgamma</function> ( <type>double precision</type> ) + <returnvalue>double precision</returnvalue> + </para> + <para> + Natural logarithm of the absolute value of the gamma function + </para> + <para> + <literal>lgamma(1000)</literal> + <returnvalue>5905.220423209181</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">π</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>. + The buckets have inclusive lower bounds and exclusive upper bounds. + Returns <literal>0</literal> for an input less + than <parameter>low</parameter>, + or <literal><parameter>count</parameter>+1</literal> for an input + greater than or equal to <parameter>high</parameter>. + If <parameter>low</parameter> > <parameter>high</parameter>, + the behavior is mirror-reversed, with bucket <literal>1</literal> + now being the one just below <parameter>low</parameter>, and the + inclusive bounds now being on the upper side. + </para> + <para> + <literal>width_bucket(5.35, 0.024, 10.06, 5)</literal> + <returnvalue>3</returnvalue> + </para> + <para> + <literal>width_bucket(9, 10, 0, 10)</literal> + <returnvalue>2</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 + inclusive 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 <= x < 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>random</primary> + </indexterm> + <function>random</function> ( <parameter>min</parameter> <type>integer</type>, <parameter>max</parameter> <type>integer</type> ) + <returnvalue>integer</returnvalue> + </para> + <para role="func_signature"> + <function>random</function> ( <parameter>min</parameter> <type>bigint</type>, <parameter>max</parameter> <type>bigint</type> ) + <returnvalue>bigint</returnvalue> + </para> + <para role="func_signature"> + <function>random</function> ( <parameter>min</parameter> <type>numeric</type>, <parameter>max</parameter> <type>numeric</type> ) + <returnvalue>numeric</returnvalue> + </para> + <para> + Returns a random value in the range + <parameter>min</parameter> <= x <= <parameter>max</parameter>. + For type <type>numeric</type>, the result will have the same number of + fractional decimal digits as <parameter>min</parameter> or + <parameter>max</parameter>, whichever has more. + </para> + <para> + <literal>random(1, 10)</literal> + <returnvalue>7</returnvalue> + </para> + <para> + <literal>random(-0.499, 0.499)</literal> + <returnvalue>0.347</returnvalue> + </para></entry> + </row> + + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>random_normal</primary> + </indexterm> + + <function>random_normal</function> ( + <optional> <parameter>mean</parameter> <type>double precision</type> + <optional>, <parameter>stddev</parameter> <type>double precision</type> </optional></optional> ) + <returnvalue>double precision</returnvalue> + </para> + <para> + Returns a random value from the normal distribution with the given + parameters; <parameter>mean</parameter> defaults to 0.0 + and <parameter>stddev</parameter> defaults to 1.0 + </para> + <para> + <literal>random_normal(0.0, 1.0)</literal> + <returnvalue>0.051285419</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> and + <literal>random_normal()</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> and <function>random_normal()</function> + functions listed in <xref linkend="functions-math-random-table"/> use 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 calls to these functions 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 call to any of these functions 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> |