CODEWITHSUNDEEP
sqlpostgresqlhibernateliquibase
Rohr Facu
Rohr Facu

Reputation: 737

Set initial value in liquibase with postgresql database

I have a table and i need that it's ID start in 1000000. I have this code set for liquibase:

        <column name="id" type="bigint" autoIncrement="true" startWith="1000000">
            <constraints primaryKey="true" nullable="false"/>
        </column>

But it does not work in postgres, the generated sequence generator starts with 1. Does anyone has any idea on this? How can i enable a table to start from a value in liquibase that works with postgresql? Thanks in advance!

Upvotes: 2

Views: 4904

Answers (3)

Evgeny See
Evgeny See

Reputation: 1

Thx! Drop and create new - helps

  1. dropSequence
  2. createSequence

Upvotes: 0

Philippe Escure
Philippe Escure

Reputation: 149

Unfortunately startWith don't seems to work for postgres db. You can use (with yaml configuration) :

- changeSet:
  id: add-auto-increment-postgres
  author: me
  dbms: postgresql
  changes:
    - createSequence:
        sequenceName: my_sequence
        startValue: 1000
    - addDefaultValue:
        columnName: id
        defaultValueSequenceNext: my_sequence
        tableName: my_table

Upvotes: 2

Vao Tsun
Vao Tsun

Reputation: 51446

just alterSequence won't help:

    <alterSequence 
            minValue="1000000"
            sequenceName="t1_id_seq"/>

Unexpected error running Liquibase: ERROR: START value (1) cannot be less than M INVALUE (1000000) [Failed SQL: ALTER SEQUENCE public.t1_id_seq MINVALUE 1000000]

and according to docs you can't start sequence with alterSequence, so you have to createSequence with 

<createSequence 
        sequenceName="seq_id"
        startValue="1000000"/>

and then "assign" it as default value to your bigint column

  <sql dbms="postgresql" endDelimiter=";\n"  splitStatements="true"
        stripComments="true">ALTER TABLE t1 ALTER COLUMN id SET DEFAULT nextval('seq_id');
  </sql>

or initially use bigserial and sql to restart sequence:

<changeSet author="Vao" id="1">
    <createTable tableName="t2">
        <column name="id" type="bigserial">
            <constraints primaryKey="true" primaryKeyName="t2_pkey"/>
        </column>
    </createTable>
</changeSet>

<changeSet author="Vao" id="2">
  <sql dbms="postgresql" endDelimiter=";">ALTER SEQUENCE t2_id_seq restart with 1000;
  </sql>
</changeSet>

Upvotes: 4

Related Questions