i am adding nthmonth (2) in my postgresql function , but at the time of execution it showing error "ERROR: operator does not exist: timestamp without time zone + integer" HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. QUERY: SELECT pi_date + nthMonth || ' month ' :: INTERVAL

DECLARE
beginMonth  timestamp;
pi_date     timestamp := to_timestamp('14-Jan-2016 01:50 AM,'DD-MON-YYYY HH:MI AM);
> beginMonth := pi_date  +   nthMonth || ' month ' :: INTERVAL;

1 Answers 11

up vote 1 down vote accepted

It's fairly obvious - the "+" is binding more tightly than the "||" (as it is telling you).

You want something like:

pi_date + (nthMonth || ' months'::interval)

Or, perhaps a little clearer:

pi_date + (nthMonth * interval '1 month')

Not the answer you're looking for? Browse other questions tagged or ask your own question.