Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Force Case Sensitive Values?

    I have a table called 'computers' as shown below:

    Code:
    holyghost=# \d computers
                                    Table "public.computers"
     Column |         Type          |                       Modifiers                        
    --------+-----------------------+--------------------------------------------------------
     id     | integer               | not null default nextval('computers_id_seq'::regclass)
     make   | character varying(20) | not null
     model  | character varying(40) | not null
     owner  | character varying(40) | not null
     price  | numeric(7,2)          | not null
    Indexes:
        "computers_pkey" PRIMARY KEY, btree (id)
    Is it possible in SQL to specify that the 1st character of the input value in the field 'make' be capital and not lower case? I'm trying to prevent users from entering data like dell, DELL, DeLl, DEll but only 'Dell'. I don't know if this is even possible considering they're all legitimate values for VARCHAR.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The intuitive answer would be to create a table to store the valid values for Make and to put a foreign key from it into the computers table. This is by far and away the best practice for this kind of problem.

    Knowing that PostgreSQL is your tool of choice, I can offer PostgreSQL Collation as a way to solve this specific problem.

    The ISO Standard does define a way to deal with collation issues, which is documented by Joe Celko in SQL For Smarties Advanced SQL Programming, but other than Mimer I don't know of any SQL implementation that uses it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    If you want to do that in order to make searches more flexible you can either use the collation solution provided by Pat, or search by applying e.g. a lower() function:
    Code:
    where lower(make) like 'dell%'
    Note that you will need to create a function based index to make that search fast.

    Probably the best (in terms of user experience solution) would be to use PostgreSQL's full text search capabilities.

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    To stop unwanted values using a check constraint:
    create table computers (make character varying(20) collate english_cs,
    constraint make_capitalized
    check (make = upper(substring(make from 1 for 1)) || lower(substring(make from 2))))

    (where english_cs is i case sensitive collation)

    But I'd rather use a trigger which converts the input value at insert, so whatever case given is stored as Dell.

    And, as Pat Phelan says, a foreign key to a computer brands table is a good idea.

Posting Permissions

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