Re: Sorting nulls and empty strings together

[Dennis Muhlestein <djmuhlestein(at)gmail(dot)com>]

Andrus wrote:
> User interface need to show nulls as empty strings.
> PostgreSQL sorts nulls after all data.
>
> create temp table test ( testcol char(10) );
> insert into test values ( null);
> insert into test values ( 'test');
> insert into test values ( '');
> select * from test order by testcol;
>
> This confuses users who expect that all empty columns are together in sorted
> data.
>
> Select statements are generated dynamically by driver and it is not easy
> to change them to generate order by coalesce( testcol,'').
> If there is no other way I can change driver to generate coalesce( 
> testcol,'') as order by expressions.
> However I'm afraid that those order by expression cannot use regular index 
> like
>
> create index test_inx on test(testcol)
>
> in it thus too slow for large data.
>
> How to force PostgreSQL to sort data so that nulls and empty strings appear
> together ?

Well, you could use a case statement to change empty strings to NULL in 
your select:

select case when testcol='' then NULL else testcol end as testcol from 
test order by testcol;

There may be a better way, like a rule or something, but this seemed 
like a quick easy thing to do.

-Dennis