Custom type converter for PostgreSQL pgvector - Need help with INSERT/UPDATE parameterization

ServiceStack.OrmLite PostgreSQL
1

Hi Mythz,

I’m implementing a custom type converter for PostgreSQL’s pgvector extension to store vector embeddings. The converter works for SELECT operations but fails on INSERT/UPDATE due to PostgreSQL’s type system requirements.

The Problem:

  • PostgreSQL’s pgvector type requires explicit ::vector casting in parameterized queries
  • OrmLite parameters are sent as text type
  • PostgreSQL won’t implicitly cast text to vector, causing: column is of type vector but expression is of type text

My PgVector Type:

public class PgVector
{
    public float[] Values { get; set; }
    
    public PgVector(float[] values)
    {
        Values = values;
    }
    
    public override string ToString()
    {
        if (Values == null || Values.Length == 0)
            return "[]";
        // Returns PostgreSQL vector format: "[0.1,0.2,0.3]"
        var formattedValues = Values.Select(v => v.ToString("G9", CultureInfo.InvariantCulture));
        return $"[{string.Join(",", formattedValues)}]";
    }
}

My Converter:

public class PgVectorConverter : OrmLiteConverter
{
    public override string ColumnDefinition => "vector";
    
    public override DbType DbType => DbType.Object;
    
    public override void InitDbParam(IDbDataParameter p, Type fieldType)
    {
        if (p is NpgsqlParameter npgsqlParam)
        {
            npgsqlParam.NpgsqlDbType = NpgsqlDbType.Unknown;
        }
    }

    public override string ToQuotedString(Type fieldType, object value)
    {
        if (value == null) return "NULL";
            
        var vector = value as PgVector;
        if (vector == null) return "NULL";
            
        return $"'{vector}'::vector";  // Works for raw SQL only
    }
    
    public override object ToDbValue(Type fieldType, object value)
    {
        if (value == null) return DBNull.Value;
            
        if (value is PgVector vector)
            return vector.ToString();  // Returns "[0.1,0.2,0.3]"
            
        return DBNull.Value;
    }
}

What Fails:

var result = new UrlScrapeResult 
{
    ContentVector = new PgVector(new float[] { 0.1f, 0.2f, 0.3f }),
    // other properties...
};

// This fails with "column is of type vector but expression is of type text"
await db.InsertAsync(result);

What Works (Raw SQL):

await db.ExecuteSqlAsync(
    "INSERT INTO url_scrape_result (content_vector) VALUES (@vector::vector)",
    new { vector = pgVector.ToString() });

Question:
Is there a way to make the converter properly handle the pgvector type so that standard OrmLite Insert/Update operations work? Specifically:

  1. Can I force the parameter to include the ::vector cast?
  2. Is there a different approach to parameter typing I should use?
  3. Any other converter methods I should override?

I’d prefer to use standard OrmLite operations rather than raw SQL for every vector operation.

Thanks for any guidance!

2

The only place to customize the DB Parameter type is in InitDbParam() where you’re setting it to NpgsqlDbType.Unknown. But doesn’t look like vector is a supported Npgsql type:

Are you using https://github.com/pgvector/pgvector-dotnet?

1 Like
3

Thank you! The pgvector-dotnet library worked perfectly. I was able to get everything working with a custom dialect provider approach.

Here’s what I ended up with in case it helps anyone else:

Custom Dialect Provider

public class PgVectorDialectProvider : PostgreSqlDialectProvider
{
    private static readonly object DataSourceLock = new object();
    private static NpgsqlDataSource? _sharedDataSource;
    private static string? _lastConnectionString;

    public static PgVectorDialectProvider Instance { get; } = new PgVectorDialectProvider();

    public override IDbConnection CreateConnection(string connectionString, Dictionary<string, string>? options)
    {
        // Ensure we have a pgvector-enabled data source
        var dataSource = GetOrCreateDataSource(connectionString);
        
        // Create connection from the data source
        var connection = dataSource.CreateConnection();
        
        // Let OrmLite wrap it if needed
        return connection;
    }

    private static NpgsqlDataSource GetOrCreateDataSource(string connectionString)
    {
        lock (DataSourceLock)
        {
            // If connection string changed or no data source exists, create new one
            if (_sharedDataSource == null || _lastConnectionString != connectionString)
            {
                // Dispose old data source if it exists
                _sharedDataSource?.Dispose();
                
                // Create new data source with pgvector support
                var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
                dataSourceBuilder.UseVector();
                _sharedDataSource = dataSourceBuilder.Build();
                _lastConnectionString = connectionString;
            }
            
            return _sharedDataSource;
        }
    }
}

Updated Converter

public class PgVectorNativeConverter : OrmLiteConverter
{
    public override string ColumnDefinition => "vector";
    
    public override object ToDbValue(Type fieldType, object value)
    {
        if (value == null)
            return DBNull.Value;
            
        if (value is PgVector pgVector)
        {
            // Convert to pgvector-dotnet's Vector type
            return new Vector(pgVector.Values);
        }
            
        return DBNull.Value;
    }

    public override object FromDbValue(Type fieldType, object value)
    {
        if (value == null || value is DBNull)
            return null;

        if (value is Vector vector)
        {
            return new PgVector(vector.ToArray());
        }

        return null;
    }
}

Registration

In your AppHost configuration:

// Use OrmLiteConnectionFactory with the custom dialect provider
services.AddSingleton<IDbConnectionFactory>(new OrmLiteConnectionFactory(
    connectionString, PgVectorDialectProvider.Instance));

And register the converter with the custom dialect:

// Register PgVector converter with our custom dialect provider
PgVectorDialectProvider.Instance.RegisterConverter<PgVector>(new PgVectorNativeConverter());

Results

All standard OrmLite operations now work seamlessly:

// INSERT - Works!
var id = await db.InsertAsync(new UrlScrapeResult { 
    ContentVector = new PgVector(embeddings) 
}, selectIdentity: true);

// SELECT - Works!
var result = await db.SingleByIdAsync<UrlScrapeResult>(id);
// result.ContentVector contains the vector data

// UPDATE - Works!
result.ContentVector = new PgVector(newEmbeddings);
await db.UpdateAsync(result);

With this setup, all the standard OrmLite operations work without needing any raw SQL or explicit casting. The pgvector-dotnet library handles the vector type registration through NpgsqlDataSource.UseVector().

Thanks again for the pointer to pgvector-dotnet - it was exactly what I needed!

1 Like
4

Awesome, thanks for the info! will look at putting it up on ormlite docs somewhere