2024-11-19 honeysql | Clojure Slack Archive

honeysql

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 ()"]

is this a know limitation? is this a honeysql issue?

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 ()

which is a broken syntax in terms of Postgres. Always check if your collection of ids is empty before performing such a query.

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)]))

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.

Clojurians Log v2

honeysql