更改H2中的序列 [英] Alter sequence in H2

问题描述

我在生产中使用Postgres数据库，在H2中使用它进行测试。
我想为现有表创建一个新序列-所以我在Liquibase中这样写：

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;
...

}

现在，因为我已经在此表中有实体（带有我使用的最后一个序列的ID），我需要相应地设置此序列的当前值。
为此，我写道：

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>

在Postgres（生产环境）中，它似乎工作正常-但在H2中，我收到一条错误消息名为[BOOKS_SEQ]的序列设置不正确。其增量与预先分配的大小不匹配。

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".

根据此-我需要将start_value（或当前值）设置为大于500的值-但我不知道该怎么做可以在H2中完成。

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.

所以我的问题是：如何在H2中设置序列的当前值？

So my question is: How can I set the current value of a sequence in H2?

推荐答案

您是否尝试过

alter sequence <your_sequence_name> restart with <next_value>

例如：

alter sequence BOOKS_SEQ restart with 500

重新启动当我运行上述命令时， BOOKS_SEQ将其当前值设置为499（因此下一个值将是500）。

When I run the above command, I have BOOKS_SEQ set its current value to 499 (so the next value will be 500).

这篇关于更改H2中的序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！