Re: Re: [JDBC] BUG: DatabaseMetaData.getColumns isn't case insensitive

On Tue, 3 Feb 2009, maly.velky@email.cz wrote:

> Hello, thank you very much for your clarification.
>
> So there is no way how to perform case-insensitive search in getColumns() ?

Sorry, no.

>> > supportsMixedCaseIdentifiers: false
>>
>> This is reporting how the server matches up with the SQL standard, not how
>> a particular JDBC API call works.
>>
> So when does this (namely "Retrieves whether this database treats mixed case
> unquoted SQL identifiers as case sensitive" = false) apply? As you say it
> doesn't apply to the getColumns call but when does it? For
> insert/update/select statements only? If it applies for select statements,
> what happens (referring to you example) when I've "select t1 from ...", does
> it select t1, T1, or fail?

jurka=# create table t ("c1" int, "C1" int);
CREATE TABLE
jurka=# insert into t values (1, 2);
INSERT 0 1
jurka=# select c1, C1 from t;
  c1 | c1
----+----
   1 |  1
(1 row)


This is actually against the SQL spec, the spec says unquoted identifiers
should be folded to upper case rather than postgresql's behavior of
folding to upper case.  So a spec complaint database would have retrieved
C1's value of 2.  If you look further at DatabaseMetaData you can see this
called out by storesLowerCaseIdentifiers returning true.

To get both values out the column names must be quoted.

jurka=# SELECT "c1", "C1" from t;
  c1 | C1
----+----
   1 |  2
(1 row)

Supporting that in getColumns would be odd because requiring a column name
to be quoted there isn't something that would happen naturally.  It's
would be especially bad if you also consider that column names can have
quotes in them:

jurka=# alter table t add column """" int;
ALTER TABLE
jurka=# \d t
        Table "public.t"
  Column |  Type   | Modifiers
--------+---------+-----------
  c1     | integer |
  C1     | integer |
  "      | integer |


Kris Jurka