Re: Case Sensitive "WHERE" Clauses?

On Fri, 27 Sep 2002, Ian Barwick wrote:

> On Friday 27 September 2002 05:19, Tom Lane wrote:
> > Ian Barwick <barwick(at)gmx(dot)net> writes:
> > > Anyone know what the ANSI standard is? I don`t recall any other
> > > database apart from MySQL which default to case-insensitive
> > > CHAR or VARCHAR columns.
> >
> > I believe the spec has a notion of a "collation attribute" attached
> > to character-type columns. You could define a collation that makes
> > comparisons case insensitive and then mark selected columns that way.
> > We don't have anything like that yet, though Tatsuo has been heard
> > muttering about how to make it happen ...
>
> For reference, MySQL treats CHAR and VARCHAR columns as
> case insensitive by default; to be treated as case sensitive, fields
> must be defined or redefined as CHAR BINARY / VARCHAR BINARY.
>
> Personally I prefer handling case (in)sensitivity explicitly in the WHERE
> clause or at application level, though if the standard allows it and it's
> optional, enabling specific columns to be case insensitive in comparisions
> can only be a Good Thing (TM).

AFAICT it's not only a table column thing, it's all the way through, most
times you're specifying a character string of some sort of or another you
can attach an explicit collation with COLLATE. The rules for how this all
works look fairly arcane though. (As an example, it looks like group by
can get them so you might be able to say "group by col1 COLLATE foo" in
order to use the foo collation in order to do the grouping)