Search code examples
jsonpostgresqlsupabase

How to insert enum with JSON user_meta_data in Supabase?

I created enum in Supabase with name "role" and contains "owner" and "employee" value like this:

Supabase Enum

But, when I create a function for a trigger like this:

insert into public.tbl_users (id, username, role)
values (new.id, new.raw_user_meta_data ->> 'username', new.raw_user_meta_data ->> 'role');

It returns error code 500 and the log is:

ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: column \"role\" is of type public.role but expression is of type text

How can I format the JSON text into type of ENUM? Btw, here is my JSON body:

{
  "email":"[email protected]",
  "password":"12345678",
  "data": {
    "username":"mamang",
    "role":"owner",
  }
}
Solution

  • I solved this by using CAST, it converts the default TEXT from JSON into the type I wanted. This is how I use it:

    insert into public.tbl_users (id, username, role)
values (new.id, new.raw_user_meta_data ->> 'username', CAST(new.raw_user_meta_data ->> 'role' as public.role));