使用SQLAlchemy和pgVector实现PostgreSQL混合搜索时遇操作符错误

错误根源 #

报错提示operator does not exist: record <=> vector，本质是SQLAlchemy传递向量参数时，没有自动识别为PostgreSQL的vector类型，数据库把传入的参数当成了record类型，导致找不到对应的余弦距离运算符<=>。

解决方案 #

方案1：SQL语句中显式类型转换 #

直接在查询语句里给参数加上::vector强制类型转换，让数据库明确识别参数类型：

修改后的query_db函数SQL部分：

def query_db(
        image_encoding,
        image_search_weight,
        keyword_encoding,
        keyword_search_weight,
    ):
        search_query = text(
            """
                SELECT *, 
                ((:image_encoding::vector <=> image_vector) * :image_search_weight + (:keyword_encoding::vector <=> keyword_vector) * :keyword_search_weight) 
                AS vector_sum
                FROM project_images
                ORDER BY vector_sum
                LIMIT 50
            """
        )

        params = {
            "image_encoding": image_encoding,
            "image_search_weight": image_search_weight,
            "keyword_encoding": keyword_encoding,
            "keyword_search_weight": keyword_search_weight,
        }

        with session_class() as session:
            result = session.execute(search_query, params)
            return result

方案2：用SQLAlchemy类型绑定参数 #

如果方案1无效，可以通过SQLAlchemy的cast函数，在传递参数时指定向量类型：

from sqlalchemy import cast
from pgvector.sqlalchemy import Vector

def query_db(
        image_encoding,
        image_search_weight,
        keyword_encoding,
        keyword_search_weight,
    ):
        search_query = text(
            """
                SELECT *, 
                ((:image_encoding <=> image_vector) * :image_search_weight + (:keyword_encoding <=> keyword_vector) * :keyword_search_weight) 
                AS vector_sum
                FROM project_images
                ORDER BY vector_sum
                LIMIT 50
            """
        )

        params = {
            "image_encoding": cast(image_encoding, Vector(512)),
            "image_search_weight": image_search_weight,
            "keyword_encoding": cast(keyword_encoding, Vector(768)),
            "keyword_search_weight": keyword_search_weight,
        }

        with session_class() as session:
            result = session.execute(search_query, params)
            return result

额外验证步骤 #

1. 确认vector扩展已正确安装：
   执行SQL查询验证：

SELECT * FROM pg_extension WHERE extname='vector';

如果返回结果为空，重新执行CREATE EXTENSION vector;（注意不需要IF NOT EXISTS，确保执行成功）。
2. 检查传入的向量参数格式：确保image_encoding和keyword_encoding是Python列表类型的数值数组（如[0.1, 0.2, -0.3,...]），不能是字符串或其他格式。

内容的提问来源于stack exchange，提问作者Shawn Wei Chew