update table with dates with month

Question

There's a table dates_calendar:

 id | date
 -------------------------
 13 | 2016-10-23 00:00:00
 14 | 2016-10-24 00:00:00

I need to update this table and insert dates until the next month counting from the last date in the table. E.g. last date is 2016-10-24 00:00:00 - I need to insert dates till 2016-10-31. After that (the last date now is 2016-10-31) next statement call should insert dates till 2016-11-30 and so on.

Example of my SQL code, but it inserts 30 days all the time.

INSERT INTO dates_calendar (date)
    VALUES (
      generate_series(
        (SELECT date FROM dates_calendar ORDER BY date DESC LIMIT 1) + interval '1 day',
        (SELECT date FROM dates_calendar ORDER BY date DESC LIMIT 1) + interval '1 month',
        '1 day'
      )
    );

I'm using PostgreSQL. As well would be fine to get rid of a duplicated SELECT statement of the last date.

Answer 1

insert into dates_calendar (date)
select dates::date
from (
    select max(date)::date+ 1 next_day, '1day'::interval one_day, '1month'::interval one_month
    from dates_calendar
    ) s,
    generate_series(
        next_day, 
        date_trunc('month', next_day)+ one_month- one_day, 
        one_day) dates;