I’m using to_char to get the day from the timestamp in postgres. On converting the result of that timestamp to varchar, because I want to apply some filters of type varchar, it doesn’t give the correct result.
This query –
select * from (SELECT to_char(date (tableA.date), 'Day') AS day from tableA) a ;
gives weekdays in day column, which are days.
This query –
select * from (SELECT pg_typeof( to_char(date (tableA.date), 'Day')) AS day from tableA) a ;
gives text in day column. because the type of it is "text".
And this final query gives 0 rows, it should give all the matching results.
select * from (SELECT to_char(date (table.date), 'Day')::VARCHAR AS day from table) a where day IN ('Thursday');
Actual Output is 0 rows, Expected Output is 10 rows.
>Solution :
to_char(date, 'Day') returns a padded version of the day name, e.g. 'Thursday ' instead of 'Thursday'.
If you want to remove the padding you can either use the FM modifier: to_char(table.date, 'FMDay') or trim: trim(to_char(date, 'Day'))
In general I would be very cautions about using locale specific comparisons e.g. on my computer this comparison would fail even if the padded spaces are removed, as it won’t return Thursday. It’s better to use numeric values, e.g. extract(isodow from date) = 5