duckdb / duckdb/duckdb-postgres

Allow explicit result column types for postgres_query

Open
#491 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
372
Forks
105
Avg merge
10h 19m
Merged PRs (30d)
18

Description

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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```text
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.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with the existing postgres_query entry point and the columns handling already implemented for read_postgres_binary. Trace how result-column types are mapped and how unsupported record values are cast before binary COPY decoding. Done means postgres_query accepts the named columns schema and uses it to decode record and record[] results as the supplied DuckDB STRUCT types.

Written by the indexing model from the issue text.

Assessment

Tech stack
cpp, postgresql, sql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.