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(dot)klaver(at)aklaver(dot)com