PostgreSQL Math & Numeric Functions

Writing `WHERE abs(delta) < 5` prevents PostgreSQL from using an index on `delta`. The planner cannot invert a function wrapper to perform an index seek.

✓ Instead: Rewrite as `WHERE delta BETWEEN -5 AND 5` to allow a btree index scan on `delta`.

`power(x, 1.0/3.0)` computes 1.0/3.0 as a floating-point approximation (~0.3333333333333333), which can introduce rounding error. `cbrt` uses a dedicated algorithm that is more accurate for this specific case.

✓ Instead: Use `cbrt(x)` for cube roots rather than `power(x, 1.0/3.0)`.

Developers sometimes use `ceil(n / k)` expecting pages or chunk counts, but forget that integer division of two integers in SQL truncates before `ceil` ever sees it. `ceil(7 / 2)` = `ceil(3)` = 3, not 4.

✓ Instead: Cast at least one operand to numeric first: `ceil(n::numeric / k)`.

Using both `ceil` and `ceiling` interchangeably across a codebase creates inconsistency that confuses code reviewers and makes grep-based audits harder.

✓ Instead: Pick one form — preferably `ceil` for brevity — and apply it consistently via a linting rule or style guide.

Storing raw radian values from a library and calling `degrees()` in every SELECT adds per-row overhead and makes ad-hoc queries confusing.

✓ Instead: Store angles in degrees in the database (the human-friendly unit) and convert to radians only when calling trig functions with `radians()`. Alternatively, use the degree-native trig variants `sind`, `cosd`, `tand`.

`div(9.9, 3.3)` may not return 3 due to floating-point representation of the inputs — numeric arithmetic can leave a tiny fractional residue that causes div to return 2.

✓ Instead: Cast inputs to integer or bigint before using `div` when operating on whole-number values stored as numeric: `div(y::bigint, x::bigint)`.

`exp(710)` overflows double precision and returns an error or infinity. ETL pipelines ingesting raw model outputs can crash when an unbounded coefficient exceeds ~709.

✓ Instead: Clamp the input before passing it to exp: `exp(LEAST(coef, 700))`, and document why the clamp is safe in your domain.

`factorial(25)` returns a 26-digit numeric that then gets divided by another large factorial — intermediate overflow is likely when both n and k are large, and the computation is much slower than using logarithms.

✓ Instead: For large combinatoric computations, use the log-gamma trick: `round(exp(lgamma((n+1)::float) - lgamma((k+1)::float) - lgamma((n-k+1)::float)))::bigint`.

`floor(-7.0 / 2)` = `floor(-3.5)` = -4, not -3. If you want integer division that truncates toward zero (the behavior most programmers expect), `floor` gives the wrong answer for negative dividends.

✓ Instead: Use `trunc(y / x)` or the `div(y, x)` function when you want truncation-toward-zero semantics for integer quotients.

`gcd` requires integer types (integer, bigint, or numeric with scale 0). Passing a non-integer numeric like `gcd(1.5, 2.5)` raises an error because GCD is not defined for reals.

✓ Instead: Cast to integer before calling: `gcd(val1::bigint, val2::bigint)`, and only when your domain guarantees integer values.

`lcm(a, b)` can produce a result much larger than either input — up to `a * b`. For large bigint inputs (e.g., scheduling intervals in seconds over years), the result can overflow bigint.

✓ Instead: Check `a * b / gcd(a, b) <= 9223372036854775807` before calling, or use numeric type inputs when overflow is possible.

`ln(0)` and `ln(-1)` both throw errors. If your column contains zeroes or negatives (e.g., a balance that can go negative), the query will fail at runtime for those rows.

✓ Instead: Guard with `NULLIF` or a CASE: `ln(NULLIF(GREATEST(value, 0), 0))` to return NULL instead of an error, and document that negative values are excluded.

In most programming languages `log(x)` means natural log (base e). In PostgreSQL `log(x)` means base-10 log. Porting formulas from Python or JavaScript without adjustment silently produces wrong results.

✓ Instead: Use `ln(x)` for natural log in PostgreSQL. Reserve `log(x)` only when you explicitly want base-10, and add a comment noting the distinction.

`min_scale` only accepts the `numeric` type. Passing a `float8` value causes a type error, which surprises developers who assume it works on all numeric types.

✓ Instead: Cast to numeric first: `min_scale(value::numeric)`. Be aware that float-to-numeric conversion may introduce representation artefacts.

`mod(-3, 10)` returns -3 in PostgreSQL, not 7. Code that uses mod to compute a hash bucket index will map negative IDs to negative bucket numbers, causing array-out-of-bounds errors.

✓ Instead: Use `((y % x) + x) % x` or `mod(mod(y, x) + x, x)` to guarantee a non-negative result regardless of the dividend's sign.

Embedding a truncated constant like `3.14159` or `3.1415926` in queries introduces rounding error that compounds in formulas. It also makes the intent less clear.

✓ Instead: Always use `pi()` to get the full double-precision constant, or `radians(180)` as an equivalent expression.

`WHERE power(x, 2) < 100` wraps the column in a function, preventing index use on `x`. For a simple square, the planner cannot invert `power(x, 2)` to a range scan.

✓ Instead: Rewrite as `WHERE x BETWEEN -10 AND 10` (i.e., manually invert the inequality) to allow an index scan, given that x is non-negative or you handle both signs.

`sin(90)` does NOT return 1 in PostgreSQL — it interprets 90 as radians (~5156°), returning -0.894. This is one of the most common silent errors when porting formulas from spreadsheets.

