sqldelight
sqldelight copied to clipboard
Postgres cast of string <-> enum problem
SQLDelight Version
2.0.0-alpha03
SQLDelight Dialect
Postgres
Describe the Bug
Problem with casting string <-> enum in Postges. SQL:
SELECT CAST(count(id) AS INTEGER)
FROM patient_actions
WHERE
CASE WHEN :typesExists
THEN type IN :types
ELSE TRUE
END;
☝️ it leads to runtime error: ERROR: operator does not exist: patient_actions_type_enum = character varying
Because type of patient_actions.type
is enum patient_actions_type_enum
Ok try something another:
SELECT CAST(count(id) AS INTEGER)
FROM patient_actions
WHERE
CASE WHEN :typesExists
THEN CAST(type AS TEXT) IN :types
ELSE TRUE
END;
☝️ it leads to runtime error: class com.treatment.enums.PatientActionTypeEnum cannot be cast to class java.lang.String
SELECT CAST(count(id) AS INTEGER)
FROM patient_actions
WHERE
CASE WHEN :typesExists
THEN type::text IN :types
ELSE TRUE
END;
☝️ Not event compiled
Is exists way to cast string <-> enum in Postgres?
Stacktrace
No response
SELECT CAST(count(id) AS INTEGER)
FROM patient_actions
WHERE
CASE WHEN :typesExists
THEN CAST(type AS TEXT) IN :types
ELSE TRUE
END;
I found problems and ☝️ works But CAST on field is performance killer Is exists way to compile something like? 👇 :
SELECT CAST(count(id) AS INTEGER)
FROM patient_actions
WHERE
CASE WHEN :typesExists
THEN type IN ('value1':: patient_actions_type_enum, 'value2'::patient_actions_type_enum)
ELSE TRUE
END;