Go and IN clause in Postgres

Question

I am trying to execute the following query against the PostgreSQL database in Go using pq driver:

SELECT COUNT(id)
FROM tags
WHERE id IN (1, 2, 3)

where 1, 2, 3 is passed at a slice tags := []string{"1", "2", "3"}.

I have tried many different things like:

s := "(" + strings.Join(tags, ",") + ")"
if err := Db.QueryRow(`
    SELECT COUNT(id)
    FROM tags
    WHERE id IN $1`, s,
).Scan(&num); err != nil {
    log.Println(err)
}

which results in pq: syntax error at or near "$1". I also tried

if err := Db.QueryRow(`
    SELECT COUNT(id)
    FROM tags
    WHERE id IN ($1)`, strings.Join(stringTagIds, ","),
).Scan(&num); err != nil {
    log.Println(err)
}

which also fails with pq: invalid input syntax for integer: "1,2,3"

I also tried passing a slice of integers/strings directly and got sql: converting Exec argument #0's type: unsupported type []string, a slice.

So how can I execute this query in Go?

Answer 1

Pre-building the SQL query (preventing SQL injection)

If you're generating an SQL string with a param placeholder for each of the values, it's easier to just generate the final SQL right away.

Note that since values are strings, there's place for SQL injection attack, so we first test if all the string values are indeed numbers, and we only proceed if so:

tags := []string{"1", "2", "3"}
buf := bytes.NewBufferString("SELECT COUNT(id) FROM tags WHERE id IN(")
for i, v := range tags {
    if i > 0 {
        buf.WriteString(",")
    }
    if _, err := strconv.Atoi(v); err != nil {
        panic("Not number!")
    }
    buf.WriteString(v)
}
buf.WriteString(")")

Executing it:

num := 0
if err := Db.QueryRow(buf.String()).Scan(&num); err != nil {
    log.Println(err)
}

Using ANY

You can also use Postgresql's ANY, whose syntax is as follows:

expression operator ANY (array expression)

Using that, our query may look like this:

SELECT COUNT(id) FROM tags WHERE id = ANY('{1,2,3}'::int[])

In this case you can declare the text form of the array as a parameter:

SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])

Which can simply be built like this:

tags := []string{"1", "2", "3"}
param := "{" + strings.Join(tags, ",") + "}"

Note that no check is required in this case as the array expression will not allow SQL injection (but rather will result in a query execution error).

So the full code:

tags := []string{"1", "2", "3"}

q := "SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])"
param := "{" + strings.Join(tags, ",") + "}"

num := 0
if err := Db.QueryRow(q, param).Scan(&num); err != nil {
    log.Println(err)
}

Answer 2

This is not really a Golang issue, you are using a string to compare to integer (id) in your SQL request. That means, SQL receive:

SELECT COUNT(id)
FROM tags
WHERE id IN ("1, 2, 3")

instead of what you want to give it. You just need to convert your tags into integer and passe it to the query.

EDIT: Since you are trying to pass multiple value to the query, then you should tell it:

params := make([]string, 0, len(tags))
for i := range tags {
    params = append(params, fmt.Sprintf("$%d", i+1))
}
query := fmt.Sprintf("SELECT COUNT(id) FROM tags WHERE id IN (%s)", strings.Join(params, ", "))

This will end the query with a "($1, $2, $3...", then convert your tags as int:

values := make([]int, 0, len(tags))
for _, s := range tags {
    val, err := strconv.Atoi(s)
    if err != nil {
        // Do whatever is required with the error
        fmt.Println("Err : ", err)
    } else {
        values = append(values, val)
    }
}

And finally, you can use it in the query:

Db.QueryRow(query, values...)

This should do it.

Answer 3

Extending @icza solution, you can use pq.Array instead of building the params yourself.

So using his example, the code can look like this:

tags := []string{"1", "2", "3"}

q := "SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])"

num := 0
if err := Db.QueryRow(q, pq.Array(tags)).Scan(&num); err != nil {
    log.Println(err)
}