[Solved] PostgreSQL enumeration type usage error: operator does not exist error handling

//Creating Enumeration Classes
CREATE TYPE USER_ROLE AS ENUM ('MALE', 'FEMALE');
//Add conversion rules
CREATE CAST (VARCHAR AS USER_ROLE) WITH INOUT AS IMPLICIT;
//Create table, add fields of enumeration type
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      not null,
    user_name   varchar(200)       not null
);

Because the conversion rule is added, you can directly use the varchar type string as the judgment condition query in pgadmin, but if you use mybatis to query the database, the error operator does not exist will be reported

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

Solution: convert varchar type to enumeration type and compare

Method 1
select * from sys_user where del_flag = 0 and role = cast(#{role} as user_role);
Method 2
select * from sys_user where del_flag = 0 and role = #{role}::user_role;


Read More: