Message-ID: <4487285A.5050104@fer.hr>
Date: Wed, 07 Jun 2006 21:26:18 +0200
From: Ivan Voras <ivoras@fer.hr>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050921)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Curious sorting puzzle
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

The situation is this: we're using a varchar column to store 
alphanumeric codes which are by themselves 7-bit clean. But we are 
operating under a locale which has its own special collation rules, and 
is also utf-8 encoded. Recently we've discovered a serious "d'oh!"-type 
bug which we tracked down to the fact that when we sort by this column 
the collation respects locale sorting rules, which is messing up other 
parts of the application.

The question is: what is the most efficient way to solve this problem 
(the required operation is to sort data using binary "collation" - i.e. 
compare byte by byte)? Since this field gets queried a lot it must have 
an index. Some of the possible solutions we thought of are: replacing 
the varchar type with numeric and do magical transcoding (bad, needs 
changes thoughout the application) and inserting spaces after every 
character (not as bad, but still requires modifying both the application 
and the data). An ideal solution would be to have a 
"not-locale-affected-varchar" field type :)