Expand yet keep performance

16 Apr 2021

The cryptic title to this entry just means this: When you want to fill in the args for a SQL statement in an in clause, you ideally want to have just one arg get filled in, not a list of args, because especially with numbered args, one needs to get into string concatenation of a SQl query, and who wants to do that?

Example:

...where my_col in ($1, $2, $3)

But that's OK, because PostgreSQL has arrays, so one could instead do

...where my_col in (select unnest from unnest('{1, 2, 3, 4, 5}'::int2[]))

Hence

...where my_col in (select unnest from unnest($1::int2[]))

And in Go, using pgx, this would look like

daysOfWeek = []int{1, 2, 3, 4, 5}
dowArr := &pgtype.Int2Array{}
dowArr.Set(daysOfWeek)
rows, err := pgConn.Query(
    context.Background(),
    `select foo from x where my_col in (select unnest from unnest($1::int2[]))`,
    dowArr)

Finally, note the unnest is nice to have for performance reasons.