Working with VECTORHALF Type
In This Topic
dotConnect for PostgreSQL provides support for creating and working with the VECTORHALF type for .NET 6 and later.
This article shows how to create tables with vector columns, insert and retrieve vector data, and perform operations such as equality checks, parsing, and finding nearest vectors using cosine distance.
Creating a Table with a Vector Column
To create a table with a VECTORHALF vector column, use the following SQL statement:
CREATE TABLE documents_vector (
id SERIAL PRIMARY KEY,
embedding halfvec(3) -- A 3-dimensional vector
);
To view the table data, run the following query:
SELECT * FROM documents_vector;
Establishing a Database Connection
Create an instance of the PgSqlConnection class and open the connection.
PgSqlConnection conn = new PgSqlConnection(
"User Id=test;Host=test;Port=1111;Database=test;Password=test;");
conn.Open();
Inserting Data into the Table
Create an instance of the PgSqlCommand class associated with the PgSqlConnection object and specify the SQL statement.
PgSqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO documents_vector (embedding) VALUES (:embedding);"
Adding Parameters
Assign a value to the parameter. The value must be a PgSqlHalfVector created from a List<Half> whose length matches the vector dimensionality defined in the table.
cmd.Parameters.AddWithValue("embedding", new PgSqlHalfVector(new List<Half> { (Half)0.02579, (Half)0.0078, (Half)0.02347 }));
After setting up the command and parameters, execute it using the ExecuteNonQuery method.
Retrieving Data
To retrieve vector data, use the GetPgSqlHalfVector method.
while (reader.Read()) {
PgSqlHalfVector vector = reader.GetPgSqlHalfVector(0);
}
Working with PgSqlHalfVector
This section describes common operations supported by the PgSqlHalfVector type.
Checking Equality
To check if two vectors are equal, use the == operator.
PgSqlHalfVector vector1 = new PgSqlHalfVector(new List<Half> { (Half)0.02579, (Half)0.0078, (Half)0.02347 });
PgSqlHalfVector vector2 = new PgSqlHalfVector(new List<Half> { (Half)0.02579, (Half)0.0078, (Half)0.02347 });
if (vector1 == vector2)
Console.WriteLine("vector1 and vector2 are the same");
Checking Inequality
To check if two vectors are not equal, use the != operator.
PgSqlHalfVector vector1 = new PgSqlHalfVector(new List<Half> { (Half)0.02579, (Half)0.0078, (Half)0.02347 });
PgSqlHalfVector vector2 = new PgSqlHalfVector(new List<Half> { (Half)0.04579, (Half)0.0678, (Half)0.01347 });
if (vector1 != vector2)
Console.WriteLine("vector1 and vector2 are not the same");
Parsing from String
Using TryParse
To safely convert a string to a vector, use the TryParse method.
PgSqlHalfVector vector = new PgSqlHalfVector(new List<Half> { (Half)0.02579, (Half)0.0078, (Half)0.02347 });
string stringVector = "[1,2,3]";
if(PgSqlHalfVector.TryParse(stringVector, out vector)) {
Console.WriteLine("You can parse stringVector");
}
Using Parse
To directly convert a string to a vector, use the Parse method.
string stringVector = "[1,2,3]";
PgSqlHalfVector newVector = PgSqlHalfVector.Parse(stringVector);
Converting Vector to String
To convert a vector to its string representation, use the ToString method.
PgSqlHalfVector vector = new PgSqlHalfVector(new List<Half> { (Half)0.02579, (Half)0.0078, (Half)0.02347 });
string stringVector = vector.ToString();
Getting Hash Code
To retrieve the hash code of a vector, use the GetHashCode method.
PgSqlHalfVector vector = new PgSqlHalfVector(new List<Half> { (Half)0.02579, (Half)0.0078, (Half)0.02347 });
int hashCode = vector.GetHashCode();
Finding Nearest Vectors Using Cosine Distance
To find the nearest vectors using cosine distance, order the results using the <=> operator.
string query = "SELECT embedding FROM public.documents_vector ORDER BY embedding <=> '[0.02579,0.0078,0.02347'] LIMIT 10";
PgSqlCommand pgSqlCommand = new PgSqlCommand(query, conn);
var reader = pgSqlCommand.ExecuteReader();
while (reader.Read()) {
PgSqlHalfVector vector = reader.GetPgSqlHalfVector(0);
}