英文:
Placeholders in the Postgres query for generate_series returns unknown
问题
我尝试使用占位符进行查询，像这样：
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 int64
var max_id int64
err:=_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.Rows
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)
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)
}
英文:
I try to make a query with placeholders like this
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)
It throws me an error:
pq: function generate_series(unknown, unknown) is not unique
Then I find a solution to format query as a string
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)
and it works.
I'd like to make it work in a right way, but I don't understand why it doesn't.
Updated:
var min_id int64
var max_id int64
err:=_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.Rows
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)
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)
}
答案1
得分: 9
尝试将查询拆分开来。你会发现第一部分运行正常：
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)。
英文:
Try breaking your query apart. You'll see the first part works fine:
db.QueryRow("SELECT $1+trunc(random()*$2) as test", 10, 5)
This probably works because $1 and $2 are used with mathematical operators (and/or because trunc() and random() both return numbers), so postgres can infer the data type.
So it looks like it's the generate_series() parameters that aren't being properly determined. Postgres can infer data types based on parameters, e.g.
if err := db.QueryRow("SELECT trunc($1,2) as test", 1.4343).Scan(&output); err != nil {panic(err)}
// output = 1.43
However, if it's a polymorphic function ambiguity can arise and it will fail, e.g.
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
To avoid ambiguity, explicitly cast parameters in your prepared statement, like: generate_series($3::int,$4::int)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论