Running sql query in app.preset.io system which should use postgresql. The query just calculate precision and recall values from metrics_table, but got
weird error. Ran same sql query use other sql query tool and it works fine.
WITH time_periods AS (
SELECT year, week, cast(tp as real) as tp , cast(fp as real) as fp,
cast(tn as real) as tn, cast(fn as real) as fn
FROM metrics_table
WHERE week >= 37
)
SELECT
year,
week,
CASE
WHEN (tp + fp) = 0 THEN 0
ELSE ROUND(cast(tp as real) / (cast(tp as real) + cast(fp as real)), 3)
END as precision,
CASE
WHEN (tp + fn) = 0 THEN 0
ELSE ROUND(cast(tp as real) / (cast(tp as real) + cast(fn as real)), 3)
END as recall
FROM time_periods
ORDER BY year, week
got following error
postgresql error: Error during planning: Coercion from [Float32, Int64] to the signature OneOf([Exact([Float64]), Exact([Float32]), Exact([Decimal(38, 10), Int32])]) failed
It’s this part causing the error:
ROUND(cast(tp as real) / (cast(tp as real) + cast(fp as real)), 3)
Tried to cast the columns to float type or numeric type but still got same error.
These are the round() functions in PostgreSQL:
df round
List of functions
Schema │ Name │ Result data type │ Argument data types │ Type
════════════╪═══════╪══════════════════╪═════════════════════╪══════
pg_catalog │ round │ double precision │ double precision │ func
pg_catalog │ round │ numeric │ numeric │ func
pg_catalog │ round │ numeric │ numeric, integer │ func
(3 rows)
The two-argument form of round() exists only for numeric, but not for real or double precision.