Example 3:

-- Create a new traditional table database examples;
create table ifx_sample_tab3 (
ident bigint,
embedding_data lvector_embedding, text_data lvarchar,
metadata lvarchar, primary key (ident)
);
-- Generate some real embeddings and insert them into our tableinsert into ifx_sample_tab3 values (
1,
lvector_embed('This text is about squirrels'), 'This text is about squirrels',
'This metadata will not be used in this example'
);
insert into ifx_sample_tab3 values (
2,
lvector_embed('This text is about car racing'), 'This text is about car racing',
'This metadata will not be used in this example'
);
insert into ifx_sample_tab3 values (
3,
lvector_embed('This text is about architecture'), 'This text is about architecture',
'This metadata will not be used in this example'
);
insert into ifx_sample_tab3 values
(
4,
lvector_embed('This text is about wrestling'), 'This text is about wrestling',
'This metadata will not be used in this example'
);
insert into ifx_sample_tab3 values (
5,
lvector_embed('This text is about sound'), 'This text is about sound',
'This metadata will not be used in this example'
);

-- Say we want to find the text most relevant to the question, "Tell me about building design". First, we have to create a lance table that we can use to perform this search. We indicate that the vectors we will be storing have 1536 dimensions, because this is the format of the vectors returned by OpenAI:

EXECUTE PROCEDURE lvector_create_table('sample_tab3', 1536);

-- Now we synchronize this lance table with our traditional table:

EXECUTE PROCEDURE lvector_bulk_sync('ifx_sample_tab3', 'sample_tab3', 'ident', 'embedding_data');

-- Now we can perform a true vector search:

SELECT text_data

FROM ifx_sample_tab3 WHERE ident IN

(SELECT * FROM TABLE(lvector_id_search('sample_tab3', lvector_embed('Tell me about building design'), 1)));

You should see that the results of this vector search are: text_data This text is about architecture

How about this one:

SELECT text_data

FROM ifx_sample_tab3 WHERE ident IN

(SELECT * FROM TABLE(lvector_id_search('sample_tab3', lvector_embed('Tell me about cute rodents'), 1)));

Expected results:

text_data This text is about squirrels

Or maybe this one:

SELECT text_data

FROM ifx_sample_tab3 WHERE ident IN

(SELECT * FROM TABLE(lvector_id_search('sample_tab3', lvector_embed('Tell me about sports'), 2)));

Expected results:

text_data This text is about car racing text_data This text is about wrestling