duckdb / duckdb/duckdb-sqlite

Getting errors when creating a VIRTUAL table for Full Text Search with SQLite extension

Open
#128 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
291
Forks
45
Avg merge
10h 13m
Merged PRs (30d)
18

Description

### What happens?

Getting an error when using SQLite extension to create a full text search table in SQLite through VIRTUAL table mechanism
code example
```
fts_table_name = 'cik_md'
if any(fts_table_name in table for table in tables_duckdb):
duckdb_con.execute(f"""
CREATE VIRTUAL TABLE {SQLITE_DB_NAME}.site_search_fts USING fts5(
code,
name,
category UNINDEXED,
updated_at UNINDEXED,
tokenize='unicode61 remove_diacritics 2'
);
""")

# Populate the FTS table with data from the 'cik_name' table
duckdb_con.execute(f"""
INSERT INTO {SQLITE_DB_NAME}.site_search_fts (code, name, category)
SELECT cik AS code, cik_name AS name, 'investor' AS category
FROM {SQLITE_DB_NAME}.{fts_table_name};
""")
```

** The error**

```
---------------------------------------------------------------------------
ParserException Traceback (most recent call last)
File :36

ParserException: Parser Error: syntax error at or near "VIRTUAL"
```

### To Reproduce

Try creating a full text search table in SQLite with Duckdb's SQLite extension

### OS:

mac os

### SQLite Version:

3.47.2

### DuckDB Version:

1.1.3

### DuckDB Client:

python

### Full Name:

kuatroka

### Affiliation:

kuatroka

### Have you tried this on the latest `main` branch?

- [X] 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?

- [X] I agree

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.