How to execute an IN lookup in SQL using Golang?

Question

What does Go want for the second param in this SQL query. I am trying to use the IN lookup in postgres.

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= $1 AND other_field IN $2")
rows, err := stmt.Query(10, ???)

What I really want:

SELECT * FROM awesome_table WHERE id=10 AND other_field IN (this, that);

Answer 1

It looks like you may be using the pq driver. pq recently added Postgres-specific Array support via pq.Array (see pull request 466). You can get what you want via:

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id= $1 AND other_field = ANY($2)")
rows, err := stmt.Query(10, pq.Array([]string{'this','that'})

I think this generates the SQL:

SELECT * FROM awesome_table WHERE id=10 AND other_field = ANY('{"this", "that"}');

Note this utilizes prepared statements, so the inputs should be sanitized.

Answer 2

Query just takes varargs to replace the params in your sql so, in your example, you would just do

rows, err := stmt.Query(10)

say, this and that of your second example were dynamic, then you'd do

stmt, err := db.Prepare("SELECT * FROM awesome_table WHERE id=$1 AND other_field IN ($2, $3)")
rows, err := stmt.Query(10,"this","that")

If you have variable args for the "IN" part, you can do (play)

package main

import "fmt"
import "strings"

func main() {
    stuff := []interface{}{"this", "that", "otherthing"}
    sql := "select * from foo where id=? and name in (?" + strings.Repeat(",?", len(stuff)-1) + ")"
    fmt.Println("SQL:", sql)
    args := []interface{}{10}
    args = append(args, stuff...)
    fakeExec(args...)
    // This also works, but I think it's harder for folks to read
    //fakeExec(append([]interface{}{10},stuff...)...)
}

func fakeExec(args ...interface{}) {
    fmt.Println("Got:", args)
}

Answer 3

Incase anyone like me was trying to use an array with a query, here is an easy solution.

get https://github.com/jmoiron/sqlx

ids := []int{1, 2, 3}
q,args,err := sqlx.In("SELECT id,username FROM users WHERE id IN(?);", ids) //creates the query string and arguments
//you should check for errors of course
q = sqlx.Rebind(sqlx.DOLLAR,q) //only if postgres
rows, err := db.Query(q,args...) //use normal POSTGRES/ANY SQL driver important to include the '...' after the Slice(array)

Answer 4

With PostgreSQL, at least, you have the option of passing the entire array as a string, using a single placeholder:

db.Query("select 1 = any($1::integer[])", "{1,2,3}")

That way, you can use a single query string, and all the string concatenation is confined to the parameter. And if the parameter is malformed, you don't get an SQL injection; you just get something like: ERROR: invalid input syntax for integer: "xyz"

https://groups.google.com/d/msg/golang-nuts/vHbg09g7s2I/RKU7XsO25SIJ

Answer 5

if you use sqlx, you can follow this way: https://github.com/jmoiron/sqlx/issues/346

arr := []string{"this", "that"}
query, args, err := sqlx.In("SELECT * FROM awesome_table WHERE id=10 AND other_field IN (?)", arr)
 
query = db.Rebind(query) // sqlx.In returns queries with the `?` bindvar, rebind it here for matching the database in used (e.g. postgre, oracle etc, can skip it if you use mysql)
rows, err := db.Query(query, args...)

Answer 6

var awesome AwesomeStruct
var awesomes []*AwesomeStruct

ids := []int{1,2,3,4}
q, args, err := sqlx.In(`
  SELECT * FROM awesome_table WHERE id=(?) AND other_field IN (?)`, 10, ids)

// use .Select for multiple return
err = db.Select(&awesomes, db.SQL.Rebind(q), args...)

// use .Get for single return
err = db.Get(&awesome, db.SQL.Rebind(q), args...)