Make H2 treat quoted name and unquoted name as the same
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.
Related videos on Youtube
Michael Butler
A computer science student at the University of Sherbrooke, Quebec, Canada. I love programming more than anything else. My favorite languages are javascript, scala, C++ and C#.
Updated on July 07, 2022Comments
-
Michael Butler almost 2 years
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?
-
Artem almost 12 yearsRead the SQL standard and learn to use UPPERCASE as appropriate, I think. The effect of the quotes is to prevent the default mapping from lower to upper case.
-
Michael Butler almost 12 years@bmargulies does
UPPERCASE
even work with H2? Can't find it on H2's SQL documentation. -
Artem almost 12 yearsNot as a function, just spell them out. "TEST" is what matches plain test.
-
-
Michael Butler almost 12 yearsThank you for your help M. Mueller.
-
Brett Ryan about 8 yearsIs there a way to tell h2 to treat tables created using quoted names to be case insensitive? i.e. if I
create table "test"
will allowselect * from test
to work? -
Brett Ryan about 8 yearsLooking at the compatibility section it describes appending
;IGNORECASE=TRUE
which I have done and recreated tables, however they're still created as they are quoted and remain as case sensitive. Using 1.4.191. -
Brett Ryan about 8 yearsMy bad, that's the collation not the name. I want to create identifiers quoted/unquoted and be able to select quoted/unquoted regardless of case matching. In actuality I'd prefer the reverse that the DB convert everything to lower instead of upper.
-
Ivan almost 8 yearsIf you use uppercase names then you can quote them or not and they should refer to the same thing, i.e. you have a level of fault tolerance. If you are going to quote them, then are you going to quote them how Microsoft Corporation prefers or how Oracle Corporation prefers? If non-uppercase database table and column names are forced on you, then you have little option but to quote them. However if you are the database designer then you can choose to write SQL that will work over a variety of databases types by sticking to what is in common between manufacturers and suppliers where possible.