Fts extension in DuckDB doesn't work

General Usage
,
1

The fts extension in DuckDB is finally installed on my windows machine after much appreciated help.
The first thing I need to do to use this extension is create an fts index.
Following the docs for this extension, I tried:

    DBInterface.execute(con, "PRAGMA create_fts_index('combined', 'UPRN', 'combo');")

but I get this error:

ERROR: LoadError: DuckDB.QueryException("Invalid Input Error: Cannot prepare multiple statements at once!")

Google found the same error message in this existing DuckDB issue relating to the Python DuckDB client. The answer given stated:

This seems to be an error with the Python client not being able to handle multiple statements in the same call to execute.

Is this also an issue for the Julia client? Is it possible to get fts to work?

2

You could try with Home · DBInterface.jl

This does seem unfortunate that this pragma creates multiple statements and doesn’t insulate the user from it given it looks like a single statement. It might qualify as a leaky abstraction and therefore bug in the FTS extension.

1 Like
3

Actually it looks like it was reported and fixed here: `PRAGMA create_fts_index(…)` doesn't work in Julia package · Issue #10892 · duckdb/duckdb · GitHub

Are you using a version that contains the purported fix?

1 Like
4

Looks like you need to use DuckDB.query


"""
Executes a SQL query within a connection and returns the full (materialized) result. 
The query function is able to run queries with multiple statements, unlike `DBInterface.execute`(@ref) which is only able to prepare a single statement.
"""
function query(con::DuckDB.Connection, sql::AbstractString)
1 Like
5

Thank you @frankier.