var dataSourceBuilder = new NpgsqlDataSourceBuilder(connString);
dataSourceBuilder.UseVector();
await using var dataSource = dataSourceBuilder.Build();
var conn = dataSource.OpenConnection();
Enable the extension
await using (var cmd = new NpgsqlCommand("CREATE EXTENSION IF NOT EXISTS vector", conn))
{
await cmd.ExecuteNonQueryAsync();
}
conn.ReloadTypes();
Create a table
await using (var cmd = new NpgsqlCommand("CREATE TABLE items (id serial PRIMARY KEY, embedding vector(3))", conn))
{
await cmd.ExecuteNonQueryAsync();
}
Insert a vector
await using (var cmd = new NpgsqlCommand("INSERT INTO items (embedding) VALUES ($1)", conn))
{
var embedding = new Vector(new float[] { 1, 1, 1 });
cmd.Parameters.AddWithValue(embedding);
await cmd.ExecuteNonQueryAsync();
}
Get the nearest neighbors
await using (var cmd = new NpgsqlCommand("SELECT * FROM items ORDER BY embedding <-> $1 LIMIT 5", conn))
{
var embedding = new Vector(new float[] { 1, 1, 1 });
cmd.Parameters.AddWithValue(embedding);
await using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine((Vector)reader.GetValue(0));
}
}
}
Add an approximate index
await using (var cmd = new NpgsqlCommand("CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100)", conn))
{
await cmd.ExecuteNonQueryAsync();
}
Use vector_ip_ops for inner product and vector_cosine_ops for cosine distance
SqlMapper.AddTypeHandler(new VectorTypeHandler());
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connString);
dataSourceBuilder.UseVector();
await using var dataSource = dataSourceBuilder.Build();
var conn = dataSource.OpenConnection();
Enable the extension
conn.Execute("CREATE EXTENSION IF NOT EXISTS vector");
conn.ReloadTypes();
Define a class
public class Item
{
public int Id { get; set; }
public Vector? Embedding { get; set; }
}
Create a table
conn.Execute("CREATE TABLE items (id serial PRIMARY KEY, embedding vector(3))");
Insert a vector
var embedding = new Vector(new float[] { 1, 1, 1 });
conn.Execute(@"INSERT INTO items (embedding) VALUES (@embedding)", new { embedding });
Get the nearest neighbors
var embedding = new Vector(new float[] { 1, 1, 1 });
var items = conn.Query<Item>("SELECT * FROM items ORDER BY embedding <-> @embedding LIMIT 5", new { embedding });
foreach (Item item in items)
{
Console.WriteLine(item.Embedding);
}
Add an approximate index
conn.Execute("CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100)");
// or
conn.Execute("CREATE INDEX ON items USING hnsw (embedding vector_l2_ops)");
Use vector_ip_ops for inner product and vector_cosine_ops for cosine distance
var embedding = new Vector(new float[] { 1, 1, 1 });
var items = await ctx.Items.FromSql($"SELECT * FROM items ORDER BY embedding <-> {embedding} LIMIT 5").ToListAsync();
foreach (Item item in items)
{
if (item.Embedding != null)
{
Console.WriteLine(item.Embedding);
}
}
