Go postgres准备好的语句与间隔参数不起作用

huangapple go评论73阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2022年3月7日 16:43:01
  • 转载请务必保留本文链接:https://go.coder-hub.com/71378392.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定