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 '
:
'
End first quotation which uses single quotes."
Start second quotation, using double-quotes.'
Quoted character."
End second quotation, using double-quotes.'
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
SQL Azure: What Will Happen If Size of My SQL Azure Get 5Gb
Openrowset for Excel: How to Skip Several Rows
SQL Server Select Column by Number
Always Show Decimal Places in SQL
How to Get the First and the Last Record Per Group in SQL Server 2008
How to Create a Temporary Table from a Type
How to Use Group by on a Clob Column with Oracle
How to Insert into a Table That Specifies a Default Value for Every Column
Ssrs Grey Out Parameter Based on Result from Other Parameter
Using Patindex to Find Varying Length Patterns in T-Sql
Oracle: Can You Assign an Alias to the from Clause
Reference Value of Serial Column in Another Column During Same Insert
Timescaledb: Efficiently Select Last Row
How to List All the Column Names in Netezza