How do you expose case sensitivity to users?

Question

Okay, I get it. Data in PostgreSQL is case-sensitive. And I know I can use LOWER() to make my queries case-insensitive. I know there might even be a "citext" type in a future version of PostgreSQL. My question is, today, how do you deal with this when designing user interfaces? I'm thinking specifically of uniqueness constraints.

Let's say my application's data looks more or less like a file system (think Google Docs, except that Google Docs actually allows duplicate names :-P). How can I make it easy for our users to understand the fact that they can have duplicate names, if the case is different? I think to most people, it will just seem weird.

Let's pre-emptively get some questions out of the way:

1. I come from a Windows background, so case-insensitivity is how I "think". I now primarily use Mac OS X, which (did you know?) is also case-insensitive. The majority of our users fit into these same two buckets.

2. I'm new to PostgreSQL. Most of my experience is with MySQL, but I've also used Oracle, which is case-sensitive like PostgreSQL. I thought a lot of about this issue then, too, but ultimately left everything as-is and let our users just figure it out.

3. I'm interested in both technical solutions (i.e. making this problem just go away) and UI design solutions (i.e. helping the user feel comfortable with the system).

Summary:

• How do you deal with case insensitivity when designing user interfaces?
• How can I make it easy for our users to understand the fact that they can have duplicate names, if the case is different?

EDIT: I appreciate all the feedback so far. However, if the answer is "don't allow duplicate names if they differ by case", then how do I implement it in PostgreSQL? One solution I've considered is silently maintaining a separate column which is always the LOWER() version of the data, and putting the unique constraint on this column.

Answer 1

You can use citext data type right now. Although it may has more limitations than the future built-in version.

EDIT As for the unique constraint:

CREATE UNIQUE INDEX
    example_unique_idx
ON
    example_table ((LOWER(case_insensitive_field)));

Answer 2

Case sensitivity is forcing your user to deal with issues within the computer world.

Do not expose case sensitivity!

You just told me both Windows and Mac are case-insensitive, they are to reduce confusion to the user.

And the way you wan to handle this is, you preserve the case the user has chosen (user input is sacred), but when you do a search or compare, its always done without case sensitivity.

Answer 3

• How do you deal with case insensitivity when designing user interfaces?

You don't allow users to create objects with the same name when compared case insensitively, unless you have a very specific target audience that already 'gets it.' Regular computer users, Emily Executive, are not going to understand why she has two files for "Quarterly report" and "Quarterly Report" - it's only going to hurt the usability of your product unless the application or use scenarios call for case sensitivity.

In other words, unless it's a requirement, assume case insensitivity.

• How can I make it easy for our users to understand the fact that they can have duplicate names, if the case is different?

If you must do this, force a font that makes it obvious that the names are different. Give a user option box that turns warnings on and off, "This file has a name similar to this other file, are you sure you want to save it as this name?" with it turned on automatically at start (opt out, not opt in to generic warnings)

Answer 4

Perhaps you are not aware of the fact but you can create unique indexes over function. And even make them partial indexes.

For example:

create unique index some_name on users (lower(username));

Will make the username unique regardless of case.

You can also move 1 step further and (for example, this might not be good idea in your environment) make it so that the uniqueness is enforced only to active users:

create unique index some_name on users (lower(username)) where is_active = true;

Also, note that for case insensitive searches you should not use ILIKE. The problem is that ILIKE cannot (for some reason that I don't really understand) use indexes.

So, while it is possible to use functional index to get speedup on queries:

select * from users where lower(username) = '...'

or

select * from users where lower(username) like '...'

(at least for some values of "...")

index will not be used (as far as I know) in:

select * from users where username ilike '...'

Answer 5

Another technical solution to implement case-insensitivity is to create your own type with built-in folding to lowercase. That way, the user (and the application program) cannot make a mistake.