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 have two scenarios in mind. There are workarounds for both of them, but it would be nice if they were not necessary. 1. In a UNIQUE column, I would like a value of 'a' to be rejected if there is already a value of 'A'. Workaround - create a unique index on LOWER(col_name). 2. I would like WHERE col_name = 'x' and WHERE col_name LIKE 'x%' to find 'X' and 'X1'. Workaround - WHERE LOWER(col_name) = 'x' and WHERE col_name ILIKE 'x%'.
You could define your own type if you want to go to that level of effort. Or, you could just decide only to allow upper-case values (or lower-case).
-- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
