Force Case Sensitive Values?

CarlosinFL · #1 (**permalink**) 01-05-12, 16:03

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.

Pat Phelan · #2 (**permalink**) 01-05-12, 17:22

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

shammat · #3 (**permalink**) 01-05-12, 17:49

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.

JarlH · #4 (**permalink**) 01-09-12, 02:42

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech