Summary

Creating tables with geometry types is possible. Adding geometry columns to an existing table is not, at least not using the ALTER TABLE statement.

Reproducible example

This was the smallest example I could come up with that demonstrates this behavior.

INSTALL spatial;
LOAD spatial;

BEGIN TRANSACTION;
CREATE TABLE t1(id VARCHAR, pt VARCHAR);
CREATE TABLE t2(id VARCHAR, geom GEOMETRY);
COMMIT;

BEGIN TRANSACTION;
ALTER TABLE t1 ADD COLUMN geom GEOMETRY;
COMMIT;

--undo
BEGIN TRANSACTION;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
COMMIT;

-- verify that the geometry type is in the catalog:
SELECT COUNT(*) AS res_count FROM duckdb_types() WHERE type_name = 'GEOMETRY';

This results in a catalog error:

> Catalog Error: Type with name "GEOMETRY" is not in the catalog, but it exists in the spatial extension.
  Please try installing and loading the spatial extension: INSTALL spatial; LOAD spatial;

If the line with the ALTER TABLE statement is commented out, the above SQL will execute successfully, returning the expected value for res_count: 1.

Environment

DuckDB 0.9.2 on Windows 10, executing DuckDB SQL against an in-memory DB via the JDBC driver. I have not yet attempted to reproduce the issue in other environments.

Hi! Thanks for filing this issue. What version if DuckDB are you running?

Sorry for not including that context! I am running 0.9.2.

I cannot seem to reproduce this. Running the exact same script you provided works for me, both on DuckDB 0.9.2 and the latest dev. Im on macOS, but I don't see how this would be a windows issue.

I don't really understand what behavior you're trying to exhibit with the transactions, does a simple:

CREATE TABLE t1(id VARCHAR, pt VARCHAR);
ALTER TABLE t1 ADD COLUMN geom GEOMETRY;
INSERT INTO t1 VALUES (1, 'foo', ST_Point(1,2));
SELECT * FROM t1;

┌─────────┬─────────┬─────────────┐
│   id    │   pt    │    geom     │
│ varchar │ varchar │  geometry   │
├─────────┼─────────┼─────────────┤
│ 1       │ foo     │ POINT (1 2) │
└─────────┴─────────┴─────────────┘

Produce the same error?

I think a contributing factor may be one aspect I previously neglected to mention: I was using a memory-only DB. I get the same catalog error when running your example against an in-memory DB, but a different error when using an on-disk DB:

> Binder Error: table t1 has 3 columns but 1 values were supplied

Adapting your example slightly makes it execute successfully:

CREATE TABLE t1(id VARCHAR, pt VARCHAR);
ALTER TABLE t1 ADD COLUMN geom GEOMETRY;
INSERT INTO t1 VALUES (1, foo, ST_Point(1,2));
SELECT * FROM t1;


┌─────────┬─────────┬─────────────┐
│   id    │   pt    │    geom     │
│ varchar │ varchar │  geometry   │
├─────────┼─────────┼─────────────┤
│ 1       │ foo     │ POINT (1 2) │
└─────────┴─────────┴─────────────┘

Not sure if there are still other environment issues at play.

Right, my bad, I copy-pasted wrong from my terminal so my example was faulty. I've edited it above. Either way, Im running an in-memory database too. I can try to get my hand on a windows machine to test tomorrow.