4

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

Abhishek MG
  • 45
  • 4

1 Answers1

1

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, '@')
dshukertjr
  • 15,244
  • 11
  • 57
  • 94