Sorting null values ​​in PostgreSQL

In PostgreSQL, I am using ORDER BY

for columns of different types ( NUMERIC

, TEXT

, BOOLEAN

, TEXT[]

). I am developing an AJAX GUI where users can interactively sort records by individual columns (both ascending and descending) using the familiar ▲ ▼ characters in the table header.

The problem is that some strings contain values NULL

. As a rule, there are only a few such lines (100/10000), and they contain mostly erroneous data that I do not want to hide, but also do not show as many. I want the values ​​to NULL

always end up at the end of the list when used ORDER BY

(the user will only see them if it navigates to the last page in the pagination), regardless of whether the order is descending or ascending.

For numeric attributes, for example, I've found that the values NULL

seem to be considered the largest and therefore appear at the top of the list when sorted in descending order. This is what I don't want.

I came with one ugly hack that I'm ashamed to show, but I will get the job done (in python):

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

      

        
        
        
      

    

I would keep this ugly guy in my code if it was generic enough. But this is clearly not the case. It works for integers, doubles, and timestamps, but not for texts, booleans, etc. Just because there is no (at least I haven't found) maximum string constant that is greater than all other strings, such as infinity is the largest of all numbers. Indeed, I could put 'ZZZZZ'

in the code, but this is obviously so extremely unhygienic that I decided to ask here on SO :)

Is there another, elegant way to do what I want?