PostgreSQL 枚举类型使用及ERROR: operator does not exist错误处理

//删除枚举类
DROP TYPE IF EXISTS USER_ROLE CASCADE;
//创建枚举类
CREATE TYPE USER_ROLE AS ENUM ('MALE', 'FEMALE');
//添加转换规则
CREATE CAST (VARCHAR AS USER_ROLE) WITH INOUT AS IMPLICIT;
//如果存在先删除表
DROP TABLE IF EXISTS sys_user;
//创建表，添加枚举类型字段
create table sys_user
(
    row_id      bigserial          not null
        constraint sys_user_pkey primary key,
    create_time timestamp(6),
    update_time timestamp(6),
    del_flag    smallint default 0 not null,
    role        USER_ROLE  default 'MALE'::USER_ROLE    not null,
    user_name   varchar(200)       not null
);
// 添加新的枚举值 [参考文档](https://www.postgresql.org/docs/9.1/sql-altertype.html)
ALTER TYPE USER_ROLE ADD VALUE 'RENYAO';

参考文档

• 因为添加了转换规则，所以在pgadmin中可以直接使用varchar类型的字符串作为判断条件查询，但是使用mybatis查询数据库会报错operator does not exist

select * from sys_user where del_flag = 0 and role = 'MALE'

• 解决方法：将varchar类型转成枚举类型再做比较

方式一
select * from sys_user where del_flag = 0 and role = cast(#{role} as user_role)；
方式二
select * from sys_user where del_flag = 0 and role = #{role}::user_role;