By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
| version |
|---|
| PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
create table values1(execution_date date, id int);
insert into values1 values(null,1),('2020-01-01',2),('2022-01-02',3),(null,4);
4 rows affected
table values1;
| execution_date | id |
|---|---|
| null | 1 |
| 2020-01-01 | 2 |
| 2022-01-02 | 3 |
| null | 4 |
SELECT
id,
to_char(coalesce( execution_date, '1970-01-01'::date), 'YYYY-MM-DD') as execution_date
FROM values1
ORDER BY execution_date::date DESC, id DESC;
| id | execution_date |
|---|---|
| 4 | 1970-01-01 |
| 1 | 1970-01-01 |
| 3 | 2022-01-02 |
| 2 | 2020-01-01 |
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;
| id | execution_date |
|---|---|
| 3 | 2022-01-02 |
| 2 | 2020-01-01 |
| 4 | 1970-01-01 |
| 1 | 1970-01-01 |