PGSQL: Adding a variable interval - Syntax error

Question

I have a quick question. I am fairly new to pgsql and I am unable to figure out how to fix the syntax error below.

Here is what I am trying to do

start_date := '2011-01-01'::date;
end_date   := '2011-03-01'::date;
duration := '6 months'

    while start_date < end_date loop
        window_start_date = start_date;
        window_end_date = window_start_date + interval||duration||;

    end loop;

However I keep getting a syntax error.

ERROR:  column "interval" does not exist
LINE 1: SELECT   $1  + interval|| $2 ||
                       ^
QUERY:  SELECT   $1  + interval|| $2 ||

What am I doing wrong. Any help would be much appreciated

Your error message doesn't match the code you posted, there is no select statement in your code. — user330315
– user330315, Commented Mar 30, 2014 at 21:54
@a_horse_with_no_name That's normal - PL/PgSQL assignments are translated into SELECT ... INTO by the parser. The error messages unfortunately don't get converted back. — Craig Ringer
– Craig Ringer, Commented Mar 31, 2014 at 1:35
@Abhi What did you expect || at the end of the statement to do? It doesn't make any sense at all, || is the concatenation operator. I think you're trying to write window_start_date + duration * INTERVAL '1' day but it's hard to be sure. — Craig Ringer
– Craig Ringer, Commented Mar 31, 2014 at 1:36

Erwin Brandstetter · Accepted Answer · 2014-03-30 22:40:04Z

3

Guesswork (the rest of the function definition is missing).
This would work in PL/pgSQL (which are using behind the curtains):

window_end_date := window_start_date + interval duration;

Or:

window_end_date := window_start_date + duration::interval;

Cast the text value to interval to make it work. But it would be better to declare the variable duration as interval to begin with (maybe that is the case, then drop the cast - information missing).
The assignment operator in plpgsql is :=, not =.
The result is a timestamp, not a date. But it will be coerced to date in your example.

answered Mar 30, 2014 at 22:40

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PGSQL: Adding a variable interval - Syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related