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

The proper and simple solution is:

SELECT to_char(b.foo,'DD/MM/YYYY') as foo, b.bar
FROM   baz b
ORDER  BY b.foo;

The formatted date column foo is a completely new column for the query planner, that happens to conflict with the table column foo. In ORDER BY and GROUP BY clauses output column names take precedence over input columns. The unqualified name foo would refer to the output column.

To use the original table column in the ORDER BY clause, just table-qualify the column.

I table-qualified all column names to be clear. Would only be required in the ORDER BY clause in this case. Table alias b is just for convenience.

Answer 2

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