Re: Case folding in DatabaseMetaData

Let's just say that this makes office applications fail with the existing
ODBC driver unless you override the ~~ (like) operator in the database to
use the case insensitive version of like with:

DROP OPERATOR ~~ (name, text);
CREATE OPERATOR ~~ (PROCEDURE = nameiclike, LEFTARG = name, RIGHTARG = text,
NEGATOR = !~~ );

Benoit

----- Original Message -----
From: "Barry Lind" <barry(at)xythos(dot)com>
To: "Benoit Menendez" <benoitm(at)pacbell(dot)net>
Cc: <pgsql-jdbc(at)postgresql(dot)org>; <pgsql-odbc(at)postgresql(dot)org>
Sent: Wednesday, February 06, 2002 6:19 PM
Subject: Re: [JDBC] Case folding in DatabaseMetaData

> Benoit,
>
> Postgres doesn't fold all table and column names to lowercase. If the
> identifier is enclosed in quotes then the case is preserved. Thus it is
> possible to have three tables all named test but differing in their case:
>
> TEST
> Test
> test
>
> can all exist at the same time. However to reference TEST or Test the
> identifier would need to be quoted (i.e. select * from "TEST"). All
> unquoted references are folded to lowercase.
>
> I haven't seen anything in the jdbc spec on how this should be handled.
> Does anyone know what the spec says should be done in this area, or
> what other databases jdbc drivers do?
>
> thanks,
> --Barry
>
>
> Benoit Menendez wrote:
> > Since PostgreSQL folds all table and column names (to lowercase),
> > shouldn't the DatabaseMetaData calls do the same:
> >
> >
> >
> > for example:
> >
> >
> >
> > create table FOO ...
> >
> >
> >
> > DatabaseMetaData.getColumns(null, null, "FOO", "%") returns nothing
> > since FOO has been folded to lowercase
> >
> >
> >
> > The same goes with other DatabaseMetaData taking table or column names
> > (or patterns)...
> >
> >
> >
> > A fix would be to use the case insensitive like operator when querying
> > system tables...
> >
> >
> >
> > This applies to both ODBC and JDBC as far as I can tell...
> >
> >
> >
> > I am using the latest stable versions of 7.2 and corresponding ODBC and
> > JDBC drivers...
> >
> >
> >
> > Thanks for your comments on the subject...
> >
> >
> >
> > Benoit
> >
>
>