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
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 equivalentfloat8
can. Also, namesinterval
,time
andtimestamp
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.)
date()
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
literals . 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 syntaxtype 'string'
is that it does not work for array types; use::
orCAST()
to indicate the type of the array constant.
source to share