✓ Instead: Write `sin(radians(90))` or use the degree-native `sind(90)` which returns exactly 1.

`random()` uses a predictable PRNG seeded at session start. An attacker who can infer or observe the seed can predict all future values, making tokens generated this way cryptographically insecure.

✓ Instead: Use `encode(gen_random_bytes(16), 'hex')` from the `pgcrypto` extension, or PostgreSQL 17+ built-in `random_uuid()` / `gen_random_uuid()` for secure random identifiers.

`random_normal(0, -1)` raises an error because a negative standard deviation is not statistically valid. ETL code that computes stddev from data and passes it directly can fail when data is degenerate.

✓ Instead: Guard with `GREATEST(computed_stddev, 0.0001)` to ensure a small positive value before passing to `random_normal`.

`round(0.5::double precision)` may return 0 or 1 depending on the platform's IEEE 754 "round half to even" implementation. This surprises developers who expect 0.5 to always round to 1.

✓ Instead: Cast to numeric for deterministic rounding: `round(0.5::numeric)` always returns 1 in PostgreSQL (half-up rounding).

`WHERE sign(balance) = true` does not work — sign returns a numeric (-1, 0, 1), not a boolean. Developers coming from languages where -1 is truthy sometimes write this and get a type error or wrong results.

✓ Instead: Compare explicitly: `WHERE sign(balance) > 0` for positive, `< 0` for negative, `= 0` for zero.

`WHERE sqrt((x-px)^2 + (y-py)^2) < radius` evaluates sqrt for every row. For pure distance comparisons, this is unnecessary — the square root is monotonic, so you can skip it.

✓ Instead: Compare squared distances directly: `WHERE (x-px)^2 + (y-py)^2 < radius^2`. This avoids calling sqrt entirely and enables faster execution on large tables.

Splitting a number with `trunc(x)` for the integer and `x - trunc(x)` for the fraction and then recombining can accumulate floating-point error, especially for double precision values.

✓ Instead: If you need both integer and fractional components, use `trunc` and `x - trunc(x)`, but work in `numeric` type to avoid floating-point representation issues.

`width_bucket` returns 0 for values below `low` and `count+1` for values above `high`. If your chart code expects buckets 1–N only, out-of-range data silently appears as bucket 0 or N+1, distorting your histogram.

✓ Instead: Add a `WHERE operand BETWEEN low AND high` filter, or explicitly handle buckets 0 and `count+1` as an 'out of range' category in your query output.

`sin(90)` = ~0.894, not 1 — PostgreSQL interprets the argument as radians. This is a silent error that produces plausible-looking wrong numbers.

✓ Instead: Use `sin(radians(90))` or the degree-native `sind(90)` which returns exactly 1.

`cos(pi())` returns `-0.9999999999999998` due to floating-point precision, not exactly -1. Code that checks `cos(angle) = -1` will always be false.

✓ Instead: Use a tolerance comparison: `abs(cos(angle) - (-1)) < 1e-9`, or use the degree-native `cosd(180)` which correctly returns exactly -1.

`tan(pi()/2)` approaches ±infinity. At exactly pi/2 in floating-point, the result is a very large finite number (~1.633e16) rather than an error, silently corrupting slope calculations.

✓ Instead: Before calling `tan(angle)`, check that `abs(cos(angle)) > 1e-9`. If near-vertical angles are expected, use `atan2(dy, dx)` instead of computing tan directly.

Floating-point arithmetic can produce values like 1.0000000000000002 that are just outside the valid domain. `asin(1.0000000000000002)` raises an error, crashing queries in Haversine or other geometric formulas.

✓ Instead: Clamp the input before calling: `asin(GREATEST(-1.0, LEAST(1.0, computed_value)))` to absorb floating-point overshoot.

`atan2(x, y)` and `atan2(y, x)` are both valid calls but produce different angles. Swapping the arguments is a frequent bug when porting code — the correct signature is always `atan2(y, x)`.

✓ Instead: Always name your arguments explicitly in comments: `atan2(/* y */ delta_lat, /* x */ delta_lon)` to make the order self-documenting.

`sinh(711)` overflows double precision to infinity. If your data contains large exponents (e.g., model weights that escaped normalization), sinh will silently return Infinity.

✓ Instead: Clamp input to a safe range before calling: `sinh(LEAST(ABS(x), 700) * SIGN(x))`, and validate that upstream data is properly normalized.

`scale` only works on the `numeric` type. Calling `scale(some_float8_column)` raises a type error. Developers sometimes try to use it to count decimal places on float columns.

✓ Instead: Cast to numeric first (`scale(value::numeric)`), but be aware that the cast may introduce representation artefacts — prefer storing currency and precision-sensitive values as `numeric` from the start.

Wrapping a column in `trim_scale()` inside a WHERE clause — e.g., `WHERE trim_scale(price) = 9.99` — disables any index on `price` and adds per-row computation.

✓ Instead: Apply `trim_scale` only in the SELECT list for display purposes. For filtering, compare the raw stored value directly: `WHERE price = 9.99000`.

`setseed` sets the PRNG state for the current backend session only. In a connection-pooled application or parallel query, different connections have independent seeds. Two calls with the same seed in different sessions will produce the same sequence — but concurrent sessions will not stay in sync.

✓ Instead: For reproducible distributed sampling, use a hash-based approach instead: `ORDER BY md5(id::text || '0.42')` gives a deterministic shuffle without relying on session state.