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?
>
Thanks for all the replies, guys, I really appreciate it.
Here is what I have decided to do. If anyone sees any problems with my
approach, please let me know.
Interesting though the citypes are, I will not use them. If I get anywhere
with the app I am developing (it is making progress, but rather slowly) I
will release it as an open source project. I do not want to make it a
requirement that everyone must install a new datatype before they can use
it.
To handle searching for a row based on a string, I will use "LOWER(colname)
= 'x'" and "LOWER(colname) LIKE 'x%'". AFAICT, the second one is equivalent
to "colname ILIKE 'x%'", provided I force 'x' to lowercase first. I prefer
it as I need to support SQL Server as well, and this should work on both
platforms.
The problem of inserting 'a001' when 'A001' exists is still potentially
there, but it should not occur within my app, due to the way I handle table
maintenance. I do not distinguish between INSERT and UPDATE from the user's
point of view, but allow them to enter a primary key, do a SELECT, and then
assume UPDATE mode if the row exists, and INSERT mode if it does not. As I
will use SELECT WHERE LOWER(colname) = 'a001', it will find 'A001' and go
into UPDATE mode, so there should be no danger of duplication. It does not
feel entirely robust, so I will have to go through my app carefully to see
if I can find any loopholes in this theory.
Two questions.
1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I create
a separate index on LOWER(colname)?
2. I was expecting to have a problem with LOWER(colname) if the column was
of a numeric or date type, but it accepts it without complaining. Is it safe
for me to rely on this behaviour in the future?
Thanks again to everyone.
Frank