Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

Allow explicit result column types for postgres_query

Open
  • Is something wrong?
#491 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

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

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.

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:

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.

Dominant language
C++
Stars
375
Forks
113
Avg merge
5h 29m
Merged PRs (30d)
37

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from duckdb/duckdb-postgres

All issues in duckdb/duckdb-postgres

Similar issues

More C++ issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.