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

Sorting nulls and empty strings together

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: Sorting nulls and empty strings together
Date: 2008-04-28 17:05:45
Message-ID: fv505r$4df$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-general
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 ?


Andrus. 



Responses

pgsql-general by date

Next:From: Roberts, JonDate: 2008-04-28 17:23:34
Subject: Re: passing a temporary table with more than one column to a stored procedure
Previous:From: Erik JonesDate: 2008-04-28 17:00:52
Subject: Re: PITR problem

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