Database error saving new user in Supabase

Question

I created a public users table that get its data using trigger from the auth.users and so far it worked perfectly.

create table if not exists
  USERS (
    id uuid references auth.users not null primary key,
    full_name text,
    avatar text
);

create
or replace function public.handle_new_user () returns trigger language plpgsql security definer as $$
begin
  insert into public.users (id,full_name,avatar)
  values (new.id,new.raw_user_meta_data ->> 'fullName',new.raw_user_meta_data ->> 'avatar');
  return new;
end;
$$;

create
or replace trigger on_new_user
after insert on auth.users for each row
execute procedure public.handle_new_user ();

Throughout working on my project I've decided to add a favorites column to it that will be an array of the ids regard to the post added to favorites . I adapted the code to fit it and added manually the favorites column into the public users table

create
or replace function public.handle_new_user () returns trigger language plpgsql security definer as $$
begin
  insert into public.users (id,full_name,avatar,favorites)
  values (new.id,new.raw_user_meta_data ->> 'fullName',new.raw_user_meta_data ->> 'avatar',new.raw_user_meta_data ->> 'favorites');
  return new;
end;
$$;

But since I changed the code I've been receiving this error message "Database error saving new user"

I don't understand why does the changes causing this error to happen since everytime that I'm removing them it works again

I'm adding an example of the auth.users raw_user_meta_data:

{"sub":"a01dcc34-c49f-44ad-9f24-65b995e56639",
"email":"[email protected]",
"avatar":"",
"fullName":"niv gold",
"favorites":[],
"email_verified":false,
"phone_verified":false}```

dshukertjr · Accepted Answer · 2024-04-08 07:59:51Z

0

You are not using the correct JSON operator to access the favorites data. You can use the -> operator to extract the object as jsonb.

new.raw_user_meta_data -> 'favorites'

answered Apr 8, 2024 at 7:59

dshukertjr

19.2k11 gold badges71 silver badges101 bronze badges

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

2 Comments

Niv G. Over a year ago

Hi,thanks for answering. But it still doesn't work and I'm keep getting the same error message. To make it clear thr favorites row is an array of the ids of what the user saved into his favorites

dshukertjr Over a year ago

@NivG. What happens if you just cast it like new.raw_user_meta_data -> 'favorites'::text[]?

Collectives™ on Stack Overflow

Database error saving new user in Supabase

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related