Thread: 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
>

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@xythos.com>
To: "Benoit Menendez" <benoitm@pacbell.net>
Cc: <pgsql-jdbc@postgresql.org>; <pgsql-odbc@postgresql.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
> >
>
>

 Hi,

This may sound silly.  Is the TEXT datatype equivalent to a CLOB datatype?

Thanks
Robin

Robin,

TEXT correspondes to CLOB in the same way BYTEA corresponds to BLOB
(variable length storage of upto 1Gig).  However you can also use
LargeObjects for both CLOB and BLOB data as another alternative.

thanks,
--Barry

Robinson wrote:
>  Hi,
>
> This may sound silly.  Is the TEXT datatype equivalent to a CLOB datatype?
>
> Thanks
> Robin
>
>
>
>
> ------------------------------------------------------------------------
> *Do You Yahoo!?*
> Yahoo! Greetings <http://greetings.yahoo.com> - Send FREE e-cards for
> every occasion!