2024-05-17 honeysql | Clojure Slack Archive

honeysql

timo 2024-05-17T13:19:45.754799Z

Can someone tell me how to insert an array into (embedded-)postgres with honeysql/next.jdbc?

(-> (hsqlh/insert-into :users)
      (hsqlh/columns  :login :password :foo_id :scopes)
      (hsqlh/values [[""  "$2a$11$SIRYw.woAoLzjevU1anJm.H6zxXR4.pHkJ0Au2ysQlnK5wOz2oVBe" 204 (hsql/format-expr [:array [:access]])]])
      (hsql/format)
      (#(jdbc/execute! (-> psql-client :ds) %)))

timo 2024-05-17T13:20:18.753119Z

error is

; (err) Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2725).
; (err) ERROR: column "scopes" is of type character varying[] but expression is of type character varying
; (err)   Hinweis: You will need to rewrite or cast the expression.
; (err)   Position: 75

igrishaev 2024-05-17T13:28:09.810029Z

Can you try this?

(honey.sql/format {:insert-into [:table [:id :scope]]
           :values [{:id 1 :scope [:inline [:array ["admin" "user"]]]}]})

which produces

["INSERT INTO table (id, scope) VALUES (?, ARRAY['admin', 'user'])" 1]

igrishaev 2024-05-17T13:30:13.655799Z

pay attention that (honey.sql/format [:array [:foo :bar]]) produces this:

["ARRAY[foo, bar]"]

where foo and bar and not strings but expressions that refer to the column names

igrishaev 2024-05-17T13:30:49.722219Z

but (honey.sql/format [:array ["foo" "bar"]]) produces

["ARRAY[?, ?]" "foo" "bar"]

where each item is a string

timo 2024-05-17T13:32:19.869619Z

that works!

igrishaev 2024-05-17T13:32:32.793739Z

in your code snippet, you use a keyword :access in your array which expands into a column name or expression but not a string value

🙏 2

timo 2024-05-17T13:33:10.467979Z

thank you!

p-himik 2024-05-17T13:47:41.232459Z

If you want to pass the whole array as an argument, you can simply pass a Java array.

👍 1

seancorfield 2024-05-17T16:50:20.329109Z

You probably also want to look at :lift which is helpful for structured data insertions. Also, next.jdbc has type coercions that would make this work with :lift.

❤️ 1

Clojurians Log v2

honeysql