pass array to go-pg query

Question

I'm using Go-pg and when I use the following way to execute sql query:

db.Query(&result, sqlQuery, params)

where params is a structure like the following:

type MyParams struct {
    Codes []int
}

and sqlQuery is

SELECT id FROM bar WHERE code_id IN (?codes)

in the actual SQL query I get query like this:

SELECT id FROM bar WHERE code_id IN ('[7,45]')

Is it possible to have int array placeholder passed properly to have a query:

SELECT id FROM bar WHERE code_id IN (7,45)

Answer 1

There are a couple things you can do:

1. Keep using in (...) and use pg.In.
2. Use = any(array) in your SQL instead of in (list) and pg.Array in Go to send a proper array to PostgreSQL.

The first looks like:

db.Query(&result, `SELECT id FROM bar WHERE code_id IN (?)`, pg.In(params.Codes))

the second looks like:

db.Query(&result, `SELECT id FROM bar WHERE code_id = ANY (?)`, pg.Array(params.Codes))

Answer 2

You could use the go-pg ORM instead for the same results:

ids := []int{1, 2, 3}
err := db.Model((*Book)(nil)).
    Where("id in (?)", pg.In(ids)).
    Select()

// SELECT * FROM books WHERE id IN (1, 2, 3)