Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12

    Question Unanswered: Case Sensitive Data

    Well, I have my new plaything (PostgreSQL 8.2) up and running, and it's smoking fast. I actually thought I was going to be able to get away with altering very, very few of my queries, and am hoping that's still the case. But I've run into a little snafu that I'm hoping there's an easy solution for...

    I tried to login using the new database and it kept denying the connection, saying that my credentials were invalid. It took me a few minutes, but I figured it out: the user name that was being requested wasn't in the same case that it was originally entered into the db as. Now, I knew about the case-sensitivity regarding column names, but for data? That seems counter-productive to me, as people are going to use all sorts of different case combinations when they enter search phrases.

    My queries do not use brackets or anything like that, they're just basic SQL syntax. For instance,

    SELECT userid FROM tbl_user
    WHERE user_name = '#form.user_name#'
    AND user_psswd = '#form.user_psswd#'

    Is there any way to get around the case-sensitivity issue other than going thru my queries and adding a lower() function to everything?


    Thanks in advance.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Yes. Look up the ILIKE operator in the 8.2 documentation.

    Code:
    SELECT userid FROM tbl_user 
    WHERE user_name ILIKE '#form.user_name#' 
    AND user_psswd = '#form.user_psswd#'
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12
    loquin,

    Thanks again for the replies. Wowsers. I had no idea that it would make the imported data be case sensitive. Does this match the behavior if it were a table that I created from scratch and just entered data into (as opposed to the imported data)?

    I'm sure there's a really good reason for making the data case sensitive, I just haven't read or heard any arguments for it yet. I'm coming from a SQL Server world where case doesn't matter at all - unless you explicitly tell the db to be case sensitive. And actually, even MySQL is not case sensitive (or at least not the installations I've worked with, which are limited in number).

    Is there by chance any way to turn OFF the case sensitivity?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Well, most of the systems that I work with have been configured to be case sensitive. It's a bit less efficient for the server to be case insensitive,so a heavily loaded server might benefit.

    It's desirable for passwords to be case sensitive, as the number of permutations increase dramatically for a given password length with those additional 26 possible characters.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12
    loquin,

    I wholeheartedly agree that a password should be a case sensitive check, without a doubt. My apps always do that. But that kind of illustrates my point: *my apps handle it*. I believe that case rules should fall on the shoulders of the app, not the database. I have no problem whatsoever with there being a case sensitive datatype, or an option on the field/column to make it case sensitive... hell, I'd fully support that. But to just blanketly make data case sensitive? (Can you tell I come from a MS SQL background where the default collation is case-insensitive? )

    For instance, take a search form. I think it's insane to expect people to use the same case in their search string as was entered into the database.

    Anyways, this is literally only my second day of exposure to Pg, and I'm loving it with the exception of this case sensitivity thing. I guess either A) I'll have to continue to bite the MS SQL $$$ bullet, or B) I'll have to write a second set of files that are set to handle the case-sensitive nature of Pg. (Leaning toward the latter... or at least giving it a go.)

    Thanks for the help!

  6. #6
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12
    Oh, meant to add to that...

    If the case sensitivity is a performance issue, then I *think* I can get over it. I'll just have to modify a ton of queries. One thing is for sure though: the install I did is waaaaaaayyyyyyyy the hell faster than any other non SQL Server database I've used.

    NOTE: Referring to database performance, not installation time.
    Last edited by QuackFuzed; 01-13-07 at 02:54.

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You're right about PostgreSQL being a smokin' database. And, out of the box, it's not tuned that well. (it's conservativly tuned, supporting acceptable performance on older servers.)

    I ran across a reference to the Tweakers.net comparative database testing report a few weeks ago. The tests were slanted towards testing several single/dual processor servers. But, they ran essentially identical tests with pPostgres 8.2 (development release) and MySQL, both v4.1.20 and v5.0.20a.

    Take a peek. It's very informative. MySQL with one user is faster than pg, but as the numbers of concurrent users rise, PostgreSQL passes MySQL at about 8 users. The interesting thing about pg is that once it peaks, it really just levels off. MySQL, on the other hand, starts bogging down as the number of users increase.
    Last edited by loquin; 01-28-07 at 05:09.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12
    Heh... that very article is what got me seriously considering Pg. Someone posted it on a forum/list that I'm on, and I took notice. Plus (cover your ears here), since I'm a lame-@$$ windoze guy, I like the fact that Pg now has native support for windoze.

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Yeah, I'm a windows guy too.

    BTW. You may be in luck. I haven't tried anything with it, but in doing some searching over at postgresql.com, I ran across a reference to an add-on data type - citext (case insensitive text)

    http://gborg.postgresql.org/project/...rojdisplay.php

    I have no idea as to what is involved in adding it, but it sounds like it could do exactly what you want.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Jan 2007
    Location
    Texas
    Posts
    12
    Wow... cool find. I am going to research it a bit and see if it will work on 8.2. Also want to see if it will work in place of varchar()... errr, character varying (). :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •