Working with Mathematical Functions
1. Using Basic Arithmetic
| Operator | Description |
|---|
| + - * / | Add/sub/mul/div (integer / for ints) |
| % | Modulo |
| ^ | Exponent |
| |/ | Square root |
| ||/ | Cube root |
2. Using Power and Root
SELECT power(2, 10), sqrt(2), cbrt(27);
3. Rounding Numbers
| Function | Effect |
|---|
| round(x) | To nearest integer (banker's for numeric) |
| round(x, n) | To n decimals |
| ceil(x) / ceiling(x) | Up |
| floor(x) | Down |
4. Using Absolute Value
5. Truncating Decimals
SELECT trunc(3.789); -- 3
SELECT trunc(3.789, 2); -- 3.78
6. Using Sign Function
SELECT sign(-5), sign(0), sign(7); -- -1, 0, 1
7. Generating Random Numbers
SELECT random(); -- 0.0 ≤ x < 1.0
SELECT floor(random() * 100)::int; -- 0..99
SELECT random_normal(); -- gaussian (PG 16+)
SELECT setseed(0.5); -- reproducible
8. Using Modulo Operator
SELECT 10 % 3; -- 1
SELECT mod(10, 3);
9. Using Exponential
SELECT exp(1); -- e
SELECT ln(exp(2)); -- 2
10. Using Logarithms
| Function | Base |
|---|
| ln(x) | Natural (e) |
| log(x) | Base 10 |
| log(b, x) | Arbitrary base |
| log10(x), log2(x) PG 17+ | Specific bases |
11. Using Trigonometric Functions
| Function | Units |
|---|
| sin/cos/tan | Radians |
| sind/cosd/tand | Degrees |
| asin/acos/atan/atan2 | Returns radians |
| sinh/cosh/tanh | Hyperbolic |
12. Using PI Constant
SELECT pi(); -- 3.141592653589793
13. Using Radians and Degrees
SELECT radians(180); -- π
SELECT degrees(pi()); -- 180
14. Using Factorial
SELECT factorial(6); -- 720
15. Using GCD and LCM Functions
SELECT gcd(12, 18); -- 6
SELECT lcm(4, 6); -- 12 (PG 13+)