"ERROR: syntax error at end of input" when running Do command

Question

I was trying to create a calendar table. The query below. I get the error in the title. Can someone explain why?

create table if not exists test_calendar (test_date date primary key);

do $$
declare
    i intEGER := 0 ;--, date datetime := '20090101';
Begin
    while i <= 10 loop
        begin
            insert into test_calendar (test_date) values (dateadd(dd,i,'20090101'));
            i := i + 1;
        end;
    end Loop;
$$ ;

SELECT * FROM test_calendar

Answer 1

The immediate cause of the error message is the missing END at the end.

DO
$do$
DECLARE
   i int := 0;
   _date date := '2009-01-01';
BEGIN
   WHILE i <= 10 LOOP
      BEGIN  -- expensive noise
         INSERT INTO test_calendar (test_date)
         VALUES (_date + i);
         i := i + 1;
      END;
   END LOOP;
END  -- !
$do$;

A semicolon after the final END is optional.

But there is more:

• There is no dateadd() function in Postgres (like in SQL Server). You can simply add integer to a date for your purpose.

• The expensive nested block serves no purpose. Remove it.

• Such a loop is simpler and cheaper with FOR. See:

  • Postgres FOR LOOP

DO
$do$
DECLARE
   _date date := '2009-01-01';
BEGIN
   FOR i IN 1..10  -- i defined implicitely
   LOOP
      INSERT INTO test_calendar (test_date)
      VALUES (_date + i);
   END LOOP;
END
$do$;

Proper way

All of the above is just proof of concept. What I really would do:

INSERT INTO test_calendar (test_date)
SELECT generate_series(timestamp '2009-01-01'
                     , timestamp '2009-01-11'
                     , interval '1 day');

db<>fiddle here

A set-based solution is generally better for this. Use generate_series() in Postgres. Detailed explanation:

• Generating time series between two dates in PostgreSQL

A single multi-row INSERT is much cheaper than many single-row INSERT commands.

The generated timestamp values are cast to date in the assignment. In other contexts you may need to cast explicitly.

Typically, you don't even need a persisted calendar table at all in Postgres. Just use generate_series() directly. Might be even faster.