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?

Drew Noakes · Accepted Answer · 2014-02-13 11:19:19Z

4

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.

answered Feb 13, 2014 at 11:19

Drew Noakes

313k174 gold badges704 silver badges770 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jsonmurphy Over a year ago

I stumbled across this as an alternative. a bit more verbose but might be worth looking into.

John Velonis · Accepted Answer · 2014-02-12 19:55:38Z

1

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.

answered Feb 12, 2014 at 19:55

John Velonis

1,7101 gold badge17 silver badges18 bronze badges

Comments

little-dude · Accepted Answer · 2019-07-24 05:45:43Z

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))))

Owen · Accepted Answer · 2020-09-22 08:29:16Z

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.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-05-09 01:27:20Z

0

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.

edited May 9, 2013 at 1:27

answered May 9, 2013 at 1:16

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

1 Comment

Jeff Terrell PhD Over a year ago

I think @espeed is asking about the use of the Clojure library clojure.java.jdbc, not Java's JDBC.

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related