I’m working with an H2 in-memory database and getting a syntax error when trying to run an INSERT statement with a RETURNING clause. Here’s the error message I’m seeing:
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO project_db.students (student_name) VALUES (?) returning[*] student_id;"; SQL statement:
INSERT INTO project_db.students (student_name) VALUES (?) returning student_id; [42000-200]
The query looks correct to me but H2 keeps throwing this syntax exception. I’m using a prepared statement to insert a new record and want to get back the generated ID. Has anyone encountered this issue before? What could be causing this syntax problem?
Yeah, ran into this switching from Postgres too. H2 doesn’t support RETURNING unfortunately. Use preparedStatement.executeUpdate() then call getGeneratedKeys() right after on the same statement - works great for getting auto-generated IDs back.
H2 doesn’t support the RETURNING clause as it is specific to PostgreSQL, and H2 has not implemented this feature. Instead, you should utilize Statement.RETURN_GENERATED_KEYS when preparing your statement. After executing the insert, invoke getGeneratedKeys() to retrieve the auto-generated ID. This method is compatible across various database systems, including H2. I faced a similar issue while migrating PostgreSQL code to H2 for testing, and switching to JDBC generated keys resolved it immediately.
wait, are you migrating from postgres? that error looks familiar - i hit the same thing during my migration. what h2 version are you using? newer versions might support this syntax, or there could be a compatibility mode to enable.