Search Postgresql Archives

Re: Case sensitivity

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux