In this series, I encountered a pg_duckdb gotcha with unconstrained numeric column. With duckdb.force_execution = true, I expected DuckDB to run my aggregate. But it ran on the PostgreSQL engine instead, with a warning. Here's how to see it and fix it.
Reproduce
I created a table with four columns of the same number, differing only in declared type:
DROP TABLE IF EXISTS nf;
CREATE TABLE nf (
id bigint
, n_bare numeric
, n_10_2 numeric(10,2)
, d double precision
);
INSERT INTO nf SELECT g, g+0.5, g+0.5, g+0.5
FROM generate_series(1,100000) g
;
ANALYZE nf
;
Here are their Postgres type modifiers (-1 means "no declared precision"):
postgres=> SELECT attname, atttypmod FROM pg_attribute
WHERE attrelid='nf'::regclass AND attnum>0 AND NOT attisdropped ORDER BY attnum
;
attname | atttypmod
---------+-----------
id | -1
n_bare | -1
n_10_2 | 655366
d | -1
(4 rows)
-
n_bareanddshow-1(no declared precision) -
n_10_2shows655366, the encoded form ofnumeric(10,2)
The symptom
I forced DuckDB on, then sum the bare numeric:
postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN SELECT sum(n_bare) FROM nf
;
WARNING: (PGDuckDB/CreatePlan) Prepared query returned an error: Binder Error: No function matches the given name and argument types 'sum(UnsupportedPostgresType(DuckDB requires the precision of a NUMERIC to be set))'
QUERY PLAN
-----------------------------------------------------
Aggregate (cost=2074.00..2074.01 rows=1 width=32)
-> Seq Scan on nf (cost=0.00..1824.00 rows=100000 width=8)
(2 rows)
That's a plain PostgreSQL plan. I've set force_execution = true, yet the top node is a plain Aggregate, not a Custom Scan (DuckDBScan). DuckDB declined and PostgreSQL ran it. A WARNING at plan time is emitted when pg_duckdb tries to build the DuckDB plan and bails on the unconstrained numeric. But it's a WARNING, not an error, easy to miss from an application, and the query still returns the right answer on the native executor. If you were only watching the response time you'd think pg_duckdb didn't help, when it never ran the query in DuckDB at all.
I've run the same sum over the numeric(10,2) column:
postgres=> EXPLAIN SELECT sum(n_10_2) FROM nf
;
QUERY PLAN
-------------------------------------------------
Custom Scan (DuckDBScan) (cost=0.00..0.00 rows=0 width=0)
DuckDB Execution Plan:
┌───────────────────────────┐
│ UNGROUPED_AGGREGATE │
│ ──────────────────── │
│ Aggregates: sum(#0) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ n_10_2 │
│ │
│ ~100,000 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PGDUCKDB_POSTGRES_SCAN │
│ ──────────────────── │
│ Table: nf │
│ Projections: n_10_2 │
│ │
│ ~100,000 rows │
└───────────────────────────┘
(25 rows)
DuckDB runs it. The only difference between the two queries is that one column has a declared precision and the other doesn't.
Why
DuckDB's DECIMAL is fixed precision, up to 38 digits. A Postgres numeric with a declared (p,s) maps straight onto it. A bare numeric has no bound — there's nothing for DuckDB to map it to — so pg_duckdb leaves the whole query to PostgreSQL. (A numeric(38,10) works fine, only the unconstrained one falls back.) The fallback itself is a feature: forcing DuckDB can never break a query, it just might not accelerate it. The problem is only that it's silent.
The fix
One session setting makes the bare numeric run in DuckDB, read as double:
postgres=> SET duckdb.convert_unsupported_numeric_to_double = true;
SET
postgres=> EXPLAIN SELECT sum(n_bare) FROM nf
;
QUERY PLAN
-------------------------------------------------
Custom Scan (DuckDBScan) (cost=0.00..0.00 rows=0 width=0)
DuckDB Execution Plan:
┌───────────────────────────┐
│ UNGROUPED_AGGREGATE │
│ ──────────────────── │
│ Aggregates: sum(#0) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ ──────────────────── │
│ n_bare │
│ │
│ ~100,000 rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PGDUCKDB_POSTGRES_SCAN │
│ ──────────────────── │
│ Table: nf │
│ Projections: n_bare │
│ │
│ ~100,000 rows │
└───────────────────────────┘
(25 rows)
No more warning, and the top node is now Custom Scan (DuckDBScan) — DuckDB runs it, reading the bare numeric as double.
The caveat is in the name: double is floating point, so you can lose the exact decimal precision numeric guarantees — fine for analytical sums and averages, think twice for exact financial rounding. The cleaner fix is to declare the precision in the schema (numeric(12,2)), so DuckDB runs it exactly, no flag.
Takeaway
With force_execution = true, always EXPLAIN and check the top node is Custom Scan (DuckDBScan). If it's a plain PostgreSQL node, DuckDB declined — and a bare numeric (typmod -1) is the usual reason. Declare the precision, or set duckdb.convert_unsupported_numeric_to_double to true if your can accept that decimal fractions that cannot be stored with exact base-10 precision are rounded.
Even with duckdb.force_execution = true, you should always run EXPLAIN to verify that the query utilizes the Custom Scan (DuckDBScan) node. If the planner falls back to native PostgreSQL nodes, it indicates that DuckDB declined the query—most commonly due to an unconstrained PostgreSQL NUMERIC column (with a type modifier of -1). To resolve this, explicitly declare the column's precision and scale, or set duckdb.convert_unsupported_numeric_to_double = true if your workload can tolerate the inherent precision loss and rounding behavior of binary floating-point (DOUBLE) conversions.
