Go postgres prepared statement with interval parameter not working-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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