invalid argument for power function
SQLSTATE 2201F is raised when the POWER() function receives arguments outside its mathematical domain — specifically, a negative base with a non-integer exponent, which produces a complex number that Postgres cannot represent.
- 1Calling POWER(negative_number, fractional_exponent) which is mathematically undefined in real numbers
Raising a negative number to a fractional power.
SELECT POWER(-2.0, 0.5); -- square root of -2 is imaginary
expected output
ERROR: a negative number raised to a non-integer power is not allowed
Fix 1
Use ABS to ensure the base is non-negative when the exponent is fractional
WHEN When the sign of the base may vary.
SELECT POWER(ABS(value), 0.5) FROM data;
Why this works
ABS ensures the base is non-negative, making POWER well-defined for fractional exponents.
Fix 2
Guard with CASE or NULLIF
WHEN When a negative base should produce NULL rather than an error.
SELECT CASE WHEN value >= 0 THEN POWER(value, 0.5) ELSE NULL END FROM data;
Why this works
The CASE prevents POWER from receiving a negative base.
Class 22 — Data Exception
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev