search

lib / pq

Pure Go Postgres driver for database/sql
https://pkg.go.dev/github.com/lib/pq
MIT License
9.04k stars 909 forks source link

Issue with ::interval - pq: syntax error at or near "$3" #1102

Closed jonleopard closed 1 year ago

jonleopard commented 1 year ago

The query below throws the following error: pq: syntax error at or near "$3"

INSERT INTO snippets (title, content, created, expires)
VALUES(
    $1,
    $2,
    (now() at time zone 'utc'),
    (now() at time zone 'utc' + interval $3::interval)
)

I'm still getting my PostgreSQL ropes so I apologize if there is something obvious that I'm missing. Any help would be greatly appreciated!

johto commented 1 year ago

interval $3::interval

The interval '..' syntax is not specific to intervals, and only works when followed by a string literal. Simply now() at time zone 'utc' + $3::interval will work here. (Though you might want to consider whether you really want to use timestamp without time zone at all.)

jonleopard commented 1 year ago

Fixed! Thank you so much! Regarding your comment on timestamp without time zone, is defining a TZ generally a best practice? If so, set it to the TZ of the host?