Can't create table with GEOMETRY type using CTAS

[Maxxen]

What happens?

Original issue reported on discord.

This doesn't work.

CREATE TABLE postgres_db.my_schema.my_table AS 
    SELECT ST_Point (.22, 1.22) AS geom
-- Failed to copy data: ERROR:  Unknown WKB type (0)! Full WKB type number was (0)

But this does

CREATE TABLE postgres_db.my_schema.my_table (geom GEOMETRY);
INSERT INTO postgres_db.my_schema.my_table VALUES (ST_Point (.22, 1.22))

My guess is that during inserts we map the Postgres geometry type to WKB_BLOB, which spatial then auto-casts to from GEOMETRY. But this doesn't work during CTAS because no implicit cast get inserted.

To Reproduce

CREATE TABLE postgres_db.my_schema.my_table AS
SELECT ST_Point (.22, 1.22) AS geom

OS:

MacOS

PostgreSQL Version:

14

DuckDB Version:

1.2.0

DuckDB Client:

CLI

Full Name:

Max Gabrielsson

Affiliation:

DuckDB Labs

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