Sorting NULL values in PostgreSQL [duplicate]

Question

In PostgreSQL, I'm using ORDER BY on columns of various types (NUMERIC, TEXT, BOOLEAN, TEXT[]). I'm designing an AJAX GUI where the users may interactively sort records by individual columns (in both ascending and descending order, using familiar ▲▼ symbols in table heading).

The problem is that some rows contain NULL values. Generally, there is only few such rows (100/10000) and they contain mostly erroneous data which I'm not willing to hide, but also not to show so much. What I want is the NULL values to always fall at the end of the list when using ORDER BY (user will see them only is she navigates to the last page in pagination), no matter whether the order is descending or ascending.

For numeric attributes, by example, I found out that NULL values seem to be considered the greatest, and hence appear at the top of the list when sorting descendingly. That's exactly what I don't want.

I came with one ugly hack, which I'm ashamed to show, but will do the work (in python):

"COALESCE(value, '%sINFINITY')" % ('-' if sort_order == 'descending' else '')

I would keep this ugly guy in my code if it was general enough. But it's obviously not. It works for integers, doubles and timestamps, but not for texts, booleans etc. Simply because there is no (at least I haven't found any) maximal string constant that would be greater than all the other strings, such as infinity is the greatest of all numbers. Indeed, I could put 'ZZZZZ' into the code, but that obviously so extremely unhygienic that I decided to ask here at SO :)

Is there any other, elegant way of doing what I want?

Answer 1

Use:

select ...
from ...
order by the_column nulls last;

Read about Sorting Rows in the docs.