Search code examples
postgresqlliquibaseauto-increment

Liquibase: added autoIncrement column into a table with data already present

Hi guys I have some doubt about Liquibase

I created a table (PostgreSQL) with a classical changeset

<changeSet id="create_table">
    <createTable tableName="table" schemaName="schema">
        <column name="name" type="varchar">
            <constraints nullable="false"/>
        </column>
        <column name="surname" type="varchar">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>

I've already added some data to this table. I want to add a new column 'id' and make it as a primary key as an autoincrement column. How can I do that?

Because if I try to add a changeset like this:

<changeSet id="added_pk">
    <addColumn tableName="table"
               schemaName="schema">
        <column name="id" type="bigint">
            <constraints unique="true" nullable="false" uniqueConstraintName="PK_TABLE_ID"/>
        </column>
    </addColumn>
    <addAutoIncrement columnDataType="bigint"
                      columnName="id"
                      tableName="table"
                      schemaName="schema"
                      incrementBy="1" startWith="1"/>
</changeSet>

I obtain error when I run the script: "column "id" of relation "table" contains null values". It depends of the already existing values. How could I manage this situation? (I prefer to avoid to truncate all the data before add the new column)

Thanks a lot! Regards

Solution

  • Rename the current table in the DB. Delete the liquibase changelogs. Rerun the liquibase file including the new id field (you can combine the two changeSets, this way you also get to decide the position of the id field). After the new table has been created you can transfer the data from the old table into the new table. This way the data from the old table will automatically receive ids. Delete the old table at the end.