The POWER Function in SQL: Exponents, Roots, and Compound Growth

POWER(base, exponent) raises a number to a power: POWER(2, 10) equals 1024. It is the workhorse for growth math, metric normalization, and extracting roots through a fractional exponent.

Basic syntax

POWER takes two arguments — the base and the exponent — and returns a floating-point number (double precision in PostgreSQL). The alias POW works in MySQL and ClickHouse; PostgreSQL accepts both names.

SELECT POWER(2, 10) AS kib,      -- 1024
       POWER(10, 6) AS million,  -- 1000000
       POWER(2, -1) AS half;     -- 0.5

A negative exponent yields a fraction, and a zero exponent is always one. PostgreSQL also has an infix ^ operator that reads well inside formulas:

SELECT 2 ^ 10 AS via_operator;   -- 1024 in PostgreSQL

• In MySQL and ClickHouse, ^ is bitwise XOR, not exponentiation. Use POW there.
• The result is always real; for an integer answer wrap it in ROUND(...)::bigint.

Fractional exponents and roots

An n-th root is just raising a value to the power 1/n. The square root has its own function, SQRT, but POWER gives you any root.

SELECT POWER(27, 1.0 / 3) AS cube_root,    -- 3
       POWER(16, 0.25)     AS fourth_root,  -- 2
       SQRT(2)             AS sqrt2;        -- 1.4142...

Use this to compress the spread of order amounts with a cube root — a common trick for dampening outliers in dashboards:

SELECT id,
       amount,
       ROUND(POWER(amount, 1.0 / 3), 2) AS dampened
FROM orders
WHERE status = 'paid'
ORDER BY amount DESC;

The geometric mean of salaries per department is another case where EXP and LN shine, but a root can also be expressed through POWER:

SELECT dept,
       ROUND(EXP(AVG(LN(salary))), 2) AS geo_mean_salary
FROM employees
GROUP BY dept;

• Write 1.0 / 3, not 1 / 3: in PostgreSQL and MySQL integer division of 1 / 3 is 0, which collapses the whole result to 1.

Compound interest and growth

The classic compounding formula is base * (1 + rate) ^ periods. Let's project where the average order value lands if it grows 3% per month for a full year.

SELECT ROUND(AVG(amount), 2)                          AS avg_now,
       ROUND(AVG(amount) * POWER(1 + 0.03, 12), 2)    AS avg_in_a_year
FROM orders
WHERE status = 'paid';

The inverse problem — finding the average monthly growth rate (CAGR) of signups — also leans on POWER with a fractional exponent. The base must be the ratio of the last value to the first one (last_count / first_count), and the exponent is one over the number of periods: POWER(last_count / first_count, 1.0 / periods) - 1. A plain COUNT(*) over the whole range does not give you that ratio — it counts rows, not their growth from the first month to the last. So we take the signups of the first and last month separately and divide one by the other. Note the 1.0 in the exponent and the numeric cast, so neither division collapses to zero:

WITH monthly AS (
    SELECT date_trunc('month', created_at) AS m,
           COUNT(*)                        AS signups
    FROM users
    GROUP BY 1
),
bounds AS (
    SELECT (SELECT signups FROM monthly ORDER BY m ASC  LIMIT 1) AS first_count,
           (SELECT signups FROM monthly ORDER BY m DESC LIMIT 1) AS last_count,
           (SELECT COUNT(*) - 1 FROM monthly)                    AS periods
)
SELECT ROUND(
         (POWER(last_count::numeric / first_count, 1.0 / periods) - 1) * 100, 2
       ) AS monthly_growth_pct
FROM bounds;

Overflow and gotchas

Powers grow explosively. A large exponent overruns the type quickly, and PostgreSQL throws value out of range: overflow instead of a silent NULL.

SELECT POWER(10, 308);   -- ok, near the double precision limit
SELECT POWER(10, 309);   -- ERROR: value out of range: overflow

• A negative base with a fractional exponent (POWER(-8, 0.5)) is a complex number; PostgreSQL raises an error rather than returning NaN.
• In PostgreSQL, POWER(numeric, numeric) is more precise but more expensive than the double precision version. Cast to numeric deliberately for money.
• ClickHouse's pow always returns Float64, so expect rounding error at large magnitudes.
• POWER(0, 0) equals 1 in all three engines — a math convention, not a bug.

Keep exponents within sane bounds, cast types explicitly, and use 1.0 / n for roots; then POWER stays a predictable tool for both analytics and financial formulas.