Postgresql complains when spacing is the result of string concatenation

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

but not for interval


So, the following operators work:

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


Now I want to synthesize the lines that are passed after date

and interval

on the substring

-in some other lines (think of a stored procedure where we work with the passed parameter):

This works for date


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


But this fails for interval


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


with the error message:

ERROR:  syntax error at or near "substring"


This actually works if I explicitly point to interval

either with CAST(x as INTERVAL)

or with x::interval


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


or equivalently:

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


Why date TEXT

does it work regardless of how it is placed there TEXT

, but the same thing interval

works with only plain text, but not synthesized.

I'm on Postgres 9.4.


source to share

2 answers

This is because it date

is actually a PostgreSQL function. There is also a function interval

, but you need to quote it :

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


You can also specify the type of cast using the function syntax:

typename ( expression )


However, this only works for types whose names are also valid as function names. For example, double precision

it cannot be used this way, but the equivalent float8

can. Also, names interval

, time

and timestamp

can only be used this way if they have double quotes due to syntactic conflicts . Thus, using function-matched syntax leads to inconsistencies and should probably be avoided.

The functional syntax is actually a function call. If one of the two standard Lithuanian syntaxes is used to perform the conversion at runtime, it will internally call the registered function to perform the conversion. By convention, these conversion functions have the same name as their output type, and thus "functionally similar syntax" is nothing more than a direct call to the underlying conversion function. Obviously this is not something a portable application should rely on.

(My bold accent.)


there may be an exception, most database vendors support something like this.

However, it looks like you can create your query too date()

, because

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


first converts '2015-01-01'

(from text

) to date

, then to text

. This syntax has nothing to do with date

. They can only be expressed as follows:

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


To avoid syntactic ambiguity, the syntax type 'string'

can only be used to indicate the type of a simple literal constant. Another restriction on the syntax type 'string'

is that it does not work for array types; use ::

or CAST()

to indicate the type of the array constant.



I think this is a parenthesis problem as you cannot do this:

SELECT interval ('015 days') :: text

Try using this instead:

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



All Articles