Go postgres prepared statement with interval parameter not working

user_8497136 0
Viewed 250

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?

go postgresql
2 Answers

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

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.

user_41088030
Related