How to insert and update postgres arrays using yesql in clojure?

Question

I have tried passing a clojure vector, also tried the following format:

-- name: insert-into-sometable<!
-- inserts in the sometable the lid and uids
INSERT INTO sometable
(lid, uids) values(:lid, ARRAY[:uids])

But both the methods throws data mismatch error.

I think if I can call the postgres array functions from the query file then update and insert can be done easily. Please help.

Thanks in advance

Answer 1

The error message of your 2nd try gives a subtile hint:

AssertionError Assert failed: Query argument mismatch.
Expected keys: (:uids])
Actual keys: (:uids)
Missing keys: (:uids])

Apparently things go south when yesql tries to parse the :uids key as it appends the closing bracket of the array definition. Lets try something else:

-- name: insert-into-sometable<!
-- inserts in the sometable the lid and uids
INSERT INTO sometable
(lid, uids) values(:lid, ARRAY[ :uids ])

Notice the extra spaces between :uids and the array brackets.

=> (insert-into-sometable<! {:lid 1, :uids [1 2 42])
;; => 1

Looks like a bug in yesql to me, :uid] should never get parsed as a valid key.

Edit: Was about to file a bug with yesql, but it's already fixed with the recently released 0.5.2 version.

Answer 2

SQL Arrays are not supported by yesql, but clojure.java.jdbc offers an extension point in the ISQLParameter protocol, which you can use as:

(deftype StringArray [items]
  clojure.java.jdbc/ISQLParameter
  (set-parameter [_ stmt ix]
    (let [as-array (into-array Object items)
          jdbc-array (.createArrayOf (.getConnection stmt) "text" as-array)]
      (.setArray stmt ix jdbc-array))))

(insert-into-sometable<! db-spec "hi" (->StringArray ["one" "two"]))

Note that you cannot extend Clojure's vectors as vectors has a special meaning in yesql