Return values consisting of the set in Posgresql

Issue

I use https://github.com/kyleconroy/sqlc to generate code. I want to return human_id using the group_id array.

-- name: HumansByGroupID :many
SELECT human_id FROM groups
WHERE group_id IN (UNNEST($1::uuid[]));

return
ERROR: set-returning functions are not allowed in WHERE (SQLSTATE 0A000)

In my understanding, ... IN (UNNEST($1::uuid[])); turns into IN ('...','...','...');

Solution

You can do the following:

WHERE group_id IN (SELECT col FROM UNNEST($1::uuid[]) AS col);

or, alternatively, you can also do:

WHERE group_id = ANY ($1::uuid[]);

Answered By – mkopriva

Answer Checked By – Dawn Plyler (GoLangFix Volunteer)

Leave a Reply

Your email address will not be published.