duckdb / duckdb/duckdb-postgres
GEOMETRY column not recognized when using postgres_query()
- Dominant language
- C++
- Stars
- 372
- Forks
- 105
- Avg merge
- 10h 19m
- Merged PRs (30d)
- 18
Description
### What happens?
I have a table in PostgreSQL with a GEOMETRY column. Since DuckDB 1.5, this GEOMETRY column is recognized during import:
```
select objectid, shape from nupla_uept limit 5;
┌──────────┬─────────────────────────────────┐
│ objectid │ shape │
│ int32 │ geometry │
├──────────┼─────────────────────────────────┤
│ 116927 │ POINT (2604833.296 1208390.399) │
│ 116928 │ POINT (2605120.181 1208221.813) │
│ 116929 │ POINT (2604777.003 1207929.125) │
│ 98169 │ POINT (2610375.849 1189134.644) │
│ 98170 │ POINT (2610409.164 1189117.758) │
└──────────┴─────────────────────────────────┘
```
However, if I make the query with `postgres_query()`, the column is not recognized:
```
select * from postgres_query('vek1', 'SELECT objectid, shape from geodb.nupla_uept') LIMIT 5;
┌──────────┬────────────────────────────────────────────────────┐
│ objectid │ shape │
│ int32 │ varchar │
├──────────┼────────────────────────────────────────────────────┤
│ 116927 │ 010100002008080000F853E3A590DF43412FDD246646703241 │
│ 116928 │ 0101000020080800000C022B1720E043419CC420D09D6F3241 │
│ 116929 │ 010100002008080000D34D628074DF434100000020796E3241 │
│ 98169 │ 0101000020080800003108ACEC63EA43411B2FDDA40E253241 │
│ 98170 │ 010100002008080000B6F3FD9474EA4341BA490CC2FD243241 │
└──────────┴────────────────────────────────────────────────────┘
```
```
It does not work with this generic statement either:
select * from postgres_query('vek1', 'select st_geometryfromtext(''POINT(2600000 1200000)'', 2056);');
┌────────────────────────────────────────────────────┐
│ st_geometryfromtext │
│ varchar │
├────────────────────────────────────────────────────┤
│ 0101000020080800000000000020D6434100000000804F3241 │
```
Is there a reason for this different behavior?
### To Reproduce
1. Attach a PostgreSQL Database with the PostGIS Extension installed
2. Execute `select * from postgres_query('vek1', 'select st_geometryfromtext(''POINT(2600000 1200000)'', 2056);');`
### OS:
Windows 11
### PostgreSQL Version:
15.14
### DuckDB Version:
1.5
### DuckDB Client:
CLI
### Full Name:
Peter Schär
### Affiliation:
Office for Geoinformation, Canton Bern, Switzerland
### Have you tried this on the latest `main` branch?
- [x] I agree
### Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- [x] I agree
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.