Whenever I’ve got a database, and an array of IDs, and I want to fetch the rows with those IDs, I tend to favour an in query:
SELECT * FROM users WHERE id IN (1, 2, 3)This tends to work when I’m using SQLC with sqlite3, but it must’ve been a while since I’ve done so with PostgreSQL. I’m always left with adding a bit more boilerplate around these forms for queries. And today, when I tried doing this again with an array of UUIDs, I saw this error:
failed to encode args[0]: unable to encode []pgtype.UUID{...} into binary format for uuid (OID 2950): cannot find encode plan
A brief search online, and it looks like the way to do this is to use the any operator. This operates over an array of values, rather than a comma-separated tuple, making it a better fit for sqlc:
SELECT * FROM users WHERE id = ANY (sqlc.slice(ids));
But does it perform better? Depends. One thing I learnt about the IN keyword is that PostgreSQL actually translates IN statements into equality clauses, separated by ORs. This can have an effect on the query planner which, if faced with an IN clause with a single value, would leverage that for things, such as optimising away ordering.
But, in general, it’s actually better to use the ANY operator here. Of course, when it comes to database performance, there is nuance. But for most cases, and especially when using predefined statements with placeholders, using ANY here is preferable. And more pertinent to me: it fixed the error I was seeing.