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
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:
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$;
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:
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.
(dateadd(dd,i,'20090101')). also addendafterend loop.