Supabase triggers : ERROR: function charindex(unknown, text) does not exist (SQLSTATE 42883) (SQL)

Issue

This Content is from Stack Overflow. Question asked by Abhishek MG

I have two tables auth (this is the default one given by supabase) and profiles

when a new row is added in auth table I want a new row with the data added in auth table to be added in profiles tables as well

Profiles tables has 3 columns id,email,user_name where the user_name needs to be characters before “@” in an email , and any “.” needs to be replaced with “-” , example : “test.123@test.com” -> “test-123”

For this I have created a trigger in auth table with the following function

begin
    insert into public.profiles (id, email, user_name)
    select new.id, new.email,
           left(replace(new.email, '.', '-'), charindex('@', replace(new.email, '.', '-')) - 1);
return new;
end;

But now when a new row is added in auth table I get this error

ERROR: function charindex(unknown, text) does not exist (SQLSTATE 42883)

I tried changing charindex to strpos function but still getting error that strpos does not exist (SQLSTATE 42883)

Screenshot of error in logs when new row is added in auth

Having trouble in making charindex or strpos functions work in triggers , any help is much appreciated



Solution

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?