Trouble comparing to_char(timestamp, ‘day’) = ‘monday’

In a PostgreSQL database, I’ve got a table payment with a column payment_date of type timestamp. My goal is to count payments made on Monday. The following query:

SELECT TO_CHAR(payment_date, 'day') FROM payment;

gives result such as:

thursday 
friday   
friday   
monday   
tuesday  

However when I try to count Mondays like this:

SELECT COUNT(*) FROM payment
WHERE TO_CHAR(payment_date, 'day') = 'monday';

the result is 0 even though the previous query shows it should be greater than 0. What’s wrong about the second query?

Answer

Postgres docs says that “full lower case day name (blank-padded to 9 chars)”

May this select solve the problem? If there are trailing spaces, maybe your comparison fails.

SELECT COUNT(*) 
FROM payment 
WHERE RTRIM(TO_CHAR(payment_date, 'day')) = 'monday';