H2 fails an INSERT or CREATE TABLE with: expected identifier
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statementShort answer
H2 treats words such as USER, VALUE, KEY and ORDER as keywords, so using one unquoted as a table or column name gives a syntax error that expects an identifier. Wrap the name in double quotes, rename it, or give the entity another table name with @Table.
Fix it fast
1Find out which cause you have
Read the statement H2 prints between the quotes of the error. The marker [*] shows where parsing stopped, and the word just before it is the name that H2 refused.
# application.properties, to see the SQL Hibernate sends spring.jpa.show-sql=true2Match what you see, then jump to its fix
- 1
The marker [*] sits right after USER, VALUE, KEY, ORDER or a similar wordA table or column is named with an H2 keywordFix - 2
The failing statement comes from a SQL script or @Query, not from HibernateThe keyword is written in your own SQL (data.sql or a query) without quotesFix - 3
The statements come from Hibernate and you do not want to rename the entityHibernate sends the keyword name without quotesFix
- 1
What does the error look like?
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statementexpected "identifier"
Cause 1 of 3
A table or column is named with an H2 keyword
1Check
H2 lists these words as keywords that cannot be identifiers unless they are quoted. An entity called User maps to a table called USER, which is on that list.
grep -rn "@Entity\|@Table\|@Column" src/main/java2Fix
Give the entity or column a name that is not a keyword. This is the most portable fix.
@Entity
@Table(name = "app_user")
public class User {
@Column(name = "user_value")
private String value;
}3Verify
Restart the app. Tables are created and the INSERT runs.
./mvnw spring-boot:runConfirmed by h2database.com
Cause 2 of 3
The keyword is written in your own SQL (data.sql or a query) without quotes
1Check
In H2 a quoted name keeps its case exactly, and unquoted names become upper case. A script that quotes a name must then use the same case everywhere.
grep -rn "INSERT INTO" src/main/resources/2Fix
Quote the keyword in double quotes, in the same case the table was created with.
CREATE TABLE "USER" (id BIGINT PRIMARY KEY, name VARCHAR(100));
INSERT INTO "USER" (id, name) VALUES (1, 'Ada');3Verify
Run the script again and select the row.
SELECT * FROM "USER";Confirmed by h2database.com
Cause 3 of 3
Hibernate sends the keyword name without quotes
1Check
Spring Boot passes spring.jpa.properties.* to Hibernate unchanged, and its documentation shows hibernate.globally_quoted_identifiers=true for quoting generated names.
grep -rn "globally_quoted" src/main/resources/2Fix
Turn on global quoting, or rename the keyword names as shown in the first cause. With global quoting every name becomes case-sensitive, so hand-written SQL must match.
spring.jpa.properties.hibernate.globally_quoted_identifiers=true3Verify
Start the app and read the CREATE TABLE and INSERT statements in the log.
spring.jpa.show-sql=trueConfirmed by docs.spring.io
How do I stop it coming back?
- Avoid SQL words as table and column names: prefix tables (app_user) instead.
- Use the same database engine in tests as in production when you can, so keyword rules match.
- Keep hand-written SQL in one place and test it on startup.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
Related fixes
Databases
Flyway cannot handle the PostgreSQL URL after upgrading to Flyway 10 or newer
No database found to handle jdbc:postgresql://localhost:5432/app
Since Flyway 10, each database has its own module.
4 causes, each with a fixDatabases
H2 in PostgreSQL mode still behaves differently from PostgreSQL
Names or null ordering differ from PostgreSQL in test results
H2 compatibility modes are switched on in the JDBC URL and cover only a small part of the differences between databases.
4 causes, each with a fixDatabases
Hibernate fails on a CREATE or ALTER TABLE statement at startup
Error executing DDL "alter table events drop foreign key FKg0mkvgsqn8584qoql6a2rxheq" via JDBC Statement
Hibernate generated a CREATE, ALTER or DROP statement and MySQL refused it.
3 causes, each with a fixDatabases
Hibernate cannot initialize a lazy collection or proxy in Spring Boot
could not initialize proxy - no Session
A lazy collection or proxy is loaded on first access, and that needs an open Hibernate session.
3 causes, each with a fixDatabases
Hibernate cannot find the sequence hibernate_sequence on PostgreSQL
ERROR: relation "hibernate_sequence" does not exist
Hibernate 6 no longer uses one shared hibernate_sequence.
3 causes, each with a fixDatabases
PSQLException: column user0_.id does not exist in a Hibernate query
org.postgresql.util.PSQLException: ERROR: column user0_.id does not exist
Hibernate generated SQL that names a column PostgreSQL does not have under that exact name.
3 causes, each with a fix