In Clojure how can I make a HoneySQL query that generates SQL to insert a value in a table field that have and enum type?

Question

Let's take for instance this table created in postgres:

CREATE TABLE public.item (
id uuid NOT NULL DEFAULT gen_random_uuid(),
"size_type" public."size_type" NULL)

and this data type:

CREATE TYPE public."size_type" AS ENUM (
'small',
'medium',
'large');

What I want to do is to generate with HoneySQL the following SQL query for insert the value 'small' into the "size_type" field of the item table

INSERT INTO item ("size_type") VALUES ('small'::size_type);

I am using this approach:

{:insert-into :item :values [{:size_type "small"}]}

But I am getting this mismatch error:

"message": "Conflict: Uncaught PSQL exception: ERROR: column "size_type" is of type size_type but expression is of type character varying\n Hint: You will need to rewrite or cast the expression.\n Position: 172", "psql-error-type": "DATATYPE_MISMATCH"

Answer 1

As Eugene said in his comment, what you need is :cast -- here's a few possible approaches, showing the SQL that would be generated in each case:

user=> (require '[honey.sql :as sql])
nil
user=> (sql/format {:insert-into :item :values [{:size_type [:cast "small" :size_type]}]})
["INSERT INTO item (size_type) VALUES (CAST(? AS SIZE_TYPE))" "small"]
user=> (sql/format {:insert-into :item :values [{:size_type [:cast "small" :public.size_type]}]})
["INSERT INTO item (size_type) VALUES (CAST(? AS PUBLIC.SIZE_TYPE))" "small"]
user=> (sql/format {:insert-into :item :values [{:size_type [:cast "small" :'public.size_type]}]})
["INSERT INTO item (size_type) VALUES (CAST(? AS public.size_type))" "small"]
user=> (sql/format {:insert-into :item :values [{:size_type [:cast [:inline "small"] :'public.size_type]}]})
["INSERT INTO item (size_type) VALUES (CAST('small' AS public.size_type))"]
user=>