duanhui5344
2017-10-12 03:16
浏览 180

在golang中使用postgres IN子句

I have been trying to use the postgres IN clause in golang, but keep getting errors. This is the query I want to execute.

SELECT id1 FROM my_table WHERE type = (an int) AND id2 = (an int) AND id1 IN (list of UUIDs)

I used this code to construct this query but got the following error.

var params []interface{}
inCondition := ""
params = append(params, type)
params = append(params, id2)
for _, id := range id1 {
    params = append(params, id)
    if inCondition != "" {
        inCondition += ", "
    }
    inCondition += "?"
}
query := fmt.Sprintf(`SELECT id1 FROM my_table WHERE type = ? AND id2 = ? AND id1 IN (%s)`, inCondition)
rows, err := db.Query(query, params...)

Query I got:

SELECT id1 FROM my_table WHERE type = ? AND id2 = ? AND id1 IN (?, ?, ?)

Params output:

[]interface {}=[0 7545449 d323f8d5-ab97-46a3-a34e-95ceac2f3a6a d323f8d5-ab97-46a3-a34e-95ceac2f3a6b d323f8d5-ab97-46a3-a34e-95ceac2f3a6d]

Error:

pq: syntax error at or near \"AND\""

What am I missing? or, how will I get this to work? id1 is a slice of UUIDs whose length is variable.

图片转代码服务由CSDN问答提供 功能建议

我一直在尝试在golang中使用postgres IN子句,但始终会出错。 这是我要执行的查询。

 从my_table中选择id1,其中type =(一个int)AND id2 =(一个int)AND id1 IN(UUID列表)\  n   
 
 

我使用此代码构造了此查询,但出现了以下错误。

  var params [] interface {  } 
inCondition:=“” 
params = append(params,type)
params = append(params,id2)
for _,id:=范围id1 {
 params = append(params,id)
如果inCondition!  =“” {
 inCondition + =“,” 
} 
 inCondition + =“?” 
} 
query:= fmt.Sprintf(`从my_table中选择id1,其中type =?AND id2 =?AND id1 IN  (%s)`,没有条件)
rows,错误:= db.Query(query,params ...)
   
 
 

我得到的查询:

 从my_table中选择id1,其中type =吗?  AND id2 =?  AND id1 IN(?,?,?)
   
 
 

参数输出:

  [] interface {} =  [0 7545449 d323f8d5-ab97-46a3-a34e-95ceac2f3a6a d323f8d5-ab97-46a3-a34e-95ceac2f3a6b d323f8d5-ab97-46a3-a34e-95ceac2f3a6d] 
   
 
   
 
 
  pq:\“ AND \”“ 
   
 
 

或附近的语法错误?或 ,我将如何使用它呢?id1是长度可变的UUID的一部分。

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

2条回答 默认 最新

  • dongpingwu8378 2017-10-12 04:27
    已采纳

    Instead of ?, using $1,$2 etc as placeholder worked.

    点赞 评论
  • doushu2699 2018-03-24 11:29

    Ran into a similar issue. Can't remember where exactly I picked this up but I remember still running into issues when dealing with arrays of type integer vs string. What I had to do was to have a local custom type and return a driver compatible value for it. See sample below.

    // Int64Array is a type implementing the sql/driver/value interface
    // This is due to the native driver not supporting arrays...
    type Int64Array []int64
    
    // Value returns the driver compatible value
    func (a Int64Array) Value() (driver.Value, error) {
        var strs []string
        for _, i := range a {
            strs = append(strs, strconv.FormatInt(i, 10))
        }
        return "{" + strings.Join(strs, ",") + "}", nil
    }
    

    Highly recommend checking out sqlx. Wrote a simple orm wrapper called papergres to make my go + postgres life easier :) Give it a try.

    点赞 评论

相关推荐 更多相似问题