Make H2 Treat Quoted Name and Unquoted Name as the Same

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.

Column not found at liquibase migration for H2 in-memory database

PostgreSQL and few other DBMS are different from others: they convert unquoted identifiers to lower case. H2 and many others convert unquoted identifiers to upper case and such behavior is actually a standard one.

If you want to use both DBMS for a some reason, you can add ;DATABASE_TO_LOWER=TRUE to JDBC url of H2, with this parameter H2 will treat case of identifiers in the same way as PostgreSQL does.

But if you want to use only the H2, it would be better not to use such settings and adjust your SQL to use upper case letters in quoted identifiers.

Anyway, mixing quoted and unquoted identifiers is a bad practice. Usually it's better to always quote or never quote them. Note that there are various reserved words and these words cannot be used as unquoted identifiers.

Why does H2 try to load data in database before auto-creating the schema?

Ok i found the solution here Spring Boot is not creating tables automatically

By default, data.sql scripts are now run before Hibernate is
initialized. This aligns the behavior of basic script-based
initialization with that of Flyway and Liquibase. If you want to use
data.sql to populate a schema created by Hibernate, set
spring.jpa.defer-datasource-initialization to true.

Problem solved by adding spring.jpa.defer-datasource-initialization=true in application.properties file.

How to escape single quotes within single quoted strings

If you really want to use single quotes in the outermost layer, remember that you can glue both kinds of quotation. Example:

 alias rxvt='urxvt -fg '"'"'#111111'"'"' -bg '"'"'#111111'"'"
# ^^^^^ ^^^^^ ^^^^^ ^^^^
# 12345 12345 12345 1234

Explanation of how '"'"' is interpreted as just ':

  1. ' End first quotation which uses single quotes.
  2. " Start second quotation, using double-quotes.
  3. ' Quoted character.
  4. " End second quotation, using double-quotes.
  5. ' Start third quotation, using single quotes.

If you do not place any whitespaces between (1) and (2), or between (4) and (5), the shell will interpret that string as a one long word.



Related Topics



Leave a reply



Submit