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

Question

I tried passing the 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 methods cause a data inconsistency error.

I think that if I can call the functions of the postgres array from the request file, then updating and pasting can be done easily. Please, help.

Thank you in advance

Answer 1

The error message of your second attempt gives a hint:

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

Everything seems to go south when yesql tries to parse the :uids key, as it adds the closing bracket of the array definition. Let's try something else:

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

Note the extra spaces between :uids and array brackets.

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

Looks like an error in yesql for me,: :uid] should never be parsed as a valid key.

Edit: I was going to write an error with yesql, but it has already been fixed with the recently released version 0.5.2.

Answer 2

SQL arrays are not supported by yesql, but clojure.java.jdbc offers an extension point to 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"])) 

Please note that you cannot extend Clojure vectors as vectors; it makes special sense in yesql