Postgresql complains when interval is result of string concatenation

Question

Using this answer to convert a (date + interval) into a date. Only in my case the date and the interval are strings that are the result of slice&dice of other strings. The problem is that it works when I construct the string for the date but not for the interval.

So, the following statements work:

SELECT (date '2013-01-01');
SELECT (interval '53 days');
SELECT (date '2013-01-01' + interval '53 days');

Now I want to synthesize the strings that are passed after date and interval by substring-ing some other string (think stored procedure where we operate on a passed parameter):

This works for date:

SELECT date (substring('2015015' from 1 for 4)||'-01-01')::text;

But it fails for interval:

SELECT interval (substring('2015015' from 5)||' days')::text;

with error message:

ERROR:  syntax error at or near "substring"

It actually works if I cast explicitly to interval either with CAST(x as INTERVAL) or equivalently with x::interval:

SELECT CAST((substring('2015015' from 5)||' days')::text AS INTERVAL);

or equivalently:

SELECT ((substring('2015015' from 5)||' days')::text)::interval;

Why does the date TEXT work regardless of how the TEXT is placed there, but the same with interval only works with direct text, but not with synthesized one.

I'm on Postgres 9.4.

please see this question: http://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days — fthiella, May 08 '15 at 14:14
You may want to use the `make_date` and `make_interval` functions instead of doing string concatenation and casting to a date/interval. — Colonel Thirty Two, May 08 '15 at 14:20

pozs · Accepted Answer · 2015-05-08T20:30:19.773

That's because date is actually a function in PostgreSQL. There is an interval function too, but you need to quote it:

SELECT "interval"(substring('2015015' from 5)||' days');

It is also possible to specify a type cast using a function-like syntax:
typename ( expression )
However, this only works for types whose names are also valid as function names. For example, double precision cannot be used this way, but the equivalent float8 can. Also, the names interval, time, and timestamp can only be used in this fashion if they are double-quoted, because of syntactic conflicts. Therefore, the use of the function-like cast syntax leads to inconsistencies and should probably be avoided.

The function-like syntax is in fact just a function call. When one of the two standard cast syntaxes is used to do a run-time conversion, it will internally invoke a registered function to perform the conversion. By convention, these conversion functions have the same name as their output type, and thus the "function-like syntax" is nothing more than a direct invocation of the underlying conversion function. Obviously, this is not something that a portable application should rely on.

(Bold emphasis is mine.)

date() may be an exception here, most database vendors support something like that.

However, it seems you might build your date() query wrong too, because

SELECT date (substring('2015015' from 1 for 4)||'-01-01')::text;

will first convert '2015-01-01' (from text) to date, then to text. This syntax has nothing to do with the date literals. Those can only expressed like this:

SELECT date '<constant string without any expressions>';

To avoid syntactic ambiguity, the type 'string' syntax can only be used to specify the type of a simple literal constant. Another restriction on the type 'string' syntax is that it does not work for array types; use :: or CAST() to specify the type of an array constant.

score 0 · Answer 2 · answered May 08 '15 at 16:03

0

I think it's a problem of parenthesis, as you cannot have this:

SELECT interval ('015 days')::text

Try with cast instead:

SELECT (substring('2015015' from 5)||' days')::interval::text

answered May 08 '15 at 16:03

Jack

1,488
11
21

Postgresql complains when interval is result of string concatenation

2 Answers2