In Postgresql how to order by date while keeping custom date format

Question

The issue is that using to_char will turn order by date into order by ascii. Example:

SELECT foo, bar FROM baz ORDER BY foo;

I would like to format foo using to_char, but doing this will affect the order:

SELECT to_char(foo,'dd/MM/yyyy') as foo, bar FROM baz ORDER BY foo;

Because foo now is of type text. There is a way to correctly do this? Or only in the code?

Answer 1

You can use a different alias for the formatted column:

SELECT to_char(foo,'dd/MM/yyyy') as formatted_foo, 
       bar 
FROM baz 
ORDER BY foo;

As an alternative if you need to keep the foo alias:

select foo,
       bar
from (
  SELECT to_char(foo,'dd/MM/yyyy') as foo,
         foo as foo_date 
         bar 
  FROM baz 
) t
order by foo_date