how to check if there is an entry for each day without gaps SQL

I have a log table "table1" with values appended every day and need to find "column3" values and dates from "column1"​​, for which the data wasn't recorded. For example, my table looks like this:

Everything is ok with column3 = 'markus', but I need to get something like this as output:

One way of addressing this problem is by:

• rebuilding all combinations of names and dates
• left-joining this table with your original tables
• filtering on records whose table values is null

WITH cte_dates AS (
	SELECT DISTINCT column1 AS "date" FROM tab
), cte_names AS (
	SELECT DISTINCT column3 AS "name" FROM tab
)
SELECT cte_dates.date,
       cte_names.name
FROM       cte_dates
CROSS JOIN cte_names
LEFT JOIN tab
       ON cte_dates.date = tab.column1
      AND cte_names.name = tab.column3
WHERE tab.column2 IS NULL

Output:

Check the demo here.

If gaps can be found among your dates, you need to use generate_series with boundary dates to generate the corresponding calendar in the first cte:

WITH cte_dates AS (
    SELECT "date"
    FROM (SELECT MIN(column1) AS startdt, 
                 MAX(column1) AS enddt 
          FROM tab) boundaries
    CROSS JOIN generate_series( startdt :: timestamp 
                              , enddt   :: timestamp
                              , '1 day' :: interval ) "date"
),
...

Check the demo here.

At first, you must build a time base for the whole period, then cross-join it with distinct of column3 to create all possibilities, then subtract it from your base data as follows:

with cross_sql as (select * from 
(SELECT timebase 
FROM   generate_series(timestamp '2022-07-14'
                     , timestamp '2022-07-17'
                     , interval  '1 day') as timebase) A
cross join					 
(select distinct column3 from table1) B )

select column3, timebase from cross_sql 
except
select column3, column1 from table1;