Repository navigation
NUMERIC read as DOUBLE doesn't match Postgres's float8 cast #604
Description
On main (8d201d4), numeric values that the extension reads as DOUBLE (unconstrained numeric, or a numeric too wide for DuckDB's DECIMAL) sometimes differ from Postgres's own numeric::float8 cast:
memory D ATTACH '' as pg (TYPE postgres);
memory D SELECT n AS duckdb, f AS postgres, n = f AS same
FROM postgres_query('pg', 'SELECT n, n::float8 AS f FROM (
SELECT 8629433::numeric / 1000
UNION ALL SELECT avg(x) FROM (VALUES (275.38), (232.58), (785.33)) v(x)
UNION ALL SELECT 1e32
) t(n)');
┌───────────────────────┬───────────────────┬─────────┐
│ duckdb │ postgres │ same │
│ double │ double │ boolean │
├───────────────────────┼───────────────────┼─────────┤
│ 8629.432999999999 │ 8629.433 │ false │
│ 431.09666666666664 │ 431.0966666666667 │ false │
│ 9.999999999999999e+31 │ 1e+32 │ false │
└───────────────────────┴───────────────────┴─────────┘
These aren't the nearest double to the exact value, which is what Postgres's cast (through strtod) gives.
With SET pg_use_text_protocol = true the same query returns the correct values, so the problem is in decoding the binary numeric format. The cause is DecimalConversionDouble, which builds the double with floating-point arithmetic that rounds at each step.
Activity
staticlibs commentedon Oct 13, 2026
Hi, thanks for raising the issue! Don't have cycles to address it right now, but can accept a PR if that would be possible.
Fix binary NUMERIC-to-DOUBLE rounding