我嘗試使用這樣的占位符進(jìn)行查詢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)它給我一個(gè)錯(cuò)誤：pq: function generate_series(unknown, unknown) is not unique然后我找到了一個(gè)將查詢格式化為字符串的解決方案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)}
1 回答
慕俠2389804
TA貢獻(xiàn)1719條經(jīng)驗(yàn) 獲得超6個(gè)贊
嘗試拆分您的查詢。你會(huì)看到第一部分工作正常：
db.QueryRow("SELECT $1+trunc(random()*$2) as test", 10, 5)
這可能是因?yàn)?1和$2與數(shù)學(xué)運(yùn)算符一起使用（和/或因?yàn)閠runc()和random()兩者都返回?cái)?shù)字），所以 postgres 可以推斷數(shù)據(jù)類型。
所以看起來(lái)是generate_series()參數(shù)沒有被正確確定。Postgres可以根據(jù)參數(shù)推斷數(shù)據(jù)類型，例如
if err := db.QueryRow("SELECT trunc($1,2) as test", 1.4343).Scan(&output); err != nil {panic(err)}
// output = 1.43
但是，如果它是一個(gè)多態(tài)函數(shù)，歧義就會(huì)出現(xiàn)并且會(huì)失敗，例如
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
為避免歧義，請(qǐng)?jiān)跍?zhǔn)備好的語(yǔ)句中顯式轉(zhuǎn)換參數(shù)，例如： generate_series($3::int,$4::int)
- 1 回答
- 0 關(guān)注
- 176 瀏覽
添加回答
舉報(bào)
0/150
提交
取消