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