Clojurians Log v2
Clojure programming
Channels
Apps
honeysql 2024-05-17
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) %))) 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"]]]}]})["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]"]igrishaev 2024-05-17T13:30:49.722219Z
but (honey.sql/format [:array ["foo" "bar"]]) produces
["ARRAY[?, ?]" "foo" "bar"]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
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