Is it possible to automatically infer column name from view created from read_parquet?

[luqmansen]

Essentially, to do this

copy ( select 'foo' as t, 1 as i ) to 'test.parquet'; 

create view test_pq as (select * from read_parquet('test.parquet'))

select t, i from test_pq; -- ERROR; unkown column t and i

Instead of this

create view test_pq_2  as (select r['t'] as t, r['i']as i from read_parquet('test.parquet') r) ;

select t, i from test_pq_2;

This is the closest doc that I found, didn't mention anything about column projection
https://duckdb.org/docs/stable/data/parquet/overview.html#:~:text=Query%20the%20Parquet%20file%3A

Both of the example codes are possible in DuckDB, but not here. I wonder if I'm doing it wrong?

Thanks in advance!

[JelteF]

I think you should be able to do:

select r['t'], r['i'] from test_pq r;

But other than that yeah, sadly that's currently a limitation of pg_duckdb.

[luqmansen]

Thanks!

If I may know, what is the technical challenge of doing the proper mapping to the postgres type instead of returning raw duckdb.row type? Any pointers where I can understand more about it?

[JelteF]

Postgres doesn't really support functions that can return dynamic columns based on the input. It really requires columns to be known in advance. The duckdb.row type is a kind of workaround around that, but it does have some limitations. https://github.com/duckdb/pg_duckdb/blob/main/docs/types.md#duckdbrow

[YuweiXiao]

Does a foreign table (server) have a role to play in this context? E.g., create foreign table xxx ('location'). Internally, we (auto) infer the table schema from the data files. Not sure how much effort would be required to implement this.