Обсуждение: Joining with calendar table
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?
There is a fiddle here with the code explained above.
Thank you for your time and attentioin - salutations.
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