Order By Date desc evaluates 01-01-1970 higher then everything else

Question

I have the following simplified query

SELECT 
   id
   to_char(execution_date, 'YYYY-MM-DD') as execution_date
FROM schema.values
ORDER BY execution_date DESC, id DESC

execution_date can be null.

If no value is present in execution_date it will be set to 1970-01-01 as default. My problem is, that the following table values will lead to a result where 1970-01-01 is treated as the newest date.

Table:

id	execution_date
1
2	2020-01-01
3	2022-01-02
4

Result I would expect

id	execution_date
3	2022-01-02
2	2020-01-01
4	1970-01-01
1	1970-01-01

What I get

id	execution_date
4	1970-01-01
1	1970-01-01
3	2022-01-02
2	2020-01-01

How can I get the correct order and is it possible to easily return an empty varchar if the date is empty?

Answer 1

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=5d1fc31a3cf2d3121092f2446cce87e5

SELECT 
   id,
   to_char(coalesce( execution_date, '1970-01-01'::date), 'YYYY-MM-DD') as execution_date
FROM values1
ORDER BY execution_date DESC, id DESC;