Go app using Postgres array when selecting from Redshift table

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?

score 2 · Answer 1 · answered May 04 '18 at 12:59

2

The above is supposed to work with Postgres but Redshift is different and it doesn't have an array data type. Redshift supports ANY condition but in a different way, the parameter of the condition should be a set of rows, not an array:

select true where 1=any(select 1 union select 2 union select 3);

would return true, and

select true where 4=any(select 1 union select 2 union select 3);

would return nothing.

A set of rows can be a hardcoded union like above, or a result of a subquery, but not a comma separated list or an array.

answered May 04 '18 at 12:59

AlexYes

4,088
2
15
23

Could you explain how `select * from table where colID = ANY(array[1])` executed successfully on redshift? If `ANY(array[])` was constantly failing then that would align with issues with redshift. But the query works with `ANY` if manaully pass in the array instead of passing the array as a parameter – Pranavi Chandramohan May 06 '18 at 02:32

score 2 · Accepted Answer · answered May 07 '18 at 06:04

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)

Go app using Postgres array when selecting from Redshift table

2 Answers2