I'm using Postgres database in production and H2 for tests. I want to create a new sequence for an existing table - so in Liquibase I wrote this:
<changeSet id="Add sequence for BOOKS" author="library">
<createSequence sequenceName="BOOKS_SEQ" incrementBy="500" startValue="1"/>
</changeSet>
My Entity looks like this:
@Entity
@Table(name = "BOOKS")
@SequenceGenerator(name = "BOOKS_SEQ", allocationSize = 500)
public class Book {
@Id
@GeneratedValue(generator = "BOOKS_SEQ", strategy = GenerationType.TABLE)
private Long id;
@ManyToOne
@JoinColumn(name = "AUTHOR_ID")
private Author author;
...
}
Now, since I already have entities in this table (with Ids from the last sequence I used), I need to set the current value of this sequence accordingly. For that matter I wrote:
<changeSet id="Alter sequence for BOOKS" author="library">
<sql dbms="postgresql">select setval('BOOKS_SEQ', (select nextval('OLD_SEQUENCE_UID')))</sql>
</changeSet>
<changeSet id="Add default value to BOOKS.ID" author="library">
<addDefaultValue tableName="BOOKS" columnName="ID" defaultValueSequenceNext="BOOKS_SEQ"/>
</changeSet>
In Postgres (production), it seems to work just fine - but in H2 I get an error message "The sequence named [BOOKS_SEQ] is setup incorrectly. Its increment does not match its pre-allocation size".
According to this - I need to set the start_value (or current value) to something greater than 500 - but I can't figure out how this can be done in H2.
So my question is: How can I set the current value of a sequence in H2?
Have you tried with this?
alter sequence <your_sequence_name> restart with <next_value>
For example:
alter sequence BOOKS_SEQ restart with 500
When I run the above command, I have BOOKS_SEQ set its current value to 499 (so the next value will be 500).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With