英文:
How to pass an interval parameter to a prepared statement?
问题
我想要删除我Postgres数据库中所有早于X分钟的数据库条目。我的Go语言中的预处理语句如下所示：
delete
from my_table
where expires < (to_timestamp($1) - '$2 minutes'::interval);
我应该如何正确传递第二个参数$2？
PS：我知道有不同的语句可以解决这个问题，但我特别想知道如何传递带引号的参数。
英文:
I want to delete all database entries in my Postgres database that are older than X minutes. My prepared statement in go looks like this:
delete
from my_table
where expires < (to_timestamp($1) - '$2 minutes'::interval);
How can I correctly pass the second parameter $2?
PS: I know there's different statements to solve the problem, but I am explicitly interested in how to pass parameters that are quoted.
答案1
得分: 3
你可以将参数转换为 text，然后与字符串 ' minutes' 进行拼接。
delete from my_table
where expires < (to_timestamp($1) - ($2::text || ' minutes')::interval
更新：实际上，由于PostgreSQL具有 any || text 运算符，其结果为 text，所以你不需要对参数进行类型转换。
英文:
You can cast the parameter to text and then concatenate it with the string ' minutes'.
delete from my_table
where expires < (to_timestamp($1) - ($2::text || ' minutes')::interval
UPDATE: actually, since postgres does have a any || text operator, the result of which is text, you shouldn't need to type cast the parameter.
答案2
得分: 3
在字符串字面值中无法插入参数。
对于你的情况，一个可能的解决方案是使用一个区间与一个数字相乘：
where expires < (to_timestamp($1) - $2 * '1 minute'::interval)
英文:
There is no way to interpolate a parameter into a string literal.
A possible solution for your case would be to multiply a number with an interval:
where expires < (to_timestamp($1) - $2 * '1 minute'::interval)
答案3
得分: 3
你可以使用make_interval函数：
delete from my_table
where expires < (to_timestamp($1) - make_interval(mins => $2));
英文:
You can use make_interval
delete from my_table
where expires < (to_timestamp($1) - make_interval(mins => $2));
答案4
得分: 1
你也可以对整个区间字符串进行参数化。这样就不需要中间转换为 text 类型并进行连接。
query := `
delete from my_table
where expires < (to_timestamp($1) - $2::interval);
`
interval := fmt.Sprintf("%d minutes", mins)
db.Exec(query, timestamp, interval)
英文:
You can also parametrize the entire interval string instead. This removes the need for an intermediate cast to text and concat.
query := `
delete from my_table
where expires < (to_timestamp($1) - $2::interval);
`
interval := fmt.Sprintf("%d minutes", mins)
db.Exec(query, timestamp, interval)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论