How to force H2 database to add "RESTART WITH" into exported file

Question

When I export my H2 database instance by

SCRIPT TO

Path dbDumpPath = backupDirPath.resolve(SQL_DUMP_FILE);
String sql = String.format("SCRIPT TO '%s'", dbDumpPath.toAbsolutePath());
jdbcTemplate.execute(sql);

command, it does not add "RESTART WITH <>" in some of the sequence the statements

CREATE SEQUENCE "PUBLIC"."TABLE4_SEQ" START WITH 1 RESTART WITH 101 INCREMENT BY 50;    
CREATE SEQUENCE "PUBLIC"."TABLE5_SEQ" START WITH 1 INCREMENT BY 50;      

The entity class definitions are the same, both are using unique sequence generators

@Entity
@Getter
@Setter
@NoArgsConstructor
@SequenceGenerator(name = "table4_seq", sequenceName = "table4_seq", allocationSize = 1)
public class Table4 implements Serializable {
 @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "table4_seq")
  private Integer id;
.
.
.

@Entity
@Getter
@Setter
@NoArgsConstructor
@SequenceGenerator(name = "table5_seq", sequenceName = "table5_seq", allocationSize = 1)
public class Table5 implements Serializable {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "table5_seq")
  private Integer id;
.
.
.

How can I fix this weird problem? Is there any way to force H2 to add RESTART WITH part ?

When I run the query from console

ALTER SEQUENCE TABLE4_SEQ RESTART WITH 55;

and then backup, it is exported. But for some reason, in older backup files that are taken of course without running this ALTER .. command, there are missing RESTART WITH statements.

Answer 1

H2 export sequences with RESTART WITH clause if and only if value in this clause is different from value in START WITH clause. It means that some of your sequences weren't yet used or were restarted after use with initial start value. You can't change this behavior.

If current base values of some sequences are different from your expectations, you can only change them by restarting these sequences with values you want or by fetching their generated values.

ALTER SEQUENCE TABLE5_SEQ RESTART WITH (SELECT MAX(ID) + 1 FROM TABLE5);

Usually it is better to use identity columns instead of sequences.