How to pass an array in sqlc to a database query with an "IN" operator

Question

I have been struggling with passing an array/slice from Golang to a sqlc (postgres) database query WHERE clause that has an IN operator. There is very little documentation around this function and it leaves some info out (such as how you would mark what kind of array it is with something like ::text[]). Whether I tried:

table.jsonb_col->>'some_id' IN (sqlc.slice('list_of_ids')::text[]) or

table.jsonb_col->>'some_id' IN (@list_of_ids::text[])

It didn't matter and I would always get the error

ERROR: operator does not exist: text = text[] (SQLSTATE 42883)

What I found out later is that it seems go is passing an array to the sql statement, but the sql is expecting a set of rows (or values, I'm not sure on the terminology) or something equivalent to that.

From what little I could find about the subject this is the way to do it, but it just didn't work.

Return values consisting of the set in Posgresql

Zegarek
– Zegarek

2025-09-17 08:54:33 +00:00
Commented Sep 17 at 8:54 — Zegarek
– Zegarek, Commented Sep 17 at 8:54

Joels Elf · Accepted Answer · 2025-09-17 00:23:37Z

1

I found one way of fixing this and also a workaround that may end up being more performant. Since go is passing an array and IN wants rows you can UNNEST the array to turn it into a set of rows and then use a subquery:

table.jsonb_col->>'some_id' IN (SELECT unnest(@list_of_ids::text[]))

However the other way of accomplishing the same thing may be preferred for its conciseness and often better performance.

You can accomplish the same task by using = ANY instead of IN. So my query WHERE clause became:

table.jsonb_col->>'some_id' = ANY (@list_of_ids::text[])

The thing about ANY is that it expects an array or a subquery (that I assumes produces an array) on the right-hand side, so go passing an array works for this, no need to unnest it. Either way works, but the = ANY is probably more performant so I went with that.

answered Sep 17 at 0:23

Joels Elf

8147 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass an array in sqlc to a database query with an "IN" operator

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related