I have seen many examples online for using an array while selecting values from a table. This is the query I ran against Redshift.
select * from table where colID = ANY(array[1])
This query works fine when I ran it using SQL Workbench.
I am trying to run the same query in my Go app using https://github.com/lib/pq
db, err := sql.Open("postgres", url)
defer db.Close()
rows, err := db.Query(`select * from table where colID = ANY($1)`, pq.Array([]int{1}))
if nil != err {
pqErr := err.(*pq.Error)
fmt.Println(pqErr.Error())
}
The above code is expected to work according to https://godoc.org/github.com/lib/pq#Array.
But the output is an error.
-----------------------------------------------
error: Assert
code: 1000
context: IsA((Node*)arrayExpr->args->tail->data.ptr_value, Const) -
query: 9574375
location: xen_execute.cpp:6200
process: padbmaster [pid=14680]
-----------------------------------------------
as the error and rows is nil
However the below code works
rows, err := db.Query(`select * from table where colID = ANY(array[1])`)
Can anybody explain why I am getting an error?
Redshift supports ANY('{1,2,3}'::integer[]).
Reason why db.Query('select * from table where colID = ANY($1)', pq.Array([]int{1,2,3})) did not work is because, the value returned by pq.Array([]int{1,2,3}) is {1,2,3}. However redshift expects it to be '{1,2,3}'. Changing the query to include the single paranthesis ''
db.Query('select * from table where colID = ANY('$1')', pq.Array([]int{1,2,3}))
around array does not work.
After trying few options, the below code worked!
v, _ := pq.Array([]int{1,2,3}).Value()
query := fmt.Sprintf(`select * from table where colID = any('%v'::integer[]);`, v)
rows, err := db.Query(query)