How to fix 2201F: invalid_argument_for_power_function in PostgreSQL

-- Example error-producing queries
SELECT id, POWER(price, 0.5) FROM products;
-- ERROR if price contains negative values: invalid argument for power function

-- Zero raised to negative power
SELECT id, POWER(0, -1) FROM data;
-- ERROR: invalid argument for power function

-- Negative base with fractional exponent
SELECT id, POWER(temperature, 1/3) FROM measurements;
-- ERROR if temperature < 0: invalid argument for power function

-- Find negative bases (when exponent is fractional)
SELECT id, base_value, exponent_value
FROM calculations
WHERE base_value < 0 AND (exponent_value != CAST(exponent_value AS INT));

-- Check for zero base with negative exponent
SELECT id, base_value, exponent_value
FROM calculations
WHERE base_value = 0 AND exponent_value < 0;

-- Count affected rows
SELECT COUNT(*)
FROM products
WHERE price < 0;  -- If price is used as base

-- Check exponent values
SELECT DISTINCT exponent_value
FROM calculations
WHERE exponent_value IS NOT NULL
ORDER BY exponent_value;

-- Calculate power using absolute value
SELECT id, POWER(ABS(temperature), 0.5) AS sqrt_abs_temp
FROM measurements;

-- Preserve sign of original value by combining with SIGN()
SELECT
  id,
  SIGN(temperature) * POWER(ABS(temperature), 0.5) AS signed_sqrt
FROM measurements;

-- For cube root and other fractional powers
SELECT id, POWER(ABS(value), 1.0/3.0) AS cube_root_abs
FROM data;

-- Only calculate power for non-negative bases with fractional exponents
SELECT id, POWER(price, 0.5) AS sqrt_price
FROM products
WHERE price >= 0;

-- Exclude zero when exponent is negative
SELECT id, POWER(amount, -1) AS reciprocal
FROM transactions
WHERE amount > 0;

-- Combining multiple conditions
SELECT
  id,
  category,
  POWER(quantity, 1.5) AS quantity_1_5
FROM inventory
WHERE quantity > 0
  AND category != 'archived'
  AND is_active = true;

-- Return NULL for invalid combinations, otherwise calculate power
SELECT
  id,
  base_val,
  exponent_val,
  CASE
    WHEN base_val > 0 THEN POWER(base_val, exponent_val)
    WHEN base_val = 0 AND exponent_val > 0 THEN 0
    ELSE NULL
  END AS power_result
FROM calculations;

-- Handle negative bases and fractional exponents specially
SELECT
  id,
  temperature,
  CASE
    WHEN temperature >= 0 THEN POWER(temperature, 0.5)
    WHEN temperature < 0 THEN NULL  -- Cannot compute sqrt of negative
    ELSE NULL
  END AS sqrt_temperature
FROM measurements;

-- Provide different results based on conditions
SELECT
  id,
  CASE
    WHEN base > 0 AND exponent < 0 THEN POWER(base, exponent)
    WHEN base = 0 AND exponent > 0 THEN 0
    WHEN base = 0 AND exponent < 0 THEN NULL  -- Undefined
    WHEN base < 0 THEN NULL  -- Skip negative bases
    ELSE POWER(base, exponent)
  END AS result
FROM data;

-- Add check constraints to prevent invalid data
ALTER TABLE calculations
ADD CONSTRAINT chk_base_non_negative_for_fractional
CHECK (base_value > 0 OR exponent_value = CAST(exponent_value AS INT));

-- Separate columns for different use cases
ALTER TABLE products
ADD CONSTRAINT chk_price_positive
CHECK (price > 0);

-- Data validation before INSERT or UPDATE
-- In application code, validate before sending to database:
-- if (base < 0 && exponent % 1 !== 0) {
//   throw new Error('Cannot raise negative base to fractional exponent');
// }

// JavaScript/Node.js example
function validatePowerInputs(base, exponent) {
  if (base < 0 && !Number.isInteger(exponent)) {
    throw new Error('Cannot raise negative base to fractional exponent');
  }
  if (base === 0 && exponent < 0) {
    throw new Error('Cannot raise zero to negative power');
  }
  return true;
}