14th November 2019: PostgreSQL 12.1, 11.6, 10.11, 9.6.16, 9.5.20, and 9.4.25 Released!

Development Versions:
devel

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

**Table 9.4. 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 (associates left to right) | `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.14.

Table 9.5 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.5. Mathematical Functions**

Table 9.6 shows functions for generating random numbers.

**Table 9.6. Random Functions**

The `random()`

function uses a simple linear congruential algorithm. It is fast but not suitable for cryptographic applications; see the pgcrypto module for a more secure alternative. If `setseed()`

is called, the results of subsequent `random()`

calls in the current session are repeatable by re-issuing `setseed()`

with the same argument.

Table 9.7 shows the available trigonometric functions. All these functions take arguments and return values of type `double precision`

. Each of the trigonometric functions comes in two variants, one that measures angles in radians and one that measures angles in degrees.

**Table 9.7. Trigonometric Functions**

Another way to work with angles measured in degrees is to use the unit transformation functions

and `radians()`

shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids round-off error for special cases such as `degrees()`

`sind(30)`

.

Table 9.8 shows the available hyperbolic functions. All these functions take arguments and return values of type `double precision`

.

**Table 9.8. Hyperbolic Functions**

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.