I suggest you ...

case insensitive

Allow db columns in UTF-8 databases to be case insensitive to sorting / selecting. From what I understand this is not possible. I've looked everywhere. It is possible in lots of other DBs from my experience and would be very useful. There is UPPER but that would reduce performance.

155 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    anonymousanonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    3 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Darren DuncanDarren Duncan commented  ·   ·  Flag as inappropriate

        Case-insensitivity is too-specific a feature. What would be better is the ability to define virtual columns whose values are the result of applying some arbitrary deterministic function to other columns, for example UPPER(), and then these virtual columns must be useable in all the same sense as regular columns, including as members of primary or unique or foreign keys. The syntax for virtual columns would be loosely like a CHECK constraint of the form "v = f(...)" but that it defines the value of v rather than just asserting that v is equal to the result of f(). Kind of like how VIEWs work but on the column level.

      • sauriksaurik commented  ·   ·  Flag as inappropriate

        This is another problem that doesn't seem to be "per column" any more than it is "per database": operator classes should simply be parameterized to allow this to be easily done per use. I provided a much more fleshed out rational in the comments on per-column locales, but I could also easily see wanting to have two indexes over the same column: one case insensitive, and one case sensitive.

        (While this doesn't satisfy case folding, isn't case insensitive already mostly handled by just using lower() and using an expression-based index? This is even an example in the PostgreSQL documentation for CREATE INDEX. ;P)

      • Georges MartinGeorges Martin commented  ·   ·  Flag as inappropriate

        I'd like to add per-column "case folding" to that suggestion. Case insensitive is not always enough to process international text. "Case folding" kind of removes accents from letters. That's an option available in SQL Server and it's very, very useful.
        There's already partial implementations available at: <http://www.flexiguided.de/publications.pgcollkey.en.html> and <http://www.flexiguided.de/publications.utf8proc.en.html>.

      Feedback and Knowledge Base