PostgreSQL 9.4.1 Documentation | |||
---|---|---|---|

Prev | Up | Chapter 9. Functions and Operators | Next |

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

Table 9-2 shows the available mathematical operators.

Table 9-2. Mathematical Operators

Operator | Description | Example | Result |
---|---|---|---|

+ |
addition | 2 + 3 |
5 |

- |
subtraction | 2 - 3 |
-1 |

* |
multiplication | 2 * 3 |
6 |

/ |
division (integer division truncates the result) | 4 / 2 |
2 |

% |
modulo (remainder) | 5 % 4 |
1 |

^ |
exponentiation | 2.0 ^ 3.0 |
8 |

|/ |
square root | |/ 25.0 |
5 |

||/ |
cube root | ||/ 27.0 |
3 |

! |
factorial | 5 ! |
120 |

!! |
factorial (prefix operator) | !! 5 |
120 |

@ |
absolute value | @ -5.0 |
5 |

& |
bitwise AND | 91 & 15 |
11 |

| |
bitwise OR | 32 | 3 |
35 |

# |
bitwise XOR | 17 # 5 |
20 |

~ |
bitwise NOT | ~1 |
-2 |

<< |
bitwise shift left | 1 << 4 |
16 |

>> |
bitwise shift right | 8 >> 2 |
2 |

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
`bit` and `bit varying`,
as shown in Table
9-11.

Table
9-3 shows the available mathematical functions. In the table,
`dp` indicates `double
precision`. 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 `double
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-3. Mathematical Functions

Function | Return Type | Description | Example | Result |
---|---|---|---|---|

`abs(` |
(same as input) | absolute value | abs(-17.4) |
17.4 |

`cbrt(` |
dp |
cube root | cbrt(27.0) |
3 |

`ceil(` |
(same as input) | smallest integer not less than argument | ceil(-42.8) |
-42 |

`ceiling(` |
(same as input) | smallest integer not less than argument (alias for
`ceil` ) |
ceiling(-95.3) |
-95 |

`degrees(` |
dp |
radians to degrees | degrees(0.5) |
28.6478897565412 |

`div(` |
numeric |
integer quotient of y/x |
div(9,4) |
2 |

`exp(` |
(same as input) | exponential | exp(1.0) |
2.71828182845905 |

`floor(` |
(same as input) | largest integer not greater than argument | floor(-42.8) |
-43 |

`ln(` |
(same as input) | natural logarithm | ln(2.0) |
0.693147180559945 |

`log(` |
(same as input) | base 10 logarithm | log(100.0) |
2 |

`log(` |
numeric |
logarithm to base b |
log(2.0, 64.0) |
6.0000000000 |

`mod(` |
(same as argument types) | remainder of y/x |
mod(9,4) |
1 |

`pi()` |
dp |
"π" constant | pi() |
3.14159265358979 |

`power(` |
dp |
a raised to the power of
b |
power(9.0, 3.0) |
729 |

`power(` |
numeric |
a raised to the power of
b |
power(9.0, 3.0) |
729 |

`radians(` |
dp |
degrees to radians | radians(45.0) |
0.785398163397448 |

`round(` |
(same as input) | round to nearest integer | round(42.4) |
42 |

`round(` |
numeric |
round to s decimal
places |
round(42.4382, 2) |
42.44 |

`sign(` |
(same as input) | sign of the argument (-1, 0, +1) | sign(-8.4) |
-1 |

`sqrt(` |
(same as input) | square root | sqrt(2.0) |
1.4142135623731 |

`trunc(` |
(same as input) | truncate toward zero | trunc(42.8) |
42 |

`trunc(` |
numeric |
truncate to s decimal
places |
trunc(42.4382, 2) |
42.43 |

`width_bucket(` |
int |
return the bucket to which operand would be assigned in an
equidepth histogram with count
buckets, in the range b1 to
b2 |
width_bucket(5.35, 0.024, 10.06,
5) |
3 |

`width_bucket(` |
int |
return the bucket to which operand would be assigned in an
equidepth histogram with count
buckets, in the range b1 to
b2 |
width_bucket(5.35, 0.024, 10.06,
5) |
3 |

Table 9-4 shows functions for generating random numbers.

Table 9-4. Random Functions

Function | Return Type | Description |
---|---|---|

`random()` |
dp |
random value in the range 0.0 <= x < 1.0 |

`setseed(` |
void |
set seed for subsequent random() calls (value between -1.0 and
1.0, inclusive) |

The characteristics of the values returned by ` random()` depend on
the system implementation. It is not suitable for cryptographic
applications; see pgcrypto module for
an alternative.

Finally, Table 9-5
shows the available trigonometric functions. All trigonometric
functions take arguments and return values of type `double precision`. Trigonometric functions arguments
are expressed in radians. Inverse functions return values are
expressed in radians. See unit transformation functions
` radians()`
and

`degrees()`