search

zio / zio-quill

Compile-time Language Integrated Queries for Scala
https://zio.dev/zio-quill
Apache License 2.0
2.14k stars 347 forks source link

Pgvector can be written but can't be read #3018

Open bradovitt opened 2 months ago

bradovitt commented 2 months ago

Version: "io.getquill" % "quill-jdbc-zio_3" % "4.6.0" Module: quill-jdbc Database: PostgreSQL

If I use the Vector[Double] type I can write a pgvector to the database, but if I try to read it throws the exception "org.postgresql.util.PSQLException: No results were returned by the query," even though the generated query works in PSQL. If I remove the vector field from the schema it works from the client and returns all the rows.

The current situation is you can provide a vector in your query, but you can't extract a vector with a select statement or it will throw an exception.

Steps to reproduce the behavior

val ctx = new SqlMirrorContext(PostgresDialect, SnakeCase)

import ctx._

case class VectorTable(vector: Vector[Double])

// this will succeed
def insert(vector: Vector[Double]) = ctx.run(query[VectorTable].insert(_.vector -> lift(vector)))

// this will throw an exception
val read = ctx.run(query[VectorTable])

This official postgres library provides a solution: https://github.com/pgvector/pgvector-java, but I am so far unable to integrate it into Quill, for which my production is dependent so I will be as helpful as I can to get this fixed.

bradovitt commented 2 months ago

Many people are building AI into their apps, so many that I'd go so far as to say that vector support is almost as important as date/time support. I'm willing to implement this fix myself, but I don't understand the code base. If someone is willing to give me some direction to do it myself, that would be greatly appreciated.