CODEWITHSUNDEEP
sqljdbcclojure
espeed
espeed

Reputation: 4824

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

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?

Upvotes: 15

Views: 3540

Answers (5)

Owen
Owen

Reputation: 11

If anyone references this question while using the successor to clojure.java.jdbc, jdbc.next, the code for inserting enums is something like:

(ns whatever
 (:require
  [next.jdbc.sql :as jdbc.sql]
  [next.jdbc.types :as jdbc.types]
 ))

;; ...define your database connection and data source...    

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

(jdbc.sql/insert! ds :product prod-12345)

As documented https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md under the "Working with Enumerated Types" heading.

Upvotes: 1

little-dude
little-dude

Reputation: 1674

This blog post addresses the problem nicely. jdbc provides the ISQLValue protocol, that has only one method, sql-value that converts a clojure value into an sql value, represented by a PGObject. The blog post suggest representing enums with keywords in the form :type/value, so ISQLValue can be implemented as follow:

(defn kw->pgenum [kw]
  (let [type (-> (namespace kw)
                 (s/replace "-" "_"))
        value (name kw)]
    (doto (PGobject.)
      (.setType type)
      (.setValue value))))

(extend-type clojure.lang.Keyword
  jdbc/ISQLValue
  (sql-value [kw]
    (kw->pgenum kw)))

In your example, you would insert your product with:

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

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

The problem is that when querying the db, the enum is a simple string instead of a keyword. This can be solved in a similar fashion by implementing the IResultSetReadColumn protocol:

(def +schema-enums+
  "A set of all PostgreSQL enums in schema.sql. Used to convert
  enum-values back into Clojure keywords."
  ;; add your other enums here
  #{"product_status"})

(extend-type java.lang.String
  jdbc/IResultSetReadColumn
  (result-set-read-column [val rsmeta idx]
    (let [type (.getColumnTypeName rsmeta idx)]
      (if (contains? +schema-enums+ type)
        (keyword (s/replace type "_" "-") val)
        val))))

Upvotes: 1

Drew Noakes
Drew Noakes

Reputation: 311255

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.

Upvotes: 4

John Velonis
John Velonis

Reputation: 1649

Kris Jurka replied to the discussion Mike Sherrill cited above with a workaround:

use the url parameter stringtype=unspecified [in the JDBC connection URL] to have setString always bind to unknown instead of varchar, which then shouldn't require any code changes.

I tried this in Java, and it seems to work fine.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

Unless you pass plain SQL to the back end, you'll have to use a cast. (The SQL statement INSERT INTO product (name, status) VALUES ('SomeName', 'InStock'); should work fine.)

Tom Lane addressed this issue on pgsql-hackers a week after you asked your question.

AFAIK this is just business as usual with JDBC: setString() implies that the parameter is of a string type. It'll fall over if the type actually required is anything but a string. (I'm no Java expert, but I seem to recall that using setObject instead is the standard workaround.)

Enums are not suffering any special hardship here, and I'd be against weakening the type system to give them a special pass.

Our own @CraigRinger participated in that discussion, and might have found something relevant by now.

Upvotes: 0

Related Questions