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?

I am sure, but did you try it with a 2d slice? e.g. `[[item_type, item_number] ,..]` — ashu, Feb 23 '22 at 14:11
@ashu I just tried it as you suggested with `items := [][]string{{"itemtype1", "itemnumber1"}, {"itemtype2", "itemnumber2"}}`. However, it still gives an error that says `input of anonymous composite types is not implemented` — Yusril Maulidan Raji, Feb 23 '22 at 14:19
I am terribly sorry! I just re-read my comment, and I meant to write _I am not sure_. — ashu, Feb 23 '22 at 14:36
@ashu no need to say sorry :D every idea matters at this point since I am basically stuck. ;) — Yusril Maulidan Raji, Feb 23 '22 at 14:57

score 0 · Answer 1 · answered Feb 23 '22 at 14:58

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...)
// ...

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

1 Answers1