如何在Supabase中通过JSON用户元数据插入ENUM类型数据？

问题场景 #

我在Supabase中创建了名为role的ENUM类型，包含owner和employee两个取值。但在编写触发器函数执行以下插入语句时：

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

返回500错误，日志显示：

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

我的JSON请求体如下：

{
  "email":"tester@gmail.com",
  "password":"12345678",
  "data": {
    "username":"mamang",
    "role":"owner"
  }
}

解决方法 #

报错核心原因是：new.raw_user_meta_data ->> 'role'提取出的是text类型，但tbl_users表的role字段是自定义的public.roleENUM类型，类型不匹配导致插入失败。以下是几种转换方式：

1. 直接强制类型转换 #

在提取的文本值后使用::public.role完成强制转换，修改后的插入语句：

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

注意给提取表达式加括号，确保先提取文本再执行转换。

2. 使用CAST函数转换 #

也可以用标准SQL的CAST函数实现转换，效果和上面一致：

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)
);

3. 容错处理（可选） #

如果担心JSON中的role值不在ENUM允许范围内（比如拼写错误），可以用PostgreSQL 12+支持的TRY_CAST函数，转换失败时返回NULL，避免事务中断：

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

还能结合COALESCE给转换失败的情况设置默认值，比如默认设为employee：

insert into public.tbl_users (id, username, role)
values (
  new.id,
  new.raw_user_meta_data ->> 'username',
  COALESCE(TRY_CAST(new.raw_user_meta_data ->> 'role' AS public.role), 'employee'::public.role)
);

内容的提问来源于stack exchange，提问作者Raka M.A