When to_char's output in postgres is converted to Varchar, why is not comparing correctly with other varchars?

Question

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.

Why are you trying to use varchar instead of the normal text? — Bergi
– Bergi, Commented Nov 10, 2021 at 13:44
so that I can use the IN and NOT IN operators, for filtering out different varchars.@Bergi — krtchen
– krtchen, Commented Nov 10, 2021 at 13:54
But 'Thursday' is text (by default) as well, it doesn't need to be varchar. — Bergi
– Bergi, Commented Nov 10, 2021 at 13:55
okay, can you provide some documentation where this is written, it's compatible both with text and varchar in postgres.. — krtchen
– krtchen, Commented Nov 10, 2021 at 14:15
Yes, it's compatible with any textual type, but text is the preferred type in Postgres. — Bergi
– Bergi, Commented Nov 10, 2021 at 14:19

user330315 · Accepted Answer · 2021-11-10 13:41:51Z

1

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

answered Nov 10, 2021 at 13:41

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

When to_char's output in postgres is converted to Varchar, why is not comparing correctly with other varchars?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related