PostgreSQL 7.4.7 Documentation | ||||
---|---|---|---|---|

Prev | Fast Backward | Chapter 9. Functions and Operators | Fast Forward | Next |

Mathematical operators are provided for many PostgreSQL types. For types without common mathematical conventions for all possible permutations (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 results) | 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 are also available for the bit
string types `bit` and `bit varying`, as
shown in Table 9-3.
Bit string operands of `&`, `|`,
and `#` must be of equal length. When bit
shifting, the original length of the string is preserved, as shown
in the table.

**Table 9-3. Bit String Bitwise Operators**

Example | Result |
---|---|

B'10001' & B'01101' | 00001 |

B'10001' | B'01101' | 11101 |

B'10001' # B'01101' | 11100 |

~ B'10001' | 01110 |

B'10001' << 3 | 01000 |

B'10001' >> 2 | 00100 |

Table 9-4 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 may therefore vary depending on the host system.

**Table 9-4. Mathematical Functions**

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

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

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

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

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

`exp` (dp or numeric) | (same as input) | exponential | exp(1.0) | 2.71828182845905 |

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

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

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

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

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

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

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

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

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

`random` () | dp | random value between 0.0 and 1.0 | random() | |

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

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

`setseed` (dp) | int32 | set seed for subsequent random() calls | setseed(0.54823) | 1177314959 |

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

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

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

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

Finally, Table 9-5 shows the
available trigonometric functions. All trigonometric functions
take arguments and return values of type `double
precision`.