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?

score 5 · Accepted Answer · answered Feb 10 '18 at 20:54

5

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

answered Feb 10 '18 at 20:54

melpomene

NULLS LAST did the trick :) But does it work for text values too? – Daniel Koczuła Feb 10 '18 at 21:19
@DanielKoczuła How do you get NaN in a text column? – melpomene Feb 10 '18 at 21:26

1 Answers1