GEOMETRY column not recognized when using postgres_query()

[peterschaer]

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?

• 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?

• I agree