psycopg 3 + pgvector: 'operator does not exist: vector <=> double precision[]' when the query embedding is a Python list, even though INSERT with the same list works
Cast the parameter in SQL (ORDER BY embedding <=> %s::vector LIMIT 10), or pass a numpy array (np.asarray(q, dtype=np.float32)) on a connection where register_vector(conn) has been called.
Why: psycopg 3 sends a Python list of floats as double precision[]. pgvector defines casts from double precision[], real[], integer[] and numeric[] to vector as assignment casts only (castcontext 'a' in pg_cast). An INSERT INTO t (embedding) VALUES (%s) therefore converts silently, but operator resolution for <=>, <-> and <#> does not apply assignment casts, so it fails with HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
register_vector() does not help with lists. In pgvector-python 0.2.5 it only registers dumpers for numpy.ndarray, so calling it on every connection, which is a common guess, doesn't change anything. A common way to hit this: calling .tolist() on a numpy embedding before passing it, which turns it back into a list.
Checked in psql (pgvector 0.8.7): ORDER BY embedding <=> ARRAY[1,2,3]::float8[] gives the error, and ORDER BY embedding <=> ARRAY[1,2,3]::float8[]::vector works.