PostgreSQL how to concat interval value '2 days'
In PostgreSQL I want to concat the current_timestamp with an interval as follows:
select current_timestamp + interval 2||' days'
But when I do, I get an error:
[Err] ERROR: syntax error at or near "2"
LINE 1: select current_timestamp + interval 2||' days'
But if I do it like this, it works correctly:
select current_timestamp + interval '2 days'
Why does one work, but not the other?
With reference to the following page http://www.postgresql.org/docs/8.0/static/functions-datetime.html
Answer
Part of the problem is that the standard SQL expression for intervals quotes the number, but not the keywords. So you have to be careful.
select current_date, current_date + interval '2' day;
--
2012-02-21 2012-02-23 00:00:00
In PostgreSQL, quoting like '2 day' and '2 days' also works. So you might think that '2' || ' days' would be equivalent, but it's not.
select current_date, current_date + interval '2' || ' days';
--
2012-02-21 2012-02-21 00:00:02 days
The solution, as A.H. said, is to cast the result string as an interval.
You can also use a variable in place of 2. This generates a calendar for 2012.
-- 0 to 365 is 366 days; 2012 is a leap year.
select ('2012-01-01'::date + (n || ' days')::interval)::date calendar_date
from generate_series(0, 365) n;
I use that final cast to date, because date + interval returns a timestamp.