42804 Error on Supabase db and was just working

[GSI-alex]

I had gotten the Supabase vector store working with one ai Agent, and seemed to be testing ok via chat prompt when adding more (though i may be doing it ineffciently—another issue, see screen 1).

The issue is when i introduce a new tabel via the reddit scraper automation in bottom half of screen 1. It was working fine but i struggled a bit with getting the data to be inserted into DB, and may not actually be being input correct (see screen 5).

After hours of struggling and using msot adanced ai models to assist (latest open ai, gem 2.5, etc), its very possible i messed up a good thing as the tables were “working” without issue before introduicng reddit table.

If anyoone need additonal info i can provide. THanks!!!

Describe the problem/error/question

• Wanted: n8n search on Supabase table reddit. For ideas.
• Problem Was: n8n gave error. Search failed.
• Symptom: Error 42804. Said Returned type uuid does not match expected type bigint in column 1.
• Thought Was: Supabase function (match_documents) defined wrong?
• Function’s RETURNS TABLE part? Showed id bigint?
• But my table id? It’s uuid. That’s the mismatch?
• Tried Fix 1: Edited function SQL in Supabase. Changed bigint to uuid.
• Symptom: Then got new error? 42601 syntax. Did I paste wrong? Only part of it?
• Tried Fix 2: Used full CREATE OR REPLACE FUNCTION command. Like, the whole thing.
• Problem: Oops? Template used documents table name. Mine’s reddit. So, ran wrong code? Made function worse?
• Tried Fix 3: Used correct CREATE OR REPLACE FUNCTION. Full code this time. Table reddit. Fix id uuid. Ran it.
• Symptom: Still broken?? n8n still showed same 42804 error (uuid vs bigint). Still? Why?
• Tested: Ran function directly in Supabase editor. (SELECT * FROM match_documents(...)).
• Symptom: Supabase itself gave the same 42804 error (uuid vs bigint). Right there in Supabase.
• Means?: Function fix (CREATE OR REPLACE) didn’t stick? Failed when I ran it?
• Question: So, needed run the correct CREATE OR REPLACE FUNCTION (with reddit and id uuid) again? Had make sure Supabase said “Success”?

What is the error message (if any)?

Please share your workflow

Isolated chat (#screen 2 )

reddit part of flow, this is proably where things get messed up for table (reduced in size)
}

Share the output returned by the last node

see screen shots

Information on your n8n setup

• latest version of n8n
  -PostgreSQL 15.8 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
• n8n EXECUTIONS_PROCESS setting (default: own, main):
  -cloud
  -mac os 15.3 sequoia

[GSI-alex]

screen 6

[GSI-alex]

not sure if images werent working previousy, but ive updated post.

bump

[GSI-alex]

[GSI-alex]

Just as an update, the function for where i was inserting data into the reddit table was not specified as “match_reddit” but as “match_documents”—which was for another table.

GPT provided some fixes for me illl not mention here that came via sql in SB…

The good news is i can retrieve data from the reddit table via my chat now, but Im still experiencing the 42804 error code.

If anyone has any suggestions, let me know, otherwise ill update when i can self-resolve…

[GSI-alex]

Further follow up:

The error code seems to be because ofa mmistmatch in some of my tales using UUID, another text as the key, and another (latest–the reddit one) is now bigint.

ive unified the tables via SQL to use BigInt… original error code 42804 resolved, now addressing some other errors that came up since testng via chat trigger:

Error searching for documents: 42703 column documents.embedding does not exist null

I will update when resolved, but again if anoyone reading this has a clue…drop me a hint.

[GSI-alex]

OK thanks for the help n8n team and community. I resolved the issue on my own time.

Summary after unfying Key Id to BigInt type, i needed to resolve the “match_documents” type search functions.

Deleted all associated functions, ran following sql code for each table, eg match_articles snippet:

-- 1. Add an embedding column if not present
ALTER TABLE articles_json
ADD COLUMN IF NOT EXISTS embedding vector(1536);

-- 2. Drop old function if needed
DROP FUNCTION IF EXISTS match_articles(vector(1536), int, jsonb);

-- 3. Create or replace the vector search function
CREATE OR REPLACE FUNCTION match_articles(
  query_embedding vector(1536),
  match_count int DEFAULT 10,
  filter jsonb DEFAULT '{}'
)
RETURNS TABLE (
  id bigint,
  content text,
  metadata jsonb,
  similarity float
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    a.id,
    a.content,
    a.metadata,
    1 - (a.embedding <=> query_embedding) AS similarity
  FROM articles_json a
  WHERE a.metadata @> filter
  ORDER BY a.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;

[GSI-alex]

works fine now. thanks again everyone for the help!