begin
  insert into public.profiles (id, email, name, location, confirmed_at)
  values (new.id, new.email, new.raw_user_meta_data->> 'name', new.raw_user_meta_data->> 'location', new.email_confirmed_at);
  return new;
end;

Hey I have this sql function when new user signs up it creates a profile for each. Its working but the confirmed_at field is not updating i dont know why, i tried

new.email_confirmed_at

and

new.confirmed_at

This is because it doesn't contain any value at the point of registration, that only gets updated when the user clicks the email confirmation link in the email that is sent to them. What you can do instead is check for an update on the

auth.users

table and check if the

email_confirmed_at

filed has changed and update the

public.profiles

table at that point.

Hii i tried this and it does not work, now the the profile is not being created at all

begin
if OLD.confirmed_at != NEW.confirmed_at then
insert into public.profiles (id, email, name, location, confirmed_at)
  values (new.id, new.email, new.raw_user_meta_data->> 'name', new.raw_user_meta_data->> 'location', new.email_confirmed_at);
end if;
  return new;
end;

You will need a separate on update trigger from the on insert trigger.

hii @silentworks Here is what i did. I have a insert trigger first here is what that looks like and this works except the

confirmed_at

field

begin
  insert into public.profiles (id, email, name, location, confirmed_at)
  values (new.id, new.email, new.raw_user_meta_data->> 'name', new.raw_user_meta_data->> 'location', new.email_confirmed_at);
  return new;
end;

And i have a second update trigger like you suggested, here is what i tried

begin
if NOT EXISTS (select confirmed_at from public.profiles where id = new.id) then
  update public.profiles 
    set confirmed_at = new.confirmed_at
   where id = new.id ;
end if;
  return new;
end;

The above did'nt work so i tried this also and this also did not worked.

begin
if OLD.email_confirmed_at !=NEW.email_confirmed_at  then
  update public.profiles 
    set confirmed_at = new.email_confirmed_at
   where id = new.id ;
end if;
  return new;
end;