Joining with calendar table

From: Pól Ua L(dot) <dragam(at)protonmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Joining with calendar table
Date: 2022-04-19 07:34:33
Message-ID: foxk5R7fJ6dretSKWPR1Dz2HNNgnX6K_QzjWjDOZxOYH3MGSNLg-1d_pK-Su7zTYP0kh_QAD1UnosyFQXm0Jkwlg3QfmISA8_pm485DRmWo=@protonmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9a0220303051dd5da57cd1bd5b132e94

Thank you for your time and attentioin - salutations.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas, Richard 2022-04-19 11:59:41 RE: PostgreSQL 10.20 crashes / Antivirus
Previous Message Rob Sargent 2022-04-19 06:56:23 Re: oldest xmin is far in the past :: BUT xmin is not available in system