column "vector_embedding" is of type vector but expression is of type vector[]

[frieda-huang]

Hi, I have the following schema to store multiple vectors, but I kept getting error telling me the mismatch between column type and the expression type.

class Embedding(Base):
    __tablename__ = "embedding"

    id: Mapped[int] = mapped_column(primary_key=True)
    vector_embedding: Mapped[list[np.array]] = mapped_column(ARRAY(Vector(128)))
    dim: Mapped[int] = mapped_column(Integer, default=128)
    embedding_type: Mapped[str] = mapped_column(String, default="multi-vector")

    page_id: Mapped[int] = mapped_column(ForeignKey("page.id"))

    page: Mapped[Page] = relationship(back_populates="embeddings")

Embedding(vector_embedding=[np.array(e) for e in embedding], page_id=page_id)

Error

sqlalchemy.exc.ProgrammingError: (psycopg.errors.DatatypeMismatch) column "vector_embedding" is of type vector but expression is of type vector[] LINE 1: ..._embedding, dim, embedding_type, page_id) VALUES ($1::VECTOR... ^ HINT: You will need to rewrite or cast the expression. [SQL: INSERT INTO embedding (vector_embedding, dim, embedding_type, page_id) VALUES (%(vector_embedding)s::VECTOR(128)[], %(dim)s::INTEGER, %(embedding_type)s::VARCHAR, %(page_id)s::INTEGER) RETURNING embedding.id]

[ankane]

Hi @frieda-huang, it sounds like the vector_embedding column was created as a vector type instead of vector[] (you can check this in Postgres directly). I added a test case in the commit above to confirm that SQLAlchemy creates it correctly.

[frieda-huang]

Hi @frieda-huang, it sounds like the vector_embedding column was created as a vector type instead of vector[] (you can check this in Postgres directly). I added a test case in the commit above to confirm that SQLAlchemy creates it correctly.

Thank you so much! It was indeed the issue!!