SQRT in SQL: Square Roots, Euclidean Distance, and Standard Deviation

SQRT returns the square root of a number: a classic building block for geometry, statistics, and any formula with a "root of a sum of squares" in it. In PostgreSQL it is an ordinary function, but it has a couple of sharp edges — negative input and type choice — that are easy to trip over in production.

Basic syntax and types

The simplest case is the root of a constant:

SELECT SQRT(144);   -- 12
SELECT SQRT(2);     -- 1.4142135623730951

SQRT takes double precision and returns double precision. If you pass a numeric, PostgreSQL picks the numeric overload and returns numeric — exact, but slower:

SELECT SQRT(2::numeric);  -- 1.4142135623730950488...
SELECT pg_typeof(SQRT(2::numeric));  -- numeric
SELECT pg_typeof(SQRT(2::double precision));  -- double precision

• For money in orders.amount (usually numeric), the root stays numeric on its own.
• For heavy analytics over millions of rows, casting to double precision is noticeably faster.

The error on negative input

The square root of a negative number is undefined over the reals, so PostgreSQL does not return NULL — it raises an error:

SELECT SQRT(-1);
-- ERROR: cannot take square root of a negative number

This means a single "bad" row can fail the whole query. If your input might go negative (say, a difference of magnitudes), take the absolute value explicitly:

SELECT SQRT(ABS(balance))    AS magnitude,
       SIGN(balance)         AS direction
FROM (SELECT amount - 100 AS balance FROM orders) t;

• Guarantee the sign upfront: ABS(x) or power(x, 2) before the root.
• A NULL input yields a NULL output — that is not an error, unlike a negative number.

Gotcha: in MySQL, SQRT(-1) returns NULL rather than raising, and ClickHouse returns nan. The same query behaves differently across engines, and a silent NULL/nan can quietly corrupt downstream aggregates before you notice.

Euclidean distance

The classic use is the distance between two points via the Pythagorean theorem: SQRT(dx^2 + dy^2). Suppose users carry lat/lon coordinates and we want those near an office:

SELECT id,
       name,
       SQRT(power(lat - 40.0, 2) + power(lon - (-3.7), 2)) AS dist
FROM users
ORDER BY dist
LIMIT 10;

To merely rank by closeness, you do not need the root — squared distance is monotonic:

SELECT id, name
FROM users
ORDER BY power(lat - 40.0, 2) + power(lon - (-3.7), 2)
LIMIT 10;

• Drop SQRT when you want order, not the actual number: less CPU work.
• PostgreSQL also has ^ for exponentiation (x ^ 2), but power(x, 2) reads less ambiguously.

Standard deviation by hand

PostgreSQL ships stddev_pop and stddev_samp, but it is worth knowing how to assemble the variance yourself: it is SQRT(AVG(x^2) - AVG(x)^2). Let us measure salary spread per department:

SELECT dept,
       SQRT(AVG(power(salary, 2)) - power(AVG(salary), 2)) AS std_pop,
       stddev_pop(salary)                                  AS std_check
FROM employees
GROUP BY dept;

The std_pop and std_check columns will match. The manual formula shows exactly where the root comes from, and it carries over to a window:

SELECT id,
       salary,
       SQRT(
         AVG(power(salary, 2)) OVER w
         - power(AVG(salary) OVER w, 2)
       ) AS dept_std
FROM employees
WINDOW w AS (PARTITION BY dept);

• On double precision, the formula AVG(x^2) - AVG(x)^2 can produce a tiny negative value from rounding error — and then SQRT fails. Wrap it as SQRT(GREATEST(variance, 0)).
• For financial reporting, compute in numeric: slower, but free of rounding surprises.

Wrap-up

SQRT is simple but demands discipline: guard against negative input with ABS/GREATEST, choose numeric versus double precision deliberately, and drop the root where only the ordering matters. Do that and geometry and statistics in SQL stay both fast and predictable.