PostgreSQL & Liquibase - default id value from sequence lost

I generated a changelog.xml from an existent database using Liquibase.

  • Liquibase 4.9.1
  • PostgreSQL 13.2

The original database was built using the below commands. One company table that has its primary key id defaulted to the my_company_id_seq sequence nextval

The Liquibase generate-changelog command created two change sets (company table & sequence) but the configured company default id, that used to point to the my_company_id_seq sequence, was lost, now it uses GENERATED BY DEFAULT AS IDENTITY.

I understand GENERATED BY DEFAULT AS IDENTITY is the recommended approach from PostgreSQl 10+ on instead of SERIAL, but not for my use case where sometimes I need to generate the ID in advance (calling nextValue from the sequence) and later pass it at time to insert the data into company table, and other times I can just let the id to be generated by the configured DEFAULT nextval(‘my_company_id_seq’)

Do you know the rationale of it?

Or maybe there is no and it can be considered a bug?

Any insights and/or suggestions will be really appreciated

DDL from existent database:

CREATE SEQUENCE my_company_id_seq
INCREMENT BY 1
START WITH 1;

CREATE TABLE company (
	id INT NOT NULL DEFAULT nextval('my_company_id_seq') PRIMARY KEY,
	name VARCHAR NOT NULL
);

changelog.xml generated from existent database:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd">

    <changeSet author="liquibase (generated)" id="1656837328215-1">
        <createTable tableName="company">
            <column autoIncrement="true" name="id" type="INTEGER">
                <constraints nullable="false" primaryKey="true" primaryKeyName="company_pkey"/>
            </column>
            <column name="name" type="VARCHAR">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet author="liquibase (generated)" id="1656837328215-2">
        <createSequence cacheSize="1" cycle="false" dataType="bigint" incrementBy="1" maxValue="9223372036854775807" minValue="1" sequenceName="my_company_id_seq" startValue="1"/>
    </changeSet>

</databaseChangeLog>

Generated sequence & table from Liquibase changelog:

CREATE SEQUENCE IF NOT EXISTS app.my_company_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;    

CREATE TABLE IF NOT EXISTS app.company
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    name character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT company_pkey PRIMARY KEY (id)
)

Based on what you described, I’m thinking a bug is most likely unless PostgreSQL for some reason doesn’t want to allow you to specify the nextval. You can submit a bug for our team to test and QA using these instructions: How to Report Liquibase Issues and Bugs | Liquibase.org

1 Like