Working with VECTORFLOAT Type
In This Topic
dotConnect for PostgreSQL provides support for creating and working with the VECTORFLOAT type.
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 VECTORFLOAT vector column, use the following SQL statement:
CREATE TABLE documents_vector (
id SERIAL PRIMARY KEY,
embedding vector(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 PgSqlFloatVector created from a List<float> whose length matches the vector dimensionality defined in the table.
cmd.Parameters.AddWithValue("embedding", new PgSqlFloatVector(new List<float> { -0.02578710f, 0.00780120f, -0.02346830f }));
After setting up the command and parameters, execute it using the ExecuteNonQuery method.
Retrieving Data
To retrieve vector data, use the GetPgSqlFloatVector method.
while (reader.Read()) {
PgSqlFloatVector vector = reader.GetPgSqlFloatVector(0);
}
Working with PgSqlFloatVector
This section describes common operations supported by the PgSqlFloatVector type.
Checking Equality
To check if two vectors are equal, use the == operator.
PgSqlFloatVector vector1 = new PgSqlFloatVector(new List<float> { -0.02578710f, 0.00780120f, -0.02346830f });
PgSqlFloatVector vector2 = new PgSqlFloatVector(new List<float> { -0.02578710f, 0.00780120f, -0.02346830f });
if (vector1 == vector2)
Console.WriteLine("vector1 and vector2 are the same");
Checking Inequality
To check if two vectors are not equal, use the != operator.
PgSqlFloatVector vector1 = new PgSqlFloatVector(new List<float> { -0.02578710f, 0.00780120f, -0.02346830f });
PgSqlFloatVector vector2 = new PgSqlFloatVector(new List<float> { -0.04578710f, 0.00680120f, -0.02146830f });
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.
PgSqlFloatVector vector = new PgSqlFloatVector(new List<float> { -0.02578710f, 0.00780120f, -0.02346830f });
string stringVector = "[1,2,3]";
if(PgSqlFloatVector.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]";
PgSqlFloatVector newVector = PgSqlFloatVector.Parse(stringVector);
Converting Vector to String
To convert a vector to its string representation, use the ToString method.
PgSqlFloatVector vector = new PgSqlFloatVector(new List<float> { -0.02578710f, 0.00780120f, -0.02346830f });
string stringVector = vector.ToString();
Getting Hash Code
To retrieve the hash code of a vector, use the GetHashCode method.
PgSqlFloatVector vector = new PgSqlFloatVector(new List<float> { -0.02578710f, 0.00780120f, -0.02346830f });
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.029760782,0.02229251,-0.027655067]' LIMIT 10";
PgSqlCommand pgSqlCommand = new PgSqlCommand(query, conn);
var reader = pgSqlCommand.ExecuteReader();
while (reader.Read()) {
PgSqlFloatVector vector = reader.GetPgSqlFloatVector(0);
}