PostgreSql: select only weekends

Question

I have a table containing the public holidays

id   | description                 | date
 1   | Ferragosto and Assunzione   | 2012-08-15 00:00:00
 2   | Tutti i santi (Ognissanti)  | 2012-11-01 00:00:00
 ......

I want to select only the holidays occurring on the weekends (Saturday & Sunday) with PostgreSQL.

I know there is a function called extract and isodow to extract the day of week but I don't know how to use it in my case:

 select extract(isodow from date '2016-12-12')

Answer 1

You could use query which is similar to this one:

SELECT * 
  FROM table
 WHERE EXTRACT(ISODOW FROM date) IN (6, 7)