Upsert into vec0 tables?

[gospodinbodurov]

I tried the following query:

INSERT INTO vec_items(rowid, embedding) VALUES (?, ?) ON CONFLICT(rowid) DO UPDATE SET embedding = excluded.embedding;

and got:

UPSERT not implemented for virtual table "vec_items"

Is upsert supported in some form?

[asg017]

@gospodinbodurov thanks for the report! Initially I though UPSERT wasn't support for virtual tables, as mentioned in the SQLite docs:

UPSERT does not currently work for virtual tables.

But now I think that's outdated. Other docs mention a few other APIs that may help:

• SQLITE_VTAB_CONSTRAINT_SUPPORT in sqlite3_vtab_config()
• sqlite3_vtab_on_conflict()

So I'll try to include proper UPSERT support in an upcoming release!

[gospodinbodurov]

You are welcome.

P.S. Not sure whether this is the right place for asking, but where I can find the different supported distance metrics? I guess at the moment cosine and l2 are the only ones?

[asg017]

cosine, l1, and l2. is there any other you'd be interested in?

[gospodinbodurov]

At the moment I think the existing ones are good enough, but I am wondering how to use them - could not find their usage in the documentation.

P.S. I plan integrating this project in our platform and that's the reason I am asking about the upsert functionality and what distance functions are supported. If upsert will not be supported I will rebuild the whole database from scratch during updates.

[asg017]

docs are lacking but for vec0 tables you can specify a 'distance_metric=cosine' option after the vector column declaration, hoping to update the docs early next week with samples

i'll also try to get upsert support soon! dont think it'll take too long but i won't be at a computer for a few more days