Clojurians Log v2
Clojure programming
Channels
Apps
honeysql 2024-11-19
souenzzo 2024-11-19T12:54:29.974699Z
Hello. I'm new(bie) into SQL/postgres world
This query on postgres fails when ids is empty
(let [ids #{}]
(honey.sql/format {:select [:id]
:from [:users]
:where [:in :id (vec ids)]}))
=> ["SELECT id FROM users WHERE id IN ()"]
p-himik 2024-11-19T13:09:08.442439Z
A known thing: https://clojurians.slack.com/archives/C66EM8D5H/p1659858798143319
igrishaev 2024-11-19T14:06:10.770479Z
It's expected behaviour. Your map produces something like
where id in ()souenzzo 2024-11-19T14:08:07.831329Z
@igrishaev so it is more like a psql limitation than a honeysql issue?!
p-himik 2024-11-19T14:08:14.531589Z
As an alternative that doesn't need a check: [:= :id [:any (into-array Integer/TYPE ids)]].
👍 1
👆 1
p-himik 2024-11-19T14:09:29.386449Z
@souenzzo It's a limitation of the SQL standard - it requires for there to be at least one item in the IN (...) list.
👍 1
igrishaev 2024-11-19T14:11:09.998279Z
@souenzzo Keep in mind that HoneySQL is a builder for raw SQL. But you can easily build an expression which fails when being passed to a database. That's exactly your case.
plooney 2024-11-19T15:54:52.433479Z
For things that may need a check, I really enjoy using the honey-sql helper functions.
(cond-> {:select [:id]
:from [:users]}
(seq ids) (sql/where [:in :id (vec ids)])) 1
valerauko 2024-11-20T16:20:12.847079Z
i circumvented this with = ANY, which is much easier to put into honeysql (instead of placeholders for each element it makes one for the array) https://stackoverflow.com/questions/34627026/in-vs-any-operator-in-postgresql
p-himik 2024-11-20T16:24:51.824469Z
Oops - that's what I meant with my alternative above, but I forgot the actual :any. Fixed.