CODEWITHSUNDEEP
sqlpostgresqlsupabasesupabase-database
Abhishek MG
Abhishek MG

Reputation: 49

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

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

Upvotes: 4

Views: 1113

Answers (1)

dshukertjr
dshukertjr

Reputation: 18670

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, '@')

Upvotes: 2

Related Questions