PostgreSQL how to concat interval value '2 days'

Question

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 1

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.

Answer 2

Please try this syntax:

select current_timestamp + ( 2 || ' days')::interval; 

or even this one:

select current_timestamp + 2 * interval '1 day';