Insert PostgreSQL Arrays with Clojure

Question

I cannot find a way to insert an array of Postgres arrays with Clojure.

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

It didn’t work as I 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 SQL access I could find did not work:

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

I don’t know what is going on here:

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

Is it really supposed to be possible? If not auxiliary functions, then raw SQL is somehow executed.

Answer 1

use insert! to insert a row vector you must create an object (from a row vector) that implements java.sql.Array. You can use java.sql.Connection.createArrayOf to create such an 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 docs on execute! said

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

Your string must be placed in the vector for it to work.

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

Answer 2

You can make clojure.java.jdbc automatically convert between Clojure vectors and SQL arrays by extending two protocols. This can be done from native 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 iv))))) (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 am currently working on a library that automatically supports PostgreSQL and PostGIS. It still works a lot, although https://github.com/remodoy/clj-postgresql

Answer 3

A similar strategy that I used:

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