Is there any wrong syntax in H2 SQL statement?

Question

Trying to execute prepared SQL statement with H2 DB in memory. Receive syntax error:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "INSERT INTO task7schema.courses (course_name) VALUES (?) returning[*] course_id;"; SQL statement:
INSERT INTO task7schema.courses (course_name) VALUES (?) returning course_id; [42000-200]

Can not figure out what's wrong with syntax. Any help appreciated.

Does this answer your question: [Return primary key value generated by default in H2 database upon INSERT of new row, for UUID type column](https://stackoverflow.com/questions/53907038/return-primary-key-value-generated-by-default-in-h2-database-upon-insert-of-new)? The example is for returning a UUID - but I expect it can be adapted for your situation, if you are not using UUIDs. — andrewJames, Sep 26 '21 at 19:01
Thank for the guidance. I'll try to dig in that direction. My initial schema script contains 'SERIAL' keyword, it seems to be postgres specific. — Jantao, Sep 27 '21 at 07:35

score 0 · Accepted Answer · answered Sep 28 '21 at 09:53

H2 supports only standard-compliant syntax:

SELECT course_id FROM FINAL TABLE
    (INSERT INTO task7schema.courses (course_name) VALUES (?));

Syntax from your question is PostgreSQL-specific. Because PostgreSQL doesn't support standard data change delta tables, you need to use different SQL for H2 and PostgreSQL.

JDBC has own more or less portable way to request generated keys. You can explicitly request them from the statement or prepared statement (with additional Statement.RETURN_GENERATED_KEYS parameter or by specifying names or ordinal indexes of columns) and read them with getGeneratedKeys().

Is there any wrong syntax in H2 SQL statement?

1 Answers1