sql - Make H2 treat quoted name and unquoted name as the same

Question

H2 seems to make a difference between name with quote and name without quote. Is there a way to make it treat them the same way?

Here's the tests that I've done :

CREATE TABLE test (dummy INT);
CREATE TABLE "testquote" (dummy INT, "quotedDummy" INT);

Here are the queries :

SELECT * FROM test; --work
SELECT * FROM "test"; -- doesn't work
SELECT * FROM "testquote"; --work
SELECT * FROM testquote; --doesn't work
SELECT dummy FROM "testquote"; --work
SELECT quotedDummy FROM "testquote"; --doesn't work
SELECT "quotedDummy" FROM "testquote"; --work

What can I do to make those queries work with H2?

Answer 1

Quotes names in H2 are case sensitive, as required by the SQL specification. That means this will work:

CREATE TABLE "testquote" (dummy INT, "quotedDummy" INT); 
SELECT * FROM "testquote";

but this will not:

SELECT * FROM "TestQuote";
SELECT * FROM "TESTQuote";
SELECT * FROM "TESTQUOTE";

Unquotes names are not case sensitive in H2. They are normally converted to uppercase (as in Oracle and other databases). That means the statements

CREATE TABLE test (dummy INT);
SELECT * FROM test;

are the same as

CREATE TABLE "TEST" ("DUMMY" INT);
SELECT * FROM "TEST";

In that H2 behaves in the same way as Oracle. This is a bit different on how other databases like MySQL and PostgreSQL deal with identifier names. H2 has a compatibility feature: If you append ;DATABASE_TO_UPPER=FALSE to the database URL, unquotes identifiers are not converted to uppercase, that means they are case sensitive as well. But you need append this when creating the database, and each time you use it (if you append the setting for existing databases, the identifiers of existing objects are already converted to uppercase).

By the way, this has nothing to do with the function UPPER, which is meant for data. Your question is about identifiers, not data.