Postgres order column with NaN values

Question

I've got a database in postgres and in few columns (type int) I've got NaN values. When I'm sorting ASC the result is correct, for example:

0
1
2
3
4
NaN

But when I'm sorting DESC I've got:

naN
4
3
2
1
0

I know that Postgres treats NaN values as equal, and greater than all non-NaN values, but Is there a way to get this result?

4
3
2
1
0
NaN

Any ideas?

Answer 1

If your column doesn't contain NULLs, you can unambiguously convert NaNs to NULLs and sort on that:

select *
from some_table
order by nullif(some_column, 'NaN') desc nulls last