Problem
postgres_query currently cannot preserve anonymous PostgreSQL record and record[] result columns as DuckDB STRUCT and STRUCT[] values.
This matters for queries that intentionally return nested anonymous records, for example ROW(...) values and arrays of ROW(...) values.
At the moment, postgres_query asks PostgreSQL for the result-column types before running the query and then falls back to VARCHAR. The scan still uses PostgreSQL binary COPY under the hood, but the generated COPY query casts these unsupported columns to VARCHAR, so the binary composite values are lost before DuckDB can decode them.
This means a query like:
SELECT *
FROM postgres_query(
'pg',
'SELECT ROW(1::BIGINT, now()::TIMESTAMP) AS r'
);
returns r as VARCHAR instead of a DuckDB STRUCT.
Proposed change
Please add an optional columns named parameter to postgres_query, similar to the one already implemented for read_postgres_binary.
For example:
SELECT *
FROM postgres_query(
'pg',
$query$
SELECT
ROW(1::BIGINT, now()::TIMESTAMP) AS r,
ARRAY[
ROW(10::BIGINT, 'a'::TEXT),
ROW(20::BIGINT, 'b'::TEXT)
] AS rs
$query$,
columns = {
r: 'STRUCT(id BIGINT, created TIMESTAMP)',
rs: 'STRUCT(child_id BIGINT, name VARCHAR)[]'
}
);The supplied columns schema would override or supplement the automatic type mapping for result columns.
In particular, it would let users tell postgres_query how to decode PostgreSQL record / record[] values rather than casting them to VARCHAR.
This is closely related to existing functionality:
SELECT *
FROM read_postgres_binary(
'dump.bin',
columns = {
r: 'STRUCT(id BIGINT, created TIMESTAMP)',
rs: 'STRUCT(child_id BIGINT, name VARCHAR)[]'
}
);read_postgres_binary already proves that, given an explicit DuckDB schema, PostgreSQL binary composite values can be decoded into DuckDB structs. The missing piece is exposing the same explicit-schema mechanism directly on postgres_query, before unsupported record columns are cast to VARCHAR.
Minimal demonstration
This works today when the PostgreSQL binary COPY stream is read with an explicit schema:
PostgreSQL:
COPY (
SELECT
ROW(1::BIGINT, '2026-06-29 12:00:00'::TIMESTAMP) AS r,
ARRAY[
ROW(10::BIGINT, 'a'::TEXT),
ROW(20::BIGINT, 'b'::TEXT)
] AS rs
) TO STDOUT (FORMAT binary);
DuckDB:
INSTALL postgres;
LOAD postgres;
SELECT *
FROM read_postgres_binary(
'dump.bin',
columns = {
r: 'STRUCT(id BIGINT, created TIMESTAMP)',
rs: 'STRUCT(child_id BIGINT, name VARCHAR)[]'
}
);Expected result shape:
r: STRUCT(id BIGINT, created TIMESTAMP)
rs: STRUCT(child_id BIGINT, name VARCHAR)[]
The same query through postgres_query currently returns these columns as VARCHAR, because the anonymous composite types are not mapped and are cast before binary COPY decoding.
Why this would be useful
This would allow high-volume PostgreSQL-to-DuckDB transfer of nested query results without JSON serialization, without creating named composite types in PostgreSQL.
It would also make postgres_query more consistent with read_postgres_binary, where users can already provide the schema required to decode PostgreSQL binary data that cannot be inferred automatically.
Problem
postgres_querycurrently cannot preserve anonymous PostgreSQLrecordandrecord[]result columns as DuckDBSTRUCTandSTRUCT[]values.This matters for queries that intentionally return nested anonymous records, for example
ROW(...)values and arrays ofROW(...)values.At the moment,
postgres_queryasks PostgreSQL for the result-column types before running the query and then falls back toVARCHAR. The scan still uses PostgreSQL binary COPY under the hood, but the generated COPY query casts these unsupported columns toVARCHAR, so the binary composite values are lost before DuckDB can decode them.This means a query like:
returns
rasVARCHARinstead of a DuckDBSTRUCT.Proposed change
Please add an optional
columnsnamed parameter topostgres_query, similar to the one already implemented forread_postgres_binary.For example:
The supplied
columnsschema would override or supplement the automatic type mapping for result columns.In particular, it would let users tell
postgres_queryhow to decode PostgreSQLrecord/record[]values rather than casting them toVARCHAR.This is closely related to existing functionality:
read_postgres_binaryalready proves that, given an explicit DuckDB schema, PostgreSQL binary composite values can be decoded into DuckDB structs. The missing piece is exposing the same explicit-schema mechanism directly onpostgres_query, before unsupportedrecordcolumns are cast toVARCHAR.Minimal demonstration
This works today when the PostgreSQL binary COPY stream is read with an explicit schema:
PostgreSQL:
DuckDB:
Expected result shape:
The same query through
postgres_querycurrently returns these columns asVARCHAR, because the anonymous composite types are not mapped and are cast before binary COPY decoding.Why this would be useful
This would allow high-volume PostgreSQL-to-DuckDB transfer of nested query results without JSON serialization, without creating named composite types in PostgreSQL.
It would also make
postgres_querymore consistent withread_postgres_binary, where users can already provide the schema required to decode PostgreSQL binary data that cannot be inferred automatically.