Message-ID: <42F895CC.3070705@archonet.com>
Date: Tue, 09 Aug 2005 12:38:52 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Frank Millman <frank@chagford.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Case sensitivity
References: <VPOP32.1.0e.20050809093619.625.7.1.a40c1f81@chagford.com>
In-Reply-To: <VPOP32.1.0e.20050809093619.625.7.1.a40c1f81@chagford.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Frank Millman wrote:
> Frank Millman wrote:
> 
> 
>>Hi all
>>
>>Is there an LC_COLLATE setting, or any other method, which allows all 
>>data in a database to be treated in a case-insensitive manner?
> 
> 
> I was hoping to stimulate some discussion on this topic, but it seems I will
> have to kick-start it myself and see if anyone responds.
> 
> My area of interest is general accounting/business systems, using a typical
> Western character set. I would imagine that this is a common scenario, but
> it is not universal, so read my comments in this context.
> 
> In the good old days, data entry was always done in upper case, by dedicated
> data entry personnel. These days, it is typically done by a wide variety of
> individuals, who carry out a wide range of tasks, most of which require
> lower case (word processing, email) with the occasional use of the shift key
> to enter a capital letter.
> 
> In this context, here are two undesirable effects.

Martijn has pointed to a case-insensitive type, but I'll add a couple of 
  points.

> 1. The user tries to call up account 'A001', but they enter 'a001'. First
> problem, the system does not find the account. Second problem, the system
> allows them to create a new account with the code 'a001'. Now you have
> 'A001' and 'a001'. This is a recipe for chaos.

The basic problem here is that the value isn't text. This is partly the 
fault of development-systems not having a way to deal with sophisticated 
types in databases.

What should happen is that you define some suitable type "AccountCode" 
which is defined as allowing character data in the form of (e.g.) 
"[A-Z][0-9][0-9][0-9]". That type can cast to/from text but doesn't need 
access to the full range of text-handling functions (e.g. concatenating 
two account-codes is probably meaningless). Of course, you want to 
define this in one place and have both the database constraints and 
user-interface understand what you want.

Ironically, MS-Access does this quite well with its tight coupling of 
user-interface and data storage.


> 2. The user tries to call up a product item using a search string on the
> description. Assume they enter 'Wheel nut'. Assume further that the person
> who created the product item used the description 'Wheel Nut'. Try
> explaining to the user why the system cannot find the item they are looking
> for.

Here, I'm not convinced a case-insensitive type is any more useful than 
just using ILIKE.

--
   Richard Huxton
   Archonet Ltd