From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Ian Barwick <barwick@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Case Sensitive "WHERE" Clauses?
Date: Fri, 27 Sep 2002 00:37:08 -0700 (PDT)
Message-ID: <20020927002843.O35766-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200209270925.36160.barwick@gmx.net>
On Fri, 27 Sep 2002, Ian Barwick wrote:
> On Friday 27 September 2002 05:19, Tom Lane wrote:
> > Ian Barwick <barwick@gmx.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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, barwick@gmx.net, tgl@sss.pgh.pa.us
Subject: Re: Case Sensitive "WHERE" Clauses?
In-Reply-To: <20020927002843.O35766-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox