Re: Sorting nulls and empty strings together

"Andrus" <kobruleht2(at)hot(dot)ee> writes:

> User interface need to show nulls as empty strings.
> PostgreSQL sorts nulls after all data.
>...
> Select statements are generated dynamically by driver and it is not easy
> to change them to generate order by coalesce( testcol,'').

You could use NULLS FIRST (assuming your collation has '' sorted at the
beginning which I think is normally true). But you would have to switch it to
NULLS LAST if you sort descending...

> 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)

create index test_inx on test(coalesce(testcol,''))

But I bet you'll have trouble using an index at all for the order by. You'll
either be searching on other columns which would have to be leading columns of
every index or you'll be reading the whole table anyways and postgres will
prefer to sort since it's faster.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!