Error thrown when trying to create a GEOMETRY column in postgis, from duckdb

[CGenie]

Hello,

Here's what I do:

LOAD spatial
attach 'host=localhost ...' AS db (TYPE postgres);

CREATE TABLE db.points (name varchar, pt geometry);

However, I get:

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

Any ideas if this is solvable?

[CGenie]

BTW, when I pre-create that table on the PostGIS side, I'm able to insert data like this, without error:

INSERT INTO db.points (SELECT name, ST_AsText(ST_GeomAsHEXEWKB(pt)) AS pt FROM my_other_source);

The ST_AsText is important, I figured it out based on https://postgis.gishub.org/chapters/geometries.html#casting-from-text.

However, I'm not sure if SRID is preserved this way?

I know I could do: 'SRID=4326;' || ST_AsText(...).

[Maxxen]

Hello!
This is currently not supported, the DuckDB Postgres adapter has no knowledge of the spatial extension (or postgis). It's something Ive wanted to implement but I can't currently provide a timeline of when I will get to it.

INSERT INTO db.points (SELECT name, ST_AsText(ST_GeomAsHEXEWKB(pt)) AS pt FROM my_other_source);

This doesn't look right to me but if it work I guess it works? The way I'd go about it is to first convert to WKB on the DuckDB side (with ST_AsWKB(geom)::BLOB) and then convert back from WKB on the postgis side (ST_GeomFromWKB(blob))

However, I'm not sure if SRID is preserved this way?

DuckDB spatial does not store or track the SRID in geometries, so it doesn't matter either way, you need to set the SRID postgis-side regardless.

[fvankrieken]

Seems like this is fixed by this duckdb/duckdb-postgres#291 which was just merged

Parallel issue in postgres extension repo: duckdb/duckdb-postgres#264

[CGenie]

Splendid, thank you!