INTERVAL add i day is not working in postgresql

Question

I have a table like this:

CREATE TABLE DateInsert(
    DateInsert timestamp without time zone,
    DateInt integer NOT NULL
);

I want insert list day from 2018-01-01 to 2045-05-18 but it give me an erro

"invalid input syntax for type interval:"

CREATE OR REPLACE FUNCTION insertdate() RETURNS integer AS $$
DECLARE i integer := 0;
    d timestamp without time zone := '2018-01-01';
    di integer := 0;
BEGIN
    while i <10000
    LOOP
        d := d + INTERVAL ''+ i::character varying + ' day';
        di := to_char(d , 'yyyymmdd')::int;
        insert into DateInsert(DateInsert,DateInt) values(d, di); 
        i := i+1;
    END LOOP ;
    return i;
END;
$$ LANGUAGE plpgsql;

How can I insert to db with timestamp increase 1 in n day loop?

Code In sql server has been working.

declare @i int=0
declare @d datetime
declare @di int = 0
while @i <10000
    begin
    set @d = DATEADD(DAY, @i, '2018-01-01')
    set @di = cast(CONVERT(VARCHAR(10), @d, 112) as int)
    insert into DateInsert(DateInsert,DateInt) values(@d, @di)
    set @i = @i+1 
end

sticky bit · Accepted Answer · 2019-12-18 02:54:20Z

3

The concatenation operator is || not +. And the prefixed form doesn't seem to like anything else than literals. But you can cast the concatenation expression.

So changing

...
d := d + INTERVAL ''+ i::character varying + ' day';
...

to

...
d := d + (i || ' day')::interval;
...

should work for you.

answered Dec 18, 2019 at 2:54

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

INTERVAL add i day is not working in postgresql

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