Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

H2 fails an INSERT or CREATE TABLE with: expected identifier

Errororg.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement

Short 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.

  • Applies to H2 2.3, Spring Boot 3.4, Hibernate ORM 6.6
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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=true
  2. 2Match what you see, then jump to its fix

    1. 1The marker [*] sits right after USER, VALUE, KEY, ORDER or a similar wordA table or column is named with an H2 keywordFix
    2. 2The 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. 3The statements come from Hibernate and you do not want to rename the entityHibernate sends the keyword name without quotesFix

What does the error look like?

  • org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement
  • expected "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/java

2Fix

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:run

Confirmed by h2database.com

Did this fix it?

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

Did this fix it?

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=true

3Verify

Start the app and read the CREATE TABLE and INSERT statements in the log.

spring.jpa.show-sql=true

Confirmed by docs.spring.io

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.