Skip site navigation (1) Skip section navigation (2)

Re: Sorting nulls and empty strings together

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Sorting nulls and empty strings together
Date: 2008-04-29 13:05:57
Message-ID: 20080429130557.GC3146@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
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(at)svana(dot)org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.

In response to

pgsql-general by date

Next:From: Gregory StarkDate: 2008-04-29 13:42:02
Subject: Re: Sorting nulls and empty strings together
Previous:From: Martijn van OosterhoutDate: 2008-04-29 13:01:13
Subject: Re: varchar or text

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group