Inserting PostgreSQL arrays with Clojure

Question

I can't find a way to insert Postgres' array type with Clojure.

(sql/insert! db :things {:animals ["cow" "pig"]})

Didn't work which I kind of expected. Error message:

PSQLException Can't infer the SQL type to use for an instance of clojure.lang.PersistentVector. Use setObject() with an explicit Types value to specify the type to use.  org.postgresql.jdbc2.AbstractJdbc2Statement.setObject (AbstractJdbc2Statement.java:1936)

Even the most direct access to SQL that I could find didn't work:

(sql/execute! db "INSERT INTO things (animals) VALUES ('{\"cow\", \"pig\"}')")

Don't really know what's going on here:

ClassCastException java.lang.Character cannot be cast to java.lang.String  clojure.java.jdbc/prepare-statement (jdbc.clj:419)

Surely it must be possible somehow? If not by the helper functions, then by somehow executing raw SQL.

jkj · Accepted Answer · 2014-09-11 12:13:23Z

You can make clojure.java.jdbc automatically convert between Clojure vectors and SQL arrays by extending two protocols. This can be done from your own code:

(extend-protocol clojure.java.jdbc/ISQLParameter
  clojure.lang.IPersistentVector
  (set-parameter [v ^java.sql.PreparedStatement stmt ^long i]
    (let [conn (.getConnection stmt)
          meta (.getParameterMetaData stmt)
          type-name (.getParameterTypeName meta i)]
      (if-let [elem-type (when (= (first type-name) \_) (apply str (rest type-name)))]
        (.setObject stmt i (.createArrayOf conn elem-type (to-array v)))
        (.setObject stmt i v)))))

(extend-protocol clojure.java.jdbc/IResultSetReadColumn
  java.sql.Array
  (result-set-read-column [val _ _]
    (into [] (.getArray val))))

REPL Example:

user> (def db (clj-postgresql.core/pool :dbname "test"))
#'user/db
user> (clojure.java.jdbc/query db ["SELECT ?::text[], ?::int[]" ["foo" "bar"] [1 2 3]])
({:int4 [1 2 3], :text ["foo" "bar"]})

I'm currently working on a library that will support PostgreSQL, and PostGIS types automatically. It's still very much work in process though https://github.com/remodoy/clj-postgresql

mavbozo · Accepted Answer · 2014-04-09 15:47:48Z

7

to use insert! to insert a vector of strings you must create an object (from the vector of strings) that implements java.sql.Array. You can use java.sql.Connection.createArrayOf to create such object

(def con (sql/get-connection db))

(def val-to-insert 
    (.createArrayOf con "varchar" (into-array String ["cow", "pig"]))

(sql/insert! db :things {:animals val-to-insert})

and

clojure.java.jdbc's docs on execute! said

(execute! db-spec [sql & params] :multi? false :transaction? true)
(execute! db-spec [sql & param-groups] :multi? true :transaction? true)

Your must put your sql string in a vector to make it work.

(sql/execute! db ["INSERT INTO things (animals) VALUES ('{\"cow\", \"pig\"}')"])

edited Apr 9, 2014 at 15:47

answered Apr 9, 2014 at 14:43

mavbozo

1,2619 silver badges10 bronze badges

Comments

bhurlow · Accepted Answer · 2014-09-25 21:45:01Z

0

A similar strategy that I've been using:

(defn vec->arr [array-vector]
  (.createArrayOf (j/get-connection db) "varchar" (into-array String array-vector)))

(extend-protocol j/ISQLValue
    clojure.lang.IPersistentVector
    (sql-value [v]
    (vec->arr v)))

answered Sep 25, 2014 at 21:45

bhurlow

2,0592 gold badges16 silver badges14 bronze badges

Collectives™ on Stack Overflow

Inserting PostgreSQL arrays with Clojure

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related