Supabase Vector Store Issue

[lhyphendixon]

I’m having the same issue as the URL below. I’m using the Supabase Vector Store and am trying to use it to access embeddings in my ‘messages’ table, but no matter what I do, it searches the ‘documents’ table.

Similar to this case: [BUG REPORT] - Supabase: Load

I was getting results back from my query for messages, but they were from the documents table, so I renamed the documents table for troubleshooting purposes and am now getting one error or another related to the documents table not being there.

I’ve verified my function(match_messages) probably a dozen times and have combed through everything looking for references to the ‘documents’ table and am coming up short.

What is the error message (if any)?

Currently:
ERROR: Error searching for documents: 42804 structure of query does not match function result type Returned type double precision does not match expected type jsonb in column 3.

Or

ERROR: Error searching for documents: 42P01 relation “documents” does not exist null

Depending on which method I try.

Please share your workflow

Share the output returned by the last node

Error in sub-node ‘Supabase Vector Store’
Error searching for documents: 42P01 relation “documents” does not exist null Open node

Information on your n8n setup

• n8n version: Cloud
• Database (default: SQLite): Supabase

Also, here’s my Supabase Function:

BEGIN
  RETURN QUERY
  SELECT
    messages.id,
    messages.content,
        1 - (messages.embedding <-> query_embedding) as similarity  -- Use the <-> operator
  FROM messages
  WHERE messages.metadata @> filter
  ORDER BY messages.embedding <-> query_embedding  -- Use the <-> operator
  LIMIT match_count;
END;

Any help would be super appreciated.

[n8n]

It looks like your topic is missing some important information. Could you provide the following if applicable.

• n8n version:
• Database (default: SQLite):
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app):
• Operating system:

[EmeraldHerald]

Hi @lhyphendixon Sorry it’s taken so long to get back to you!

Admittedly, when @oleg is back he may have more to help you with here, as I’m a novice when it comes to Supabase

When using your provided workflow, I’m able to get it to successfully run after I used the specific template in Supabase to create a SQL query that enables pgvector extension when creating a table. Perhaps you can take a look at this query and see if anything might be missing in your configuration that you might need?

-- Enable the pgvector extension to work with embedding vectors
create extension vector;

-- Create a table to store your documents
create table documents (
  id bigserial primary key,
  content text, -- corresponds to Document.pageContent
  metadata jsonb, -- corresponds to Document.metadata
  embedding vector(1536) -- 1536 works for OpenAI embeddings, change if needed
);

-- Create a function to search for documents
create function match_documents (
  query_embedding vector(1536),
  match_count int DEFAULT null,
  filter jsonb DEFAULT '{}'
) returns table (
  id bigint,
  content text,
  metadata jsonb,
  embedding jsonb,
  similarity float
)
language plpgsql
as $$
#variable_conflict use_column
begin
  return query
  select
    id,
    content,
    metadata,
    (embedding::text)::jsonb as embedding,
    1 - (documents.embedding <=> query_embedding) as similarity
  from documents
  where metadata @> filter
  order by documents.embedding <=> query_embedding
  limit match_count;
end;
$$;

[lhyphendixon]

Thanks @EmeraldHerald Yea, I got that part to work as well. The documents query and retrieval function worked properly. It’s just that when I created an exact clone of the query, function and the table for ‘messages’, that one continues to search documents almost like it’s hardcoded in somewhere I can’t see.