douzi2785
2018-05-04 12:01
浏览 54
已采纳

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

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?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dtkl55257 2018-05-07 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)
    
    打赏 评论
  • dqwh1219 2018-05-04 12:59

    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.

    打赏 评论

相关推荐 更多相似问题