Content-Type: text/plain;
  charset="iso-8859-1"
From: Ian Barwick <barwick@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Case Sensitive "WHERE" Clauses?
Date: Fri, 27 Sep 2002 09:25:36 +0200
Cc: pgsql-sql@postgresql.org
References: <a05100312b9b8f9c321b3@[63.172.201.5]>
	<200209270133.35024.barwick@gmx.net>
	<20455.1033096783@sss.pgh.pa.us>
In-Reply-To: <20455.1033096783@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209270925.36160.barwick@gmx.net>

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=20
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=
=20
clause or at application level, though if the standard allows it and it's=
=20
optional, enabling specific columns to be case insensitive in comparisions
can only be a Good Thing (TM).

Ian Barwick
barwick@gmx.net