varchar sort ordering ignore blanks - SOLVED!

From: "Luca Arzeni" <l(dot)arzeni(at)iname(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: varchar sort ordering ignore blanks - SOLVED!
Date: 2015-06-27 22:05:26
Message-ID: trinity-b1456065-4ad9-4cfc-9313-e3915b3c4dc6-1435442725977@3capp-mailcom-lxa05
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

To make a long story short, few years ago I was complaining that the sort order of varchar, in UTF-8, was ignoring blanks, that is that the sort order was:

XXXXa
XXXX a
XXXXA
XXXX A
XXXXà
XXXXb
XXXXB

But, i was in need of a sort order that would place blanks BEFORE the other char, that is I would like to have the following sort order:

XXXX a
XXXX A
XXXXa
XXXXA
XXXXà
XXXXb
XXXXB

Now, using Debian Jessie, I found the solution to this issue. I needed to change the file

/usr/share/i18n/locales/iso14651_t1_common

placing in the proper position the sort order.

This is the output of the diff between the file before and after the modification:

diff iso14651_t1_common.orig iso14651_t1_common

4837d4836
< <U0020> IGNORE;IGNORE;IGNORE;<U0020> # 32 <SP>
5080a5080
> <U0020> <BAS>;<MIN>;IGNORE # 32<SP>

Beware: the change is sistem wide, and regards all character sets and locales...

Browse pgsql-general by date

  From Date Subject
Next Message Larry Meadors 2015-06-28 04:37:44 Weird insert issue
Previous Message Francisco Olarte 2015-06-27 18:00:06 Re: Inserting from multiple processes?