The context is connecting to a Postgres db from a rest server.
To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is older/newer than an arbitrary value.
In the example query below, the table structure is simple – name
is of type text
, and creation_date
is of type timestamp
. So when I do something like
server_pg_module:query("select name from new_table where
current_timestamp - creation_date < '6 days'")
it works nicely. But what I really want to do is get that value of 6 days
from the server. So I try something like
server_pg_module:query("select name from new_table where
current_timestamp - timestamp < $1", ["6 days"]
it throws an error. I tried '6 days'
, "'6 days'"
and a few other concoctions, all throw errors. So to check I added a new column interval
of type interval
and tried a query like
server_pg_module:query("insert into new_table (name, interval) values ($1, '3 day')", ["fooo"]).
which works, but
server_pg_module:query("insert into new_table (name, interval) values ($1, $2)", ["fooo", "3 days"]).
breaks. For good measure, in addition to the concoctions like "'3 days'"
mentioned above I also tried $2::interval
(which I am not sure is legit), but it doesn't work.
Thus I believe it might either have something to do with expressing an interval in a param query, or something peculiar about the module I am using. Any ideas on what causes the trouble and how to do this sort of thing would be appreciated. Or may be it can be narrowed down that the problem is not with pg but with the module, then I have to address it elsewhere.
Postgres version: 10.x
The module I am using is pgo (for the Erlang programming language) https://github.com/SpaceTime-IoT/pgo. The error message I get (when I pass "2 days"
or "'2 days'"
as the query parameter) looks like:
{error,{pgsql_error,#{code => <<"08P01">>,file => <<"pqformat.c">>,
line => <<"575">>,
message => <<"insufficient data left in message">>,
routine => <<"pq_copymsgbytes">>,severity => <<"ERROR">>,
{unknown,86} => <<"ERROR">>}}}
And when I pass '2 days'
as the parameter, it throws a badarg
error.
Best Answer
TLDR: Skip to chapter "Superior query" below.
You didn't disclose the module you are working with, but the problem is obviously one of type casting. Looks like your parameter is passed as typed value, and I assume
text
orvarchar
. There is no implicit type cast fortext
->interval
:db<>fiddle here
If my assumptions are correct you should see an error message like:
I am pretty confident your undisclosed module has ways to pass a different data type or an untyped string literal. Postgres does offer this functionality.
You also state:
This is odd, because an explicit type cast should also work.
Demo
The same is true for prepared statements:
db<>fiddle here
Superior query
All this aside, neither of your queries can use an index (not "sargable"). Use something like this instead!
You can multiply an
interval
withinteger
.Or if you figure out the problem with passing typed parameters:
Using
localtimestamp
to point out that the "current time" depends on your current time zone setting with the typetimestamp
. Details: