How do I perform an IN search in SQL using Golang?
What does Go want for the second parameter in this SQL query. I am trying to use search IN
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);
source to share
The query just takes varargs to replace the parameters in your sql so in your example you just do
rows, err := stmt.Query(10)
let's say this and your second example were dynamic then you would 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 args variables 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 harder for folks to read
//fakeExec(append([]interface{}{10},stuff...)...)
}
func fakeExec(args ...interface{}) {
fmt.Println("Got:", args)
}
source to share
It looks like you can use pq driver . pq
recently added Array support with Postgres 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 SQL:
SELECT * FROM awesome_table WHERE id=10 AND other_field = ANY('{"this", "that"}');
Note that this uses prepared instructions, so the entrances must be sanitized.
source to share
If someone like me tried to use an array with a query, here is a simple 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)
source to share
With PostgreSQL, at least you have the ability to pass the entire array as a string using a single placeholder:
db.Query("select 1 = any($1::integer[])", "{1,2,3}")
This way you can use one query string and all string concatenation is parameter-limited. And if the parameter is wrong, you won't get SQL injection; you just get something like: ERROR: invalid input syntax for integer: "xyz"
https://groups.google.com/d/msg/golang-nuts/vHbg09g7s2I/RKU7XsO25SIJ
source to share
You can also use this direct conversion.
awesome_id_list := []int{3,5,8}
var str string
for _, value := range awesome_id_list {
str += strconv.Itoa(value) + ","
}
query := "SELECT * FROM awesome_table WHERE id IN (" + str[:len(str)-1] + ")"
Warning
This method is vulnerable to SQL Injection. Use this method only if awesome_id_list
created by the server.
source to share