H2 database throwing an unexpected JdbcSQLException on CREATE SEQUENCE

Question

I am trying to write an in-memory database in order to test my DAO objects. I get the following stack trace :

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement " CREATE SEQUENCE CE0AOFQ AS[*] BIGINT START WITH 1 INCREMENT BY 1"; SQL statement:
 CREATE SEQUENCE CE0AOFQ AS BIGINT START WITH 1 INCREMENT BY 1 [42000-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.message.DbException.getSyntaxError(DbException.java:191)
    at org.h2.command.Parser.getSyntaxError(Parser.java:534)
    at org.h2.command.Parser.prepareCommand(Parser.java:261)
    at org.h2.engine.Session.prepareLocal(Session.java:578)
    at org.h2.engine.Session.prepareCommand(Session.java:519)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
    at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:132)
    at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:121)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.executeSqlScript(ResourceDatabasePopulator.java:169)

When trying to execute this command into a file called init.sql:

CREATE SEQUENCE CE0AOFQ AS BIGINT START WITH 1 INCREMENT BY 1;

As far as I checked (here, for example), this query seems correct.

Any idea why this is not working ?

Answer 1

You cannot specify the datatype of a sequence in h2. It is always BIGINT.

Use:

CREATE SEQUENCE CE0AOFQ START WITH 1 INCREMENT BY 1;