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

Question

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 : "[email protected]" -> "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

dshukertjr · Accepted Answer · 2022-10-06 12:57:29Z

2

It seems like there are no charindex function in Postgres, but we have strpos instead! In your case, your left function could be like this:

left(replace(email, '.', '-'), strpos(email, '@') - 1), strpos(email, '@')

answered Oct 6, 2022 at 12:57

dshukertjr

19.2k11 gold badges71 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related