Can't create table with geometry field

[fnicastri]

What happens?

trying to create a table in a remote Postgis database
wuth create table postgres_db.test_table (id int, location geometry );
duckdb return a generic error:

INTERNAL Error: Unsupported logical type for RemoveAlias
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors

To Reproduce

load/install the postgres extension
load/install the spatial extension
attach a postgres database

try to create a table with a geometry column:
create table postgres_db.test_table (id int, location geometry );

OS:

mac/linux

PostgreSQL Version:

16 + PostGIS 3.4.2

DuckDB Version:

v1.1.0 fa5c2fe15f (nightly)

DuckDB Client:

cli/python

Full Name:

Francesco Nicastri

Affiliation:

myself

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

[ken2190]

same problem here

[Hajna]

same problem, this is probably expected though

[fnicastri]

expected, maybe, but it I feel it is not the right behavior.
DuckDb support Postgres, PostGis and have Spatial extension itself,
I think it should be capable to do it.

[francbartoli]

Same problem with version 1.1.3

[tobwen]

Workaround: https://duckdb.org/docs/extensions/postgres#running-sql-queries-in-postgresql

[Youssef-Harby]

Hi @Maxxen Do you have any plans for this ?

[Maxxen]

@Youssef-Harby yes eventually it would be nice if it was possible to query postgis geometries from duckdb /w spatial, and how that we figured out how to do conversion from geoparquet to spatial geometries doing another cross-extension dependency seems feasible, but I probably won't have time to work on it until sometime after next release.

[fnicastri]

it would be nice

[Mytherin]

Looked into this a bit - the interaction could be nicer for sure, but it is possible to interact with Postgis from DuckDB currently using some work-arounds:

ATTACH 'dbname=postgres' AS s (TYPE POSTGRES);
LOAD spatial;
CALL postgres_execute(s, 'CREATE TABLE my_points(geom GEOMETRY)');
INSERT INTO s.my_points SELECT ST_Point(1,1);
SELECT ST_GeomFromWKB(unhex(geom)) FROM s.my_points;
┌─────────────────────────────┐
│ st_geomfromwkb(unhex(geom)) │
│          geometry           │
├─────────────────────────────┤
│ POINT (1 1)                 │
└─────────────────────────────┘

[Mytherin]

Made it work using WKB_BLOB in #291

ATTACH 'dbname=postgres' AS postgres_db (TYPE POSTGRES);
CREATE TABLE postgres_db.test_table (id int, location geometry );
INSERT INTO postgres_db.test_table VALUES (42, ST_Point(42, 42));
SELECT * FROM postgres_db.test_table;
┌───────┬───────────────┐
│  id   │   location    │
│ int32 │   wkb_blob    │
├───────┼───────────────┤
│  42   │ POINT (42 42) │
└───────┴───────────────┘

[tjwebb]

are there plans to fix this?

[Mytherin]

This has already been implemented in the latest version