How to do WHERE IN/ANY on multiple columns in Golang with pq library?

Question

I have a list of item type and item numbers like follows:

items := models.ItemKeys{
        ItemKeys: []models.ItemKey{
            {
                ItemType:   "type1",
                ItemNumber: "10347114",
            },
            {
                ItemType:   "type2",
                ItemNumber: "40428383",
            },
            {
                ItemType:   "type3",
                ItemNumber: "90351753",
            },
        },
    }

I would like to produce this kind of query:

SELECT * FROM item
WHERE (item_type, item_number) IN (('type1','10347114'), ('type2','40428383'), ('type3','90351753'))

it is worth mentioning that I'm using pq package (https://github.com/lib/pq). I happen to find there is a way using ANY instead. However, when I try it, it says sql: converting argument $1 type: pq: Unable to convert models.ItemKeys to array

Here is my current code:

rows, err := r.Db.QueryContext(ctx, "SELECT * "+
        "FROM item "+
        "WHERE (item_type, item_number) = ANY($1) "+
        "AND deleted_dtime IS NULL", pq.Array(items))

Does anyone have a clue how to do it correctly?

Answer 1

You could do the following:

inSQL, args := "", []interface{}{}
for i, itemKey := range items.ItemKeys {
    n := i * 2
    inSQL += fmt.Sprintf("($%d,$%d),", n+1, n+2)
    args = append(args, itemKey.ItemType, itemKey.ItemNumber)
}
inSQL = inSQL[:len(inSQL)-1] // drop last ","

query := `SELECT * FROM item WHERE (item_type, item_number) IN (` + inSQL + `) AND deleted_dtime IS NULL`

// query: SELECT * FROM item WHERE (item_type, item_number) IN (($1,$2),($3,$4),($5,$6)) AND deleted_dtime IS NULL
// args: ["type1" "10347114" "type2" "40428383" "type3" "90351753"]

rows, err := r.Db.QueryContext(ctx, query, args...)
// ...