自动增加Liquibase [英] Autoincrement in liquibase

问题描述

如何使用liquibase在PostgreSQL的列上使用'startWith'设置autoincrement属性?

How do i set the autoincrement property using 'startWith' on a column in PostgreSQL using liquibase??

由于某种原因，它总是从1开始.我尝试使用自定义序列，但这也无济于事.

For some reason it always starts from 1. I tried using a custom sequence but that didn't help either.

<column autoIncrement="true" startWith="100" name="id" type="bigint">

这是我当前无法使用的列定义.

That's my current column definition which does not work.

我想使用liquibase从csv导入数据.我尝试了以下方法:

I want to import data from csv using liquibase. I tried the following:

<changeSet author="author" id="createSequence">
    <createSequence
                    incrementBy="1"
                    sequenceName="mytable_id_seq"
                    startValue="1000"/>
    </changeSet>
</changeSet>


<changeSet author="author" id="1-mytable">
    <createTable tableName="mytable">
        <column name="id" type="BIGSERIAL" defaultValueComputed="nextval('mytable_id_seq')">
              <constraints primaryKey="true" primaryKeyName="mytable_pkey"/>
        </column>
    </createTable>

    <loadData encoding="UTF-8"
              file="liquibase/data/mytable.csv"
              separator=","
              tableName="mytable">
    </loadData>
</changeSet>

如果我尝试此操作，则会收到以下错误此会话中尚未定义序列"table_id_seq"的序列"，并且我认为它使用了公共模式中的序列，而不是我设置为liquibase的序列.

If i try this I receive the following error 'currval of sequence "table_id_seq" is not yet defined in this session' and I think that it uses the sequence from the public schema instead of what i have set to liquibase.

1. 我尝试的另一件事是手动更新它:

ALTER SEQUENCE mytable_id_seq以100重新启动；

ALTER SEQUENCE mytable_id_seq restart with 100;

在这种情况下，使用的序列是公共模式中的序列，但是我想使用设置为liquibase的模式

In this case the sequence used was the one from the public schema, but i want to use the schema set to liquibase

推荐答案

如果要设置自己的增量和序列，请使用bigint而不是使用bigserial，后者是postgres专用的自动增量bigint."数据类型 smallserial、serial 和 bigserial 不是真正的类型，而仅仅是创建唯一标识符列的符号方便(类似于某些其他数据库支持的 AUTO_INCREMENT 属性).在当前的实现中，指定:"

Instead of using bigserial which is an autoincrementing bigint specific to postgres use bigint if you are going to be setting up your own increment and sequence. "The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:"

CREATE TABLE tablename (
    colname SERIAL
);

与

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

从这里

https://www.postgresql.org/docs/12/datatype-numeric.html

这篇关于自动增加Liquibase的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！