sql: converting argument $1 type: unsupported type []int, a slice of in

Question

Have this:

somevars := []int{1, 2, 3, 4}
rows, err = db.Query("SELECT c1,c2 FROM table"+tid+" WHERE c1 IN($1,$2,$3,$4);", somevars)

Got this:

sql: converting argument $1 type: unsupported type []int, a slice of int

Any way to make a slice of arguments work with lib/pq?

Answer 1

pq.Array was the answer:

somevars := []int{1, 2, 3, 4}
rows, err = db.Query("SELECT c1,c2 FROM table"+tid+" WHERE c1 = any($1);", pq.Array(somevars))

Answer 2

An alternative solution is

somevars := []interface{}{1, 2, 3, 4}
rows, err = db.Query(
    "SELECT c1,c2 FROM table"+tid+" WHERE c1 IN($1,$2,$3,$4);",
    somevars...)

Here the ... expands a slice to multiple arguments, similar to the python *args. It's documented in the language spec.

The db.Query API supports this so called variadic parameter.

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

Here interface{} is known as the empty interface, and it can hold values of any type. See the Go tour example here. So one can use it like

db.Query(stmt, var1, var2)

where var1 var2 could be of different types.

In your case, you can also pass the slice elements explicitly

db.Query(stmt,
         somevars[0], somevars[1], somevars[2], somevars[3])

But it is rather verbose and requires extra work when the slice length changes.

Note that if instead of the interface slice somevars, we use intvars := []int {1, 2, 3, 4} and expand intvars in db.Query(), the compiler will complain on intvars...

cannot use []int literal (type []int) as type []interface {} in assignment

Type conversion intvars.([]interface{}) doesn't work either. This is documented in the language spec FAQ. And there is also a dedicated wiki page for it

It is disallowed by the language specification because the two types do not have the same representation in memory.

The intuitive picture of golang interface is an object with two fields, one field stores a type, and the other stores a pointer.

Answer 3

A more generalizable solution is to use the sqlx library: https://jmoiron.github.io/sqlx/#inQueries

// assume db is an initialized database handle
somevars := []int{1,2,3,4}
query, args, err := sqlx.In("SELECT * FROM table WHERE id IN (?)", somevars)
if err != nil {
  return err
}
query = db.Rebind(query)
result := db.Select(query, args...)

This solution will work with multiple database drivers and slices of unknown length.