从Redshift表中选择时使用Postgres数组进入应用

Question

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?

Answer 1

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)