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)
)