Unimplemented type for cast (BLOB -> GEOMETRY)

[missinglink]

"@duckdb/node-api": "^1.3.2-alpha.25"

Hi,

I'm running into an issue reading a GEOMETRY field from one table and inserting it into another table using the appender api.

The error is: Unimplemented type for cast (BLOB -> GEOMETRY)

Below is a reproduction, I'm using app.appendValue(x, DuckDBBlobType.create('GEOMETRY')) without success.

Is this something which is currently possible?

import { DuckDBBlobType, DuckDBBlobValue, DuckDBInstance } from "@duckdb/node-api"

const instance = await DuckDBInstance.create(':memory:')
const conn = await instance.connect()

// install spatial extension
await conn.run('INSTALL spatial; LOAD spatial')

// create two tables, src & dst
await conn.run('CREATE TABLE src (geom GEOMETRY NOT NULL)')
await conn.run('CREATE TABLE dst (geom GEOMETRY NOT NULL)')

// insert into src
await conn.run('INSERT INTO src (geom) VALUES (ST_Point(1, 1))')

// read BLOB from src
const reader = await conn.runAndReadAll('SELECT geom FROM src')
const rows = reader.getRows() as DuckDBBlobValue[][]

// append BLOB to dst
const app = await conn.createAppender('dst')
// app.appendBlob(rows[0][0].bytes)
app.appendValue(rows[0][0], DuckDBBlobType.create('GEOMETRY'))
app.endRow()
app.closeSync()

node_bindings_1.default.append_value(this.appender, (0, createValue_1.createValue)(type ? type : (0, typeForValue_1.typeForValue)(value), value));
                                ^

Error: Failed to cast value: Unimplemented type for cast (BLOB -> GEOMETRY)

Ideally I would like to use the WKB_BLOB type for compatibility with external tables such as parquet and GEOMETRY for copying within DuckDB.

[jraymakers]

It looks like the DuckDB C API (upon which Node Neo is based) doesn't support appending extension types value-by-value. However, it does work if you append data chunks (which is more efficient anyway):

// read BLOB from src
const result = await conn.run('SELECT geom FROM src')
const chunk = await result.fetchChunk()

// append BLOB to dst
const app = await conn.createAppender('dst')
app.appendDataChunk(chunk)
app.closeSync()

console.log((await conn.runAndReadAll('from dst')).getRowObjectsJson())

If you want to append data in a different form than what is output from the source table, you can create your own data chunk:

// read BLOB from src
const reader = await conn.runAndReadAll('SELECT geom FROM src')
const rows = reader.getRows() as DuckDBBlobValue[][]

// append BLOB to dst
const app = await conn.createAppender('dst')
const chunk = DuckDBDataChunk.create([DuckDBBlobType.create('GEOMETRY')])
chunk.setColumns([
  [rows[0][0]],
])
app.appendDataChunk(chunk);
app.closeSync()

console.log((await conn.runAndReadAll('from dst')).getRowObjectsJson())