How do you insert a Postgres enum value using Clojure JDBC?

Question

For example, here is a product table in PostgreSQL with status as an enum:

create type product_status as enum ('InStock', 'OutOfStock');

create table product (
    pid            int primary key default nextval('product_pid_seq'),
    sku            text not null unique,
    name           text not null,
    description    text not null,
    quantity       int not null,
    cost           numeric(10,2) not null,
    price          numeric(10,2) not null,
    weight         numeric(10,2),
    status         product_status not null
);

Typical Clojure code to insert a product would be:

(def prod-12345 {:sku "12345"
                 :name "My Product"
                 :description "yada yada yada"
                 :quantity 100
                 :cost 42.00
                 :price 59.00
                 :weight 0.3
                 :status "InStock"})

(sql/with-connection db-spec
   (sql/insert-record :product prod-12345))

However, status is an enum so you can't insert it as a normal string without casting it to an enum:

'InStock'::product_status

I know you can do it with a prepared statement, such as:

INSERT INTO product (name, status) VALUES (?, ?::product_status)

But is there a way to do it without using a prepared statement?

Answer 1

I got this working today using the stringtype=unspecified hack workaround.

You can add this parameter to your db-spec as follows:

(def db-spec {:classname "org.postgresql.Driver"
              :subprotocol "postgresql"
              :subname "//myserver:5432/mydatabase"
              :user "myuser"
              :password "mypassword"
              :stringtype "unspecified"}) ; HACK to support enums

Then just use insert! as usual.

It would be good to have a solution that doesn't weaken type safety so much.