Sorting nulls and empty strings together

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

On Mon, Apr 28, 2008 at 08:05:45PM +0300, 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.

I'd say users are being confused by the assumption the nulls and empty
strings are the same when they clearly aren't. Perhaps you should think
which of the two you actually want to mean "empty" and then get rid of
the other possibility.

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)

You could do: create index test_inx on test(coalesce(testcol,''))

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

"Andrus" <kobruleht2@hot.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!