Postgres查询中的generate_series的占位符返回未知

Question

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)
}

Answer 1

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)