Re: Joining with calendar table

On 4/19/22 00:34, Pól Ua L. wrote:
> 
> 
> Bonjour a tous/Hello all,
> 
> 
> Small problem - I hope it not to trivial for here.
> 
> We created a table, then someone deleted some records and we want to put 
> them back, but we not sure how.
> 
> CREATE TABLE dat AS
>    SELECT
>      GENERATE_SERIES
>      (
>        '2022-03-01'::DATE,
>        '2022-04-18'::DATE,
>        '1 DAY'
>      ) AS jour;
> 
> So, all days from March 01 to Easter Monday.
> 
> Then someone delete the weekends.
> 
> 
> delete from dat where extract(isodow from jour) IN (6, 7);
> 
> 
> 
> But, there are also actvities on these days  also, so I would like to 
> put them back in.
> 
> I think I need a left join with the calendar table, but am not sure how 
> to do this so we have full months again.


> 
> Could someone show me how this to be done please?

BEGIN;
insert into dat select j.a from GENERATE_SERIES
     (
       '2022-03-01'::DATE,
       '2022-04-18'::DATE,
       '1 DAY'
     ) as j(a) left join dat on j.a = dat.jour  where dat.jour is null;

INSERT 0 14


Verify the dates where added then:

COMMIT;

-- 
Adrian Klaver
adrian.klaver@aklaver.com