Kysely•8mo ago

How to order by with embeddings using pgvector?

I'm trying to get this to work:

  const raw = db
    .selectFrom("embeddingChunks")
    .select(["id", "domainPathId"])
    .orderBy(sql`embedding::VECTOR <=> '[${embedding.toString()}]'`)
    .limit(5);

  const c = raw.compile();

  console.log({ c });

  const res = await raw.execute();

  const raw = db
    .selectFrom("embeddingChunks")
    .select(["id", "domainPathId"])
    .orderBy(sql`embedding::VECTOR <=> '[${embedding.toString()}]'`)
    .limit(5);

  const c = raw.compile();

  console.log({ c });

  const res = await raw.execute();

But I get this when executing:

error: invalid input syntax for type vector: "[$1]"
    ....
  length: 102,
  severity: 'ERROR',
  code: '22P02',
  detail: undefined,
  hint: undefined,
  position: '86',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'vector.c',
  line: '224',
  routine: 'vector_in'
}

error: invalid input syntax for type vector: "[$1]"
    ....
  length: 102,
  severity: 'ERROR',
  code: '22P02',
  detail: undefined,
  hint: undefined,
  position: '86',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'vector.c',
  line: '224',
  routine: 'vector_in'
}

When I run the compiled query manually in TablePlus it works. Any hints?

Solution:

You can't have parameters inside strings. https://kyse.link/?p=s&i=mSBxXrsodd1aLwlEKulE

Jump to solution

3 Replies

elitan•8mo ago

Here's the generated sql:

select "id", "domain_path_id" from "embedding_chunks" order by embedding::VECTOR <=> '[$1]' limit $2

select "id", "domain_path_id" from "embedding_chunks" order by embedding::VECTOR <=> '[$1]' limit $2

Inspired by: https://github.com/neondatabase/yc-idea-matcher/blob/main/src/app/api/idea/route.ts#L57

Solution

koskimas•8mo ago

You can't have parameters inside strings. https://kyse.link/?p=s&i=mSBxXrsodd1aLwlEKulE

elitan•7mo ago

Thanks that worked 🙏 Now I can continue the work for https://1177.ai 😄 lol had to come back to this one 😄 Thanks again @koskimas ❤️

Gaming

Programming

How to order by with embeddings using pgvector?