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?

Confirmatory

Read 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. – Spoonbill

@Spoonbill does UPPERCASE even work with H2? Can't find it on H2's SQL documentation. – Confirmatory

Not as a function, just spell them out. "TEST" is what matches plain test. – Spoonbill

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.

Sapper

Is 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 allow select * from test to work? – Unscientific

Looking 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. – Unscientific

My 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. – Unscientific

If 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. – Mcdavid

Answer 2

I was having some weird issue with Hibernate and H2 where if I specified catalog & schema names in @Table annotation, the generated SQL would use the wrong case for the quoted table name causing it to fail. It's probably a defect, but I found a work-around which I'm sharing here.

import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl;

public class H2IdentifierNamingStrategy extends PhysicalNamingStrategyStandardImpl {
    private static final long serialVersionUID = 1L;

    @Override
    public Identifier toPhysicalTableName(Identifier name, JdbcEnvironment context) {
        if(name==null) return null;
        else return new Identifier(name.getText().toLowerCase(), name.isQuoted());
    }
}

Ari