Couldn't handle an integer array with hugsql

[monstasat]

I have a table with two fields:

CREATE TABLE IF NOT EXISTS data
(id           VARCHAR(20) NOT NULL PRIMARY KEY,
 arr  INTEGER[]);

I have a method to add some data to the 'arr' field of the table:

-- :name set-arr :! :n
-- :doc updates arr
UPDATE data SET arr = :arr
WHERE id = :id

Sometimes (often) this method throws an exception with such message:

:cause "ERROR: column "arr" is of type integer[] but expression is of type jsonb\n Hint: You will need to rewrite or cast the expression.\n Position: 32"

What can cause this problem?

[csummers]

@monstasat In order to use Postgresql arrays, you need to create an array object type that the Postgresql JDBC driver understands to be a PG array. Below is an example of this using db-run instead of separate sql & clj. The db is a db spec and the jdbc/ is clojure.java.jdbc:

(let [db (:postgresql dbs)
      cn (jdbc/get-connection db)]
      (hugsql/db-run db "create table t1 (id integer, arr integer[])" {} :!)
      (hugsql/db-run db "insert into t1 (id) values (1)" {} :!)
      (hugsql/db-run db "update t1 set arr = :arr where id = 1" 
        {:arr (.createArrayOf cn "integer" (into-array Long [1 2 3]))} :!))

I think most folks probably create a function to take a Clojure vector of integers and return the needed array object.