Select COUNT of days between two dates except weekends
Assuming that by "weekend" you mean Saturday and Sunday, this can be even simpler:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10'
                     , interval  '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) &lt; 6;



You don't need an extra subquery level for generate_series(). SRF (set returning functions), also referred to as "table-functions", can be used just like tables in the FROM clause.
Note in particular that generate_series() includes the upper bound in the output, as long as a full interval (3rd parameter) fits. The upper bound is only excluded if the last interval would be truncated, which is not the case with full days.
With the pattern ISODOW for EXTRACT(), Sundays are reported as 7, according to the ISO standard. Allows for a simpler WHERE condition.
Rather call generate_series() with timestamp input. Here is why:


Generating time series between two dates in PostgreSQL

count(*) is slightly shorter and faster than count(the_day), doing the same in this case.


To exclude lower and / or upper bound, add / subtract 1 day accordingly. Typically, you might include the lower and exclude the upper bound:

SELECT count(*) AS count_days_no_weekend
FROM   generate_series(timestamp '2014-01-01'
                     , timestamp '2014-01-10' - interval '1 day'
                     , interval '1 day') the_day
WHERE  extract('ISODOW' FROM the_day) &lt; 6;