Movatterモバイル変換

9.3. Mathematical Functions and Operators
Prev	Up	Chapter 9. Functions and Operators	Home	Next

9.3. Mathematical Functions and Operators#

Mathematical operators are provided for manyPostgres Pro types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.

Table 9.4 shows the mathematical operators that are available for the standard numeric types. Unless otherwise noted, operators shown as acceptingnumeric_type are available for all the typessmallint,integer,bigint,numeric,real, anddouble precision. Operators shown as acceptingintegral_type are available for the typessmallint,integer, andbigint. Except where noted, each form of an operator returns the same data type as its argument(s). Calls involving multiple argument data types, such asinteger+numeric, are resolved by using the type appearing later in these lists.

Table 9.4. Mathematical Operators

Operator Description Example(s)
`numeric_type``+``numeric_type` →`numeric_type` Addition `2 + 3` →`5`
`+``numeric_type` →`numeric_type` Unary plus (no operation) `+ 3.5` →`3.5`
`numeric_type``-``numeric_type` →`numeric_type` Subtraction `2 - 3` →`-1`
`-``numeric_type` →`numeric_type` Negation `- (-4)` →`4`
`numeric_type````numeric_type`* →`numeric_type` Multiplication `2 * 3` →`6`
`numeric_type``/``numeric_type` →`numeric_type` Division (for integral types, division truncates the result towards zero) `5.0 / 2` →`2.5000000000000000` `5 / 2` →`2` `(-5) / 2` →`-2`
`numeric_type``%``numeric_type` →`numeric_type` Modulo (remainder); available for`smallint`,`integer`,`bigint`, and`numeric` `5 % 4` →`1`
`numeric^numeric` →`numeric` `double precision^double precision` →`double precision` Exponentiation `2 ^ 3` →`8` Unlike typical mathematical practice, multiple uses of`^` will associate left to right by default: `2 ^ 3 ^ 3` →`512` `2 ^ (3 ^ 3)` →`134217728`
`\|/double precision` →`double precision` Square root `\|/ 25.0` →`5`
`\|\|/double precision` →`double precision` Cube root `\|\|/ 64.0` →`4`
`@``numeric_type` →`numeric_type` Absolute value `@ -5.0` →`5.0`
`integral_type``&``integral_type` →`integral_type` Bitwise AND `91 & 15` →`11`
`integral_type``\|``integral_type` →`integral_type` Bitwise OR `32 \| 3` →`35`
`integral_type``#``integral_type` →`integral_type` Bitwise exclusive OR `17 # 5` →`20`
`~``integral_type` →`integral_type` Bitwise NOT `~1` →`-2`
`integral_type``<<integer` →`integral_type` Bitwise shift left `1 << 4` →`16`
`integral_type``>>integer` →`integral_type` Bitwise shift right `8 >> 2` →`2`

Operator

Description

Example(s)

numeric_type+numeric_type →numeric_type

Addition

2 + 3 →5

+numeric_type →numeric_type

Unary plus (no operation)

+ 3.5 →3.5

numeric_type-numeric_type →numeric_type

Subtraction

2 - 3 →-1

-numeric_type →numeric_type

Negation

- (-4) →4

numeric_type*numeric_type →numeric_type

Multiplication

2 * 3 →6

numeric_type/numeric_type →numeric_type

Division (for integral types, division truncates the result towards zero)

5.0 / 2 →2.5000000000000000

5 / 2 →2

(-5) / 2 →-2

numeric_type%numeric_type →numeric_type

Modulo (remainder); available forsmallint,integer,bigint, andnumeric

5 % 4 →1

numeric^numeric →numeric

double precision^double precision →double precision

Exponentiation

2 ^ 3 →8

Unlike typical mathematical practice, multiple uses of^ will associate left to right by default:

2 ^ 3 ^ 3 →512

2 ^ (3 ^ 3) →134217728

|/double precision →double precision

