5

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.

  • you don't say which error that gets you. – Jasen Jun 3 '18 at 8:58
  • You should include the verbatim error message and the "module" you are using. I am sure there are much better solutions. – Erwin Brandstetter Jun 4 '18 at 16:44
  • @Jasen yes, done that now... – Yogesch Jun 5 '18 at 9:02
  • it seems like your postgresql syntax is correct. it could be some bug in the Erlang library you are using. – Jasen Jun 6 '18 at 10:52
3

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 or varchar. There is no implicit type cast for text -> interval:

SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_cast
WHERE  casttarget = 'interval'::regtype;
castsource             | casttarget | castcontext
:--------------------- | :--------- | :----------
reltime                | interval   | i          
time without time zone | interval   | i          
interval               | interval   | i          

db<>fiddle here

If my assumptions are correct you should see an error message like:

ERROR:  operator does not exist: interval < text

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:

I also tried $2::interval

This is odd, because an explicit type cast should also work.

Demo

-- interval typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < interval '6 days'; -- works
| ?column? |
| :------- |
| f        |
--untyped string literal
SELECT current_timestamp - timestamp '2018-05-04 18:40' < '6 days'; -- works
| ?column? |
| :------- |
| f        |
-- text typed value
SELECT current_timestamp - timestamp '2018-05-04 18:40' < text '6 days'; -- fails!
ERROR:  operator does not exist: interval < text
LINE 2: ... current_timestamp - timestamp '2018-05-04 18:40' < text '6 ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
-- text typed value, with explicit cast
SELECT current_timestamp - timestamp '2018-05-04 18:40' < ('6 days'::text::interval); -- works
| ?column? |
| :------- |
| f        |

The same is true for prepared statements:

PREPARE fooplan_typed_interval(interval) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_interval('6 days');
| ?column? |
| :------- |
| f        |
PREPARE fooplan_untyped AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_untyped('6 days');
| ?column? |
| :------- |
| f        |
PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;

EXECUTE fooplan_typed_text('6 days');  -- only this one fails!
ERROR:  operator does not exist: interval < text
LINE 3: ...ELECT current_timestamp - timestamp '2018-05-04 18:40' < $1;
                                                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
PREPARE fooplan_typed_text(text) AS
SELECT current_timestamp - timestamp '2018-05-04 18:40' < $1::interval;

EXECUTE fooplan_typed_text('6 days');
| ?column? |
| :------- |
| f        |

db<>fiddle here

Superior query

All this aside, neither of your queries can use an index (not "sargable"). Use something like this instead!

server_pg_module:query("select name from new_table
where creation_date > localtimestamp - interval '1 day' * $1", [6])

You can multiply an interval with integer.
Or if you figure out the problem with passing typed parameters:

server_pg_module:query("select name from new_table
where creation_date > localtimestamp - $1::interval", ['6 days'])

Using localtimestamp to point out that the "current time" depends on your current time zone setting with the type timestamp. Details:

| improve this answer | |
  • Hi Erwin the "superior query" you suggested works like a charm. Your point on "sargability" is critical though, never knew that word. Many thanks for the detailed answer, especially for the sargable bit. My query could have ruined performance down the line. – Yogesch Jun 5 '18 at 8:56
  • The server module in question is github.com/SpaceTime-IoT/pgo for Erlang. Your diagnosis is correct, it has to do with typecasting, but there might be more to it than that. In Erlang, writing something within single quotes is a way of expressing an "atom", a built in datatype. So when one gives '2 days', the module handling the protocol might be getting confused. I updated the question with the error message it throws. – Yogesch Jun 5 '18 at 9:01
1

I figured out a workaround to achieve the same objective.

The approach leverages the fact that the difference of two dates is an integer; it is straightforward to cast a timestamp into a date, and integer values can be used in parametrized queries from the server to the db.

server_pg_module:query("select name from new_table where 
current_timestamp::date - timestamp::date < $1", [6])
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.