I have one table that contain d_date column. In this column, contain date value only.
My question is "I want to find missing dates from this column(d_date)".
example: This column contains dates from "1-Jan-2007" to "7-Jan-2007" then i want to find missing date between "1-Jan-2007" to "10-Jan-2007" and my result should be "8-Jan-2007", "9-Jan-2007", "10-Jan-2007".
So, how can i get this result?
You can compare a date series created with the function generate_series(start, stop, step interval) to dates in your table:
SELECT * FROM generate_series('2007-01-01', '2007-01-10', interval '1 day') AS dates
WHERE dates NOT IN (SELECT d_date FROM your_table);
More info on function generate_series from PostgreSQL documentation: 9.24. Set Returning Functions.
You can use WITH RECURSIVE to build a table of dates and then select dates that are not in your table:
WITH RECURSIVE t(d) AS (
(SELECT '2015-01-01'::date)
UNION ALL
(SELECT d + 1 FROM t WHERE d + 1 <= '2015-01-10')
) SELECT d FROM t WHERE d NOT IN (SELECT d_date FROM tbl);
Edit: notice that a recursive CTE might be an overkill here. Epikuros's answer provides a simpler solution with generate_series.