Square root

|/ 25.0 →5

||/double precision →double precision

Cube root

||/ 64.0 →4

@numeric_type →numeric_type

Absolute value

@ -5.0 →5.0

integral_type&integral_type →integral_type

Bitwise AND

91 & 15 →11

integral_type|integral_type →integral_type

Bitwise OR

32 | 3 →35

integral_type#integral_type →integral_type

Bitwise exclusive OR

17 # 5 →20

~integral_type →integral_type

Bitwise NOT

~1 →-2

integral_type<<integer →integral_type

Bitwise shift left

1 << 4 →16

integral_type>>integer →integral_type

Bitwise shift right

8 >> 2 →2

Table 9.5 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 withdouble precision 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.

Table 9.5. Mathematical Functions

Function Description Example(s)
`abs` (`numeric_type` ) →`numeric_type` Absolute value `abs(-17.4)` →`17.4`
`cbrt` (`double precision` ) →`double precision` Cube root `cbrt(64.0)` →`4`
`ceil` (`numeric` ) →`numeric` `ceil` (`double precision` ) →`double precision` Nearest integer greater than or equal to argument `ceil(42.2)` →`43` `ceil(-42.8)` →`-42`
`ceiling` (`numeric` ) →`numeric` `ceiling` (`double precision` ) →`double precision` Nearest integer greater than or equal to argument (same as`ceil`) `ceiling(95.3)` →`96`
`degrees` (`double precision` ) →`double precision` Converts radians to degrees `degrees(0.5)` →`28.64788975654116`
`div` (`y``numeric`,`x``numeric` ) →`numeric` Integer quotient of`y`/`x` (truncates towards zero) `div(9, 4)` →`2`
`erf` (`double precision` ) →`double precision` Error function `erf(1.0)` →`0.8427007929497149`
`erfc` (`double precision` ) →`double precision` Complementary error function (`1 - erf(x)`, without loss of precision for large inputs) `erfc(1.0)` →`0.15729920705028513`
`exp` (`numeric` ) →`numeric` `exp` (`double precision` ) →`double precision` Exponential (`e` raised to the given power) `exp(1.0)` →`2.7182818284590452`
`factorial` (`bigint` ) →`numeric` Factorial `factorial(5)` →`120`
`floor` (`numeric` ) →`numeric` `floor` (`double precision` ) →`double precision` Nearest integer less than or equal to argument `floor(42.8)` →`42` `floor(-42.8)` →`-43`
`gcd` (`numeric_type`,`numeric_type` ) →`numeric_type` Greatest common divisor (the largest positive number that divides both inputs with no remainder); returns`0` if both inputs are zero; available for`integer`,`bigint`, and`numeric` `gcd(1071, 462)` →`21`
`lcm` (`numeric_type`,`numeric_type` ) →`numeric_type` Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns`0` if either input is zero; available for`integer`,`bigint`, and`numeric` `lcm(1071, 462)` →`23562`
`ln` (`numeric` ) →`numeric` `ln` (`double precision` ) →`double precision` Natural logarithm `ln(2.0)` →`0.6931471805599453`
`log` (`numeric` ) →`numeric` `log` (`double precision` ) →`double precision` Base 10 logarithm `log(100)` →`2`
`log10` (`numeric` ) →`numeric` `log10` (`double precision` ) →`double precision` Base 10 logarithm (same as`log`) `log10(1000)` →`3`
`log` (`b``numeric`,`x``numeric` ) →`numeric` Logarithm of`x` to base`b` `log(2.0, 64.0)` →`6.0000000000000000`
`min_scale` (`numeric` ) →`integer` Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely `min_scale(8.4100)` →`2`
`mod` (`y``numeric_type`,`x``numeric_type` ) →`numeric_type` Remainder of`y`/`x`; available for`smallint`,`integer`,`bigint`, and`numeric` `mod(9, 4)` →`1`
`pi` ( ) →`double precision` Approximate value ofπ `pi()` →`3.141592653589793`
`power` (`a``numeric`,`b``numeric` ) →`numeric` `power` (`a``double precision`,`b``double precision` ) →`double precision` `a` raised to the power of`b` `power(9, 3)` →`729`
`radians` (`double precision` ) →`double precision` Converts degrees to radians `radians(45.0)` →`0.7853981633974483`
`round` (`numeric` ) →`numeric` `round` (`double precision` ) →`double precision` Rounds to nearest integer. For`numeric`, ties are broken by rounding away from zero. For`double precision`, the tie-breaking behavior is platform dependent, but“round to nearest even” is the most common rule. `round(42.4)` →`42`
`round` (`v``numeric`,`s``integer` ) →`numeric` Rounds`v` to`s` decimal places. Ties are broken by rounding away from zero. `round(42.4382, 2)` →`42.44` `round(1234.56, -1)` →`1230`
`scale` (`numeric` ) →`integer` Scale of the argument (the number of decimal digits in the fractional part) `scale(8.4100)` →`4`
`sign` (`numeric` ) →`numeric` `sign` (`double precision` ) →`double precision` Sign of the argument (-1, 0, or +1) `sign(-8.4)` →`-1`
`sqrt` (`numeric` ) →`numeric` `sqrt` (`double precision` ) →`double precision` Square root `sqrt(2)` →`1.4142135623730951`
`trim_scale` (`numeric` ) →`numeric` Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes `trim_scale(8.4100)` →`8.41`
`trunc` (`numeric` ) →`numeric` `trunc` (`double precision` ) →`double precision` Truncates to integer (towards zero) `trunc(42.8)` →`42` `trunc(-42.8)` →`-42`
`trunc` (`v``numeric`,`s``integer` ) →`numeric` Truncates`v` to`s` decimal places `trunc(42.4382, 2)` →`42.43`
`width_bucket` (`operand``numeric`,`low``numeric`,`high``numeric`,`count``integer` ) →`integer` `width_bucket` (`operand``double precision`,`low``double precision`,`high``double precision`,`count``integer` ) →`integer` Returns the number of the bucket in which`operand` falls in a histogram having`count` equal-width buckets spanning the range`low` to`high`. Returns`0` or`count+1` for an input outside that range. `width_bucket(5.35, 0.024, 10.06, 5)` →`3`
`width_bucket` (`operand``anycompatible`,`thresholds``anycompatiblearray` ) →`integer` Returns the number of the bucket in which`operand` falls given an array listing the lower bounds of the buckets. Returns`0` for an input less than the first lower bound.`operand` and the array elements can be of any type having standard comparison operators. The`thresholds` arraymust be sorted, smallest first, or unexpected results will be obtained. `width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])` →`2`

Function

Description

Example(s)

abs (numeric_type ) →numeric_type

Absolute value

abs(-17.4) →17.4

cbrt (double precision ) →double precision

Cube root

cbrt(64.0) →4

ceil (numeric ) →numeric

ceil (double precision ) →double precision

Nearest integer greater than or equal to argument

ceil(42.2) →43

ceil(-42.8) →-42

ceiling (numeric ) →numeric

ceiling (double precision ) →double precision

Nearest integer greater than or equal to argument (same asceil)

ceiling(95.3) →96

degrees (double precision ) →double precision

Converts radians to degrees

degrees(0.5) →28.64788975654116

div (ynumeric,xnumeric ) →numeric

Integer quotient ofy/x (truncates towards zero)

div(9, 4) →2

erf (double precision ) →double precision

Error function

erf(1.0) →0.8427007929497149

erfc (double precision ) →double precision

Complementary error function (1 - erf(x), without loss of precision for large inputs)

erfc(1.0) →0.15729920705028513

exp (numeric ) →numeric

exp (double precision ) →double precision

Exponential (e raised to the given power)

exp(1.0) →2.7182818284590452

factorial (bigint ) →numeric

Factorial

factorial(5) →120

floor (numeric ) →numeric

floor (double precision ) →double precision

Nearest integer less than or equal to argument

floor(42.8) →42

floor(-42.8) →-43

gcd (numeric_type,numeric_type ) →numeric_type

Greatest common divisor (the largest positive number that divides both inputs with no remainder); returns0 if both inputs are zero; available forinteger,bigint, andnumeric

gcd(1071, 462) →21

lcm (numeric_type,numeric_type ) →numeric_type

Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns0 if either input is zero; available forinteger,bigint, andnumeric

lcm(1071, 462) →23562

ln (numeric ) →numeric

ln (double precision ) →double precision

Natural logarithm

ln(2.0) →0.6931471805599453

log (numeric ) →numeric

log (double precision ) →double precision

Base 10 logarithm

log(100) →2

log10 (numeric ) →numeric

log10 (double precision ) →double precision

Base 10 logarithm (same aslog)

log10(1000) →3

log (bnumeric,xnumeric ) →numeric

Logarithm ofx to baseb

log(2.0, 64.0) →6.0000000000000000

min_scale (numeric ) →integer

Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely

min_scale(8.4100) →2

mod (ynumeric_type,xnumeric_type ) →numeric_type

Remainder ofy/x; available forsmallint,integer,bigint, andnumeric

mod(9, 4) →1

pi ( ) →double precision

Approximate value ofπ

pi() →3.141592653589793

power (anumeric,bnumeric ) →numeric

power (adouble precision,bdouble precision ) →double precision

a raised to the power ofb

power(9, 3) →729

radians (double precision ) →double precision

Converts degrees to radians

radians(45.0) →0.7853981633974483

round (numeric ) →numeric

round (double precision ) →double precision

Rounds to nearest integer. Fornumeric, ties are broken by rounding away from zero. Fordouble precision, the tie-breaking behavior is platform dependent, but“round to nearest even” is the most common rule.

round(42.4) →42

round (vnumeric,sinteger ) →numeric

Roundsv tos decimal places. Ties are broken by rounding away from zero.

round(42.4382, 2) →42.44

round(1234.56, -1) →1230

scale (numeric ) →integer

Scale of the argument (the number of decimal digits in the fractional part)

scale(8.4100) →4

sign (numeric ) →numeric

sign (double precision ) →double precision

Sign of the argument (-1, 0, or +1)

sign(-8.4) →-1

sqrt (numeric ) →numeric

sqrt (double precision ) →double precision

Square root

sqrt(2) →1.4142135623730951

trim_scale (numeric ) →numeric

Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes

trim_scale(8.4100) →8.41

trunc (numeric ) →numeric

trunc (double precision ) →double precision

Truncates to integer (towards zero)

trunc(42.8) →42

trunc(-42.8) →-42

trunc (vnumeric,sinteger ) →numeric

Truncatesv tos decimal places

trunc(42.4382, 2) →42.43

width_bucket (operandnumeric,lownumeric,highnumeric,countinteger ) →integer

width_bucket (operanddouble precision,lowdouble precision,highdouble precision,countinteger ) →integer

Returns the number of the bucket in whichoperand falls in a histogram havingcount equal-width buckets spanning the rangelow tohigh. Returns0 orcount+1 for an input outside that range.

width_bucket(5.35, 0.024, 10.06, 5) →3

width_bucket (operandanycompatible,thresholdsanycompatiblearray ) →integer

Returns the number of the bucket in whichoperand falls given an array listing the lower bounds of the buckets. Returns0 for an input less than the first lower bound.operand and the array elements can be of any type having standard comparison operators. Thethresholds arraymust be sorted, smallest first, or unexpected results will be obtained.

width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[]) →2

Table 9.6 shows functions for generating random numbers.

Table 9.6. Random Functions

Function Description Example(s)
`random` ( ) →`double precision` Returns a random value in the range 0.0 <= x < 1.0 `random()` →`0.897124072839091`
`random` (`min``integer`,`max``integer` ) →`integer` `random` (`min``bigint`,`max``bigint` ) →`bigint` `random` (`min``numeric`,`max``numeric` ) →`numeric` Returns a random value in the range`min` <= x <=`max`. For type`numeric`, the result will have the same number of fractional decimal digits as`min` or`max`, whichever has more. `random(1, 10)` →`7` `random(-0.499, 0.499)` →`0.347`
`random_normal` ( [`mean``double precision` [,`stddev``double precision`]] ) →`double precision` Returns a random value from the normal distribution with the given parameters;`mean` defaults to 0.0 and`stddev` defaults to 1.0 `random_normal(0.0, 1.0)` →`0.051285419`
`setseed` (`double precision` ) →`void` Sets the seed for subsequent`random()` and`random_normal()` calls; argument must be between -1.0 and 1.0, inclusive `setseed(0.12345)`

Function

Description

Example(s)

random ( ) →double precision

Returns a random value in the range 0.0 <= x < 1.0

random() →0.897124072839091

random (mininteger,maxinteger ) →integer

random (minbigint,maxbigint ) →bigint

random (minnumeric,maxnumeric ) →numeric

Returns a random value in the rangemin <= x <=max. For typenumeric, the result will have the same number of fractional decimal digits asmin ormax, whichever has more.

random(1, 10) →7

random(-0.499, 0.499) →0.347

random_normal ( [meandouble precision [,stddevdouble precision]] ) →double precision

Returns a random value from the normal distribution with the given parameters;mean defaults to 0.0 andstddev defaults to 1.0

random_normal(0.0, 1.0) →0.051285419

setseed (double precision ) →void

Sets the seed for subsequentrandom() andrandom_normal() calls; argument must be between -1.0 and 1.0, inclusive

setseed(0.12345)

Therandom() andrandom_normal() functions listed inTable 9.6 use a deterministic pseudo-random number generator. It is fast but not suitable for cryptographic applications; see thepgcrypto module for a more secure alternative. Ifsetseed() is called, the series of results of subsequent calls to these functions in the current session can be repeated by re-issuingsetseed() with the same argument. Without any priorsetseed() call in the same session, the first call to any of these functions obtains a seed from a platform-dependent source of random bits.

Table 9.7 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.

Table 9.7. Trigonometric Functions

Function Description Example(s)
`acos` (`double precision` ) →`double precision` Inverse cosine, result in radians `acos(1)` →`0`
`acosd` (`double precision` ) →`double precision` Inverse cosine, result in degrees `acosd(0.5)` →`60`
`asin` (`double precision` ) →`double precision` Inverse sine, result in radians `asin(1)` →`1.5707963267948966`
`asind` (`double precision` ) →`double precision` Inverse sine, result in degrees `asind(0.5)` →`30`
`atan` (`double precision` ) →`double precision` Inverse tangent, result in radians `atan(1)` →`0.7853981633974483`
`atand` (`double precision` ) →`double precision` Inverse tangent, result in degrees `atand(1)` →`45`
`atan2` (`y``double precision`,`x``double precision` ) →`double precision` Inverse tangent of`y`/`x`, result in radians `atan2(1, 0)` →`1.5707963267948966`
`atan2d` (`y``double precision`,`x``double precision` ) →`double precision` Inverse tangent of`y`/`x`, result in degrees `atan2d(1, 0)` →`90`
`cos` (`double precision` ) →`double precision` Cosine, argument in radians `cos(0)` →`1`
`cosd` (`double precision` ) →`double precision` Cosine, argument in degrees `cosd(60)` →`0.5`
`cot` (`double precision` ) →`double precision` Cotangent, argument in radians `cot(0.5)` →`1.830487721712452`
`cotd` (`double precision` ) →`double precision` Cotangent, argument in degrees `cotd(45)` →`1`
`sin` (`double precision` ) →`double precision` Sine, argument in radians `sin(1)` →`0.8414709848078965`
`sind` (`double precision` ) →`double precision` Sine, argument in degrees `sind(30)` →`0.5`
`tan` (`double precision` ) →`double precision` Tangent, argument in radians `tan(1)` →`1.5574077246549023`
`tand` (`double precision` ) →`double precision` Tangent, argument in degrees `tand(45)` →`1`

Function

Description

Example(s)

acos (double precision ) →double precision

Inverse cosine, result in radians

acos(1) →0

acosd (double precision ) →double precision

Inverse cosine, result in degrees

acosd(0.5) →60

asin (double precision ) →double precision

Inverse sine, result in radians

asin(1) →1.5707963267948966

asind (double precision ) →double precision

Inverse sine, result in degrees

asind(0.5) →30

atan (double precision ) →double precision

Inverse tangent, result in radians

atan(1) →0.7853981633974483

atand (double precision ) →double precision

Inverse tangent, result in degrees

atand(1) →45

atan2 (ydouble precision,xdouble precision ) →double precision

Inverse tangent ofy/x, result in radians

atan2(1, 0) →1.5707963267948966

atan2d (ydouble precision,xdouble precision ) →double precision

Inverse tangent ofy/x, result in degrees

atan2d(1, 0) →90

cos (double precision ) →double precision

Cosine, argument in radians

cos(0) →1

cosd (double precision ) →double precision

Cosine, argument in degrees

cosd(60) →0.5

cot (double precision ) →double precision

Cotangent, argument in radians

cot(0.5) →1.830487721712452

cotd (double precision ) →double precision

Cotangent, argument in degrees

cotd(45) →1

sin (double precision ) →double precision

Sine, argument in radians

sin(1) →0.8414709848078965

sind (double precision ) →double precision

Sine, argument in degrees

sind(30) →0.5

tan (double precision ) →double precision

Tangent, argument in radians

tan(1) →1.5574077246549023

tand (double precision ) →double precision

Tangent, argument in degrees

tand(45) →1

Note

Another way to work with angles measured in degrees is to use the unit transformation functionsradians() anddegrees() shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids round-off error for special cases such assind(30).

Table 9.8 shows the available hyperbolic functions.

Table 9.8. Hyperbolic Functions

Function Description Example(s)
`sinh` (`double precision` ) →`double precision` Hyperbolic sine `sinh(1)` →`1.1752011936438014`
`cosh` (`double precision` ) →`double precision` Hyperbolic cosine `cosh(0)` →`1`
`tanh` (`double precision` ) →`double precision` Hyperbolic tangent `tanh(1)` →`0.7615941559557649`
`asinh` (`double precision` ) →`double precision` Inverse hyperbolic sine `asinh(1)` →`0.881373587019543`
`acosh` (`double precision` ) →`double precision` Inverse hyperbolic cosine `acosh(1)` →`0`
`atanh` (`double precision` ) →`double precision` Inverse hyperbolic tangent `atanh(0.5)` →`0.5493061443340548`

Function

Description

Example(s)

sinh (double precision ) →double precision

Hyperbolic sine

sinh(1) →1.1752011936438014

cosh (double precision ) →double precision

Hyperbolic cosine

cosh(0) →1

tanh (double precision ) →double precision

Hyperbolic tangent

tanh(1) →0.7615941559557649

asinh (double precision ) →double precision

Inverse hyperbolic sine

asinh(1) →0.881373587019543

acosh (double precision ) →double precision

Inverse hyperbolic cosine

acosh(1) →0

atanh (double precision ) →double precision

Inverse hyperbolic tangent

atanh(0.5) →0.5493061443340548

Prev	Up	Next
9.2. Comparison Functions and Operators	Home	9.4. String Functions and Operators

pdf epub