BigQuery NUMERIC transpiled to DuckDB DECIMAL loses precision

[nikchha]

Hello :) and another issue:

BigQuery's NUMERIC defaults to NUMERIC(38, 9). When transpiling to DuckDB, the output DECIMAL without precision defaults to DECIMAL(18, 3), causing precision loss.

Fully reproducible code snippet

import sqlglot
result = sqlglot.transpile(
    "SELECT CAST(x AS NUMERIC) FROM t",
    read="bigquery",
    write="duckdb"
)[0]
print(result)

Output:

SELECT CAST(x AS DECIMAL) FROM t

Expected:

SELECT CAST(x AS DECIMAL(38, 9)) FROM t

Why current output is wrong

BigQuery NUMERIC preserves 9 decimal places. DuckDB DECIMAL without precision defaults to 3 decimal places.

-- BigQuery:
SELECT CAST(1.123456789 AS NUMERIC);
-- Returns: 1.123456789
-- DuckDB with current transpilation:
SELECT CAST(1.123456789 AS DECIMAL);
-- Returns: 1.123 (precision loss)
-- DuckDB with expected transpilation:
SELECT CAST(1.123456789 AS DECIMAL(38, 9));
-- Returns: 1.123456789

Official Documentation

• BigQuery NUMERIC type
• DuckDB DECIMAL type

Version

Tested on main (28.4.2.dev1)

[georgesittas]

Hey, thanks for reporting this. It's a legit feature gap today.

We have realized that properly addressing it requires a bigger effort, which is tricky to prioritize at the moment. I'll close this, but we'll probably revisit it soon (after holidays), so we'll reopen then.