Get all days in a month excluding weekends postgresql

Question

I want to write a query sql for postgresql that can basically return me all days of a month excluding weekends.

For example (For 11/2019) :

• First Week: 11/1
• Second Week : 11/4 -> 11/8
• Third Week : 11/11 -> 11/15
• Fouth Week : 11/18 -> 11/22
• Fifth Week : 11/25 -> 11/29

I can't find any postgresql request that can help, this should be automatic instead of putting each time a new date manually.

Answer 1

One method is:

select dt 
from generate_series(date'2019-11-01', date'2019-11-30', interval '1' day) as t(dt)
where extract(dow from dt) between 1 and 5

generate_series() produces a list of all days in the month, then the where clause filters on week days only.