Case senstivity of column names

[Gareth Boden]

If I create a table as follows:

CREATE TABLE foo (bar INT, baz VARCHAR(10));

Then I can't select from it like this:

SELECT bar, "baz" FROM foo;
Column baz not found; SQL statement: SELECT bar, "baz" FROM foo
[42S22-44]

SELECT bar, baz FROM "foo";
Table foo not found; SQL statement: SELECT bar, baz FROM
"foo" [42S02-44]

SELECT bar, baz FROM "FOO";
BAR BAZ
(no rows, 0 ms)

Anyway of stopping H2 from forcing upper case on the column names when
the table is created? Or of making it case insensitive on quote column
names? It seems logically wrong that I cannot query a table and/or
column with the same name I created it with.

Gareth

[Thomas Kellerer]

> It seems logically wrong that I cannot query a table and/or
> column with the same name I created it with.

There is nothing wrong with this.

This is how the ANSI SQL standard requires the usage of (quoted)
identifiers:
Not quoted --> Case insensitive
Quoted --> Case sensitive

In fact all major DBMS (Postgres, Oracle, Firebird, DB2, SQL Server,
Sybase, Ingres) behave like this. At least when it comes to quoted
identifiers.
Some actually chose to store identifiers in lower case - which is not
standard compliant. The ANSI standard actually requires that all non-
quoted identifiers are stored in upper case.

But all adhere to the standard that quoted identifiers are case-
sensitive.

[Thomas Mueller]

Hi,

That's right, the ANSI standard says (as far as I know) unquoted
identifiers should be stored uppercase. There are a few
DatabaseMetaData methods to find out how the database stores
identifiers, see methods DatabaseMetaData.stores*Identifiers.

Thomas