生成_series 的 Postgres 查询中的占位符返回未知

Question

我尝试使用这样的占位符进行查询database.Query("select login,displayname from (select distinct $1+trunc(random()*$2)::integer as id from generate_series($3,$4) g ) r join users using(id) limit 10",min_id,max_id-min_id,min_id,max_id)它给我一个错误：pq: function generate_series(unknown, unknown) is not unique然后我找到了一个将查询格式化为字符串的解决方案query:=fmt.Sprintf("select login,displayname from (select distinct %v+trunc(random()*%v)::integer as id from generate_series(%v,%v) g ) r join users using(id) limit 10",min_id,max_id-min_id,min_id,max_id)它有效。我想让它以正确的方式工作，但我不明白为什么不这样做。更新：var min_id int64var max_id int64err:=_database.QueryRow("select min(id),max(id) from users").Scan(&min_id, &max_id)if err!=nil {     log.Panicf("Failed to get min and max %v",err.Error())    return}var rows *sql.Rowsquery:=fmt.Sprintf("select login,displayname from (select distinct %v+trunc(random()*%v)::integer as id from generate_series(%v,%v) g ) r join users using(id) limit 10",min_id,max_id-min_id,min_id,max_id)log.Printf(query)rows,err=_database.Query("select login,displayname from (select distinct $1+trunc(random()*$2)::integer as id from generate_series($3,$4) g ) r join users using(id) limit 10",min_id,max_id-min_id,min_id,max_id)if err!=nil {    log.Panicf("failed to get random entries: %v",err)}

Answer 1

尝试拆分您的查询。你会看到第一部分工作正常：

db.QueryRow("SELECT $1+trunc(random()*$2) as test", 10, 5)

这可能是因为$1和$2与数学运算符一起使用（和/或因为trunc()和random()两者都返回数字），所以 postgres 可以推断数据类型。

所以看起来是generate_series()参数没有被正确确定。Postgres可以根据参数推断数据类型，例如

if err := db.QueryRow("SELECT trunc($1,2) as test", 1.4343).Scan(&output); err != nil {panic(err)}

// output = 1.43

但是，如果它是一个多态函数，歧义就会出现并且会失败，例如

if err := db.QueryRow("SELECT trunc($1) as test", 1.4343).Scan(&output); err != nil {panic(err)}

// panic: pq: function trunc(unknown) is not unique

为避免歧义，请在准备好的语句中显式转换参数，例如： generate_series($3::int,$4::int)