`vector[]` with pgx

[yuku]

I am currently using pgvector-go with the latest versions of sqlc and pgx. I wanted to perform a bulk insert of vectors and wrote the following SQL:

-- name: CreateEmbeddings :exec
INSERT INTO embeddings (id, embedding)
SELECT
  unnest(@ids::pg_catalog.bpchar[]) AS id,
  unnest(@embeddings::vector[]) AS vector;

However, this results in the following error:

unable to encode []pgvector.Vector{...} into text format for unknown type (OID xxxxxx)

Upon investigation, I found that the value of xxxxxx matches the result of SELECT to_regtype('vector[]')::oid. On the other hand, the pgxvector.RegisterTypes function in pgvector-go registers a codec for the OID obtained from SELECT to_regtype('vector')::oid:

pgvector-go/pgx/register.go

Lines 15 to 25 in 1ec55f1

	err := conn.QueryRow(ctx, "SELECT to_regtype('vector')::oid, to_regtype('halfvec')::oid, to_regtype('sparsevec')::oid").Scan(&vectorOid, &halfvecOid, &sparsevecOid)
	if err != nil {
	return err
	}
	if vectorOid == nil {
	return fmt.Errorf("vector type not found in the database")
	}
	tm := conn.TypeMap()
	tm.RegisterType(&pgtype.Type{Name: "vector", OID: *vectorOid, Codec: &VectorCodec{}})

I’m not very familiar with Postgres internals or the details of pgvector-go, but is it possible to add support for vector[] in pgx?

Any guidance or clarification would be greatly appreciated!

[ankane]

Hi @yuku, added support for arrays in the commit above. However, I'd recommend using pgx.CopyFromSlice for bulk inserts instead.