英文:
Go postgres prepared statement with interval parameter not working
问题
我正在尝试使用Go的pq库将以下内容简单地插入到Postgres数据库中(我正在按照《Let's Go》一书的示例,但是使用的是Postgres而不是MySQL):
title := "O snail"
content := "O snail\nClimb Mount Fuji,\nBut slowly, slowly!\n\n - Kobayashi"
expires := "7"
stmt := `INSERT INTO snippets (title, content, created, expires)
VALUES($1, $2, current_timestamp, current_timestamp + interval '$3 days')`
_, err := m.DB.Exec(stmt, title, content, expires)
但是这会抛出一个错误:
handlers.go:56: pq: got 3 parameters but the statement requires 2
实际上,如果我只是从最后一行中删除expires
,并传入2个参数,它可以正常工作,并且间隔值被视为'3 days'。
这有什么道理?为什么$
被忽略了?我以为这是由于单引号的转义问题,所以我尝试了'$3 days'
,但是它会报错pq: syntax error at or near "\"
。如果我尝试转义单引号\$3 days\
,也会出错。有什么建议吗?
英文:
I'm trying to simply insert the following into the postgres database by using Go's pq library (I'm following Let's Go book, but using Postgres instead of mySQL):
title := "O snail"
content := "O snail\nClimb Mount Fuji,\nBut slowly, slowly!\n\n - Kobayashi"
expires := "7"
stmt := `INSERT INTO snippets (title, content, created, expires)
VALUES($1, $2, current_timestamp, current_timestamp + interval '$3 days')`
_, err := m.DB.Exec(stmt, title, content, expires)
But this throws an error:
handlers.go:56: pq: got 3 parameters but the statement requires 2
In fact, if I just remove expires
from the last line, and pass in 2 parameters, it works, and the the interval value gets treated as '3 days'.
How does this make any sense? Why is $
ignored? I thought it's due to some escaping stuff with single quotes, so I tried '\$3 days'
, but it gives an error pq: syntax error at or near "\"
. I also get an error if I try to escape single quotes \'$3 days\'
. Any suggestions?
答案1
得分: 6
你可以通过将interval '1 day'
与一个绑定参数相乘,来实现你想要的正确间隔。
stmt := `INSERT INTO snippets (title, content, created, expires)
VALUES($1, $2, current_timestamp, current_timestamp + interval '1 day' * $3)`
英文:
You may multiply interval '1 day'
by a bound parameter, to achieve the correct interval you want.
stmt := `INSERT INTO snippets (title, content, created, expires)
VALUES($1, $2, current_timestamp, current_timestamp + interval '1 day' * $3)`
答案2
得分: 4
你还可以将参数转换为interval
,并将完整的时间间隔字符串作为查询参数传递:
expires := "7 days"
stmt := `INSERT INTO snippets (title, content, created, expires)
VALUES($1, $2, current_timestamp, current_timestamp + $3::interval)`
_, err := m.DB.Exec(stmt, title, content, expires)
这样做还可以更好地控制如何确定expires
的值,例如使用fmt.Sprintf
或字符串拼接,以便将时间单位作为外部提供的参数。
英文:
You can also cast the parameter to interval
and pass the complete interval string as the query argument:
expires := "7 days"
stmt := `INSERT INTO snippets (title, content, created, expires)
VALUES($1, $2, current_timestamp, current_timestamp + $3::interval)`
_, err := m.DB.Exec(stmt, title, content, expires)
This also gives you more control about how you determine the value of expire
, for example with fmt.Sprintf
, or string concatenation, in case you want to have also the unit of time as an externally supplied argument.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论