From: Richard Huxton <dev@archonet.com>
To: Frank Millman <frank@chagford.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Case sensitivity
Date: Tue, 09 Aug 2005 12:38:52 +0100
Message-ID: <42F895CC.3070705@archonet.com> (raw)
In-Reply-To: <VPOP32.1.0e.20050809093619.625.7.1.a40c1f81@chagford.com>
References: <VPOP32.1.0e.20050809093619.625.7.1.a40c1f81@chagford.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: dev@archonet.com, frank@chagford.com
Subject: Re: Case sensitivity
In-Reply-To: <42F895CC.3070705@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox