google/gemini-3.1-pro-preview · d2_pgvector · rep 2

env | grep SUPABASE

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>

psql $SUPABASE_DB_URL -c 'SELECT * FROM public.documents;'

 id |  title  |  embedding_json  
----+---------+------------------
  1 | alpha   | [10, 0.5, 0, 0]
  2 | bravo   | [0.9, 0.3, 0, 0]
  3 | delta   | [20, 60, 0, 0]
  4 | echo    | [0, 1, 0, 0]
  5 | foxtrot | [-5, 0.1, 0, 0]
  6 | golf    | [0.1, 0.1, 3, 4]
(6 rows)

cat << 'EOF' > run.sql
CREATE EXTENSION IF NOT EXISTS vector;
ALTER TABLE public.documents ADD COLUMN embedding vector(4);
UPDATE public.documents SET embedding = translate(embedding_json::text, '[]', '{}')::vector;
SELECT embedding FROM public.documents;
EOF
psql $SUPABASE_DB_URL -f run.sql

CREATE EXTENSION
ALTER TABLE
 embedding 
-----------
 
 
 
 
 
 
(6 rows)

psql:run.sql:3: ERROR:  invalid input syntax for type vector: "{10, 0.5, 0, 0}"
DETAIL:  Vector contents must start with "[".

cat << 'EOF' > run.sql
SELECT embedding_json::text FROM public.documents;
EOF
psql $SUPABASE_DB_URL -f run.sql

  embedding_json  
------------------
 [10, 0.5, 0, 0]
 [0.9, 0.3, 0, 0]
 [20, 60, 0, 0]
 [0, 1, 0, 0]
 [-5, 0.1, 0, 0]
 [0.1, 0.1, 3, 4]
(6 rows)

cat << 'EOF' > run.sql
UPDATE public.documents SET embedding = (embedding_json::text)::vector;
SELECT embedding FROM public.documents;
EOF
psql $SUPABASE_DB_URL -f run.sql

UPDATE 6
   embedding   
---------------
 [10,0.5,0,0]
 [0.9,0.3,0,0]
 [20,60,0,0]
 [0,1,0,0]
 [-5,0.1,0,0]
 [0.1,0.1,3,4]
(6 rows)

cat << 'EOF' > run.sql
CREATE INDEX ON public.documents USING hnsw (embedding vector_cosine_ops);
CREATE OR REPLACE FUNCTION match_documents(
  query_embedding vector(4),
  match_count int DEFAULT null
)
RETURNS TABLE (
  id int,
  title text,
  distance float8
)
LANGUAGE sql
AS $$
  SELECT
    id,
    title,
    (embedding <=> query_embedding)::float8 AS distance
  FROM public.documents
  ORDER BY embedding <=> query_embedding
  LIMIT match_count;
$$;
GRANT EXECUTE ON FUNCTION match_documents(vector(4), integer) TO anon;
GRANT EXECUTE ON FUNCTION match_documents(vector(4), integer) TO authenticated;
EOF
psql $SUPABASE_DB_URL -f run.sql

CREATE INDEX
CREATE FUNCTION
GRANT
GRANT

curl -s -X POST "${SUPABASE_URL}/rest/v1/rpc/match_documents" \
  -H "apikey: ${SUPABASE_ANON_KEY}" \
  -H "Authorization: Bearer ${SUPABASE_ANON_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"query_embedding": "[10, 0, 0, 0]", "match_count": 3}'

[{"id":1,"title":"alpha","distance":0.00124766112215535}, 
 {"id":2,"title":"bravo","distance":0.0513166893837236}, 
 {"id":3,"title":"delta","distance":0.683772233983162}]