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?

melpomene · Accepted Answer · 2018-02-10 20:54:40Z

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, 2018 at 20:54

melpomene

86.2k8 gold badges97 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NULLS LAST did the trick :) But does it work for text values too?

@DanielKoczuła How do you get NaN in a text column?