Insert new data using Liquibase, postgreSQL and sequence

Question

How do I make an insert for PostgreSQL using Liquibase if my ids are sequential. I try with the following:

<changeSet author="rparente" id="service-1.1-2019-01-09-01">
        <insert tableName="tenant">
            <column name="id"defaultValueSequenceNext="hibernate_sequence"/>
            <column name="description" value="Prueba"/>
            <column name="name" value="antel"/>
            <column name="service_id" value="antel"/>
        </insert>
    </changeSet>

and I try with

<changeSet author="rparente" id="service-1.1-2019-01-09-01">
        <insert tableName="tenant">
            <column name="id"  value="nextval('hibernate_sequence')"/>
            <column name="description" value="Prueba"/>
            <column name="name" value="antel"/>
            <column name="service_id" value="antel"/>
        </insert>
    </changeSet>

The error is:

ERROR: null value in column "id" violates not-null constraint

Ane

is defined without default value – Ane

Answer 1

I found the solution to insert data in Postgres with sequence (no default) ids

<changeSet author="author_name" id="service-1.1-2019-01-09-01"> 
    <insert tableName="tenant"> 
        <column name="id" valueSequenceNext="name_sequence"/> 
        <column name="description" value="TEST"/> 
        <column name="name" value="test"/> 
        <column name="service_id" value="testl"/> 
        <column name="status" value="ACTIVE"/> 
    </insert> 
</changeSet>

Ane

Worked for us. Thanks :-) – Margaux

how do you get the inserted id value ? – Adamo

@Adamo It is automatic, similar to an autogenerated field, but it is a sequence, usually a name is specified (recommended), for example, user_sequence or product_sequence, if the database is created with Hibernate the default sequence is called 'hibernate_sequence' – Ane

Answer 2

Check out the ColumnConfig doc. You should be able to set a valueComputed property and in it call the Postgres function:

<column name="id"  valueComputed="nextval('hibernate_sequence')"/>

Fourfold

I found the solution to insert <changeSet author="author_name" id="service-1.1-2019-01-09-01"> <insert tableName="tenant"> <column name="id" valueSequenceNext="name_sequence"/> <column name="description" value="TEST"/> <column name="name" value="test"/> <column name="service_id" value="testl"/> <column name="status" value="ACTIVE"/> </insert> </changeSet> thnk! – Ane

Answer 3

As for me, I have to create a sequence first and then use it.

<changeSet>
    <createSequence sequenceName="runtime_name_seq" dataType="bigint" incrementBy="1" maxValue="10000" minValue="1" startValue="1"/>
</changeSet>

<changeSet>
     <createTable tableName="runtime_name">
        <column name="id" type="INTEGER" defaultValueComputed="nextval('runtime_name_seq')">
            <constraints nullable="false" primaryKey="true" primaryKeyName="pk_runtime_name"/>
        </column>
    </createTable>
</changeSet>

This will create a SQL by Liquibase (v3.8.1 I am using)

CREATE TABLE public.runtime_name 
(
    index INTEGER DEFAULT nextval('runtime_name_seq') NOT NULL
)

Livery

In Liquibase 3.8. you can use autoIncrement="true" and Liquibase will generate an identity column – Consol

Yes, you can use autoIncrement which will create sequence for you. In this case, we are using our own sequence – Livery

Answer 4

Using liquibase the way to go now is defaultValueSequenceNext

<createSequence
  schemaName="YOUR_SCHEMA"
  sequenceName="YOUR_SEQUENCE"
  dataType="bigint"
  startValue="1"
  incrementBy="1"
  cycle="false"/>
<createTable schemaName="YOUR_SCHEMA" tableName="YOUR_TABLE">
<column name="ID" type="bigint" defaultValueSequenceNext="YOUR_SEQUENCE">
  <constraints nullable="false" primaryKey="true" primaryKeyName="PK_YOUR_TABLE"/>
</column>
</createTable>

And for the insert it's as https://stackoverflow.com/users/3499894/renzo-parente said above using <column name="id" valueSequenceNext="YOUR_SEQUENCE"/>

Tearle