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

从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)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能