Search code examples
javapostgresqlmigrationliquibase

How to add new column with default value from existing column in Liquibase


I'm using Postgres DB and for migration I'm using Liquibase. I have an ORDERS table with the following columns:

ID | DATE | NAME | CREATOR | ...

I need to add a new column which will hold the user who has last modified the order - this column should be not-nullable and should have default value which is the CREATOR. For new orders I can solve the default value part of the business logic, but thing is I already have an existing orders and I need to set the default value when I create the new column. Now, I know I can set a hard-coded default value in Liquibase - but is there a way I could add the default value based on some other column of that table (for each entity).


Solution

  • Since no one answered here I'm posting the way I handled it:

    <changeSet id="Add MODIFY_USER_ID to ORDERS" author="Noam">
        <addColumn tableName="ORDERS">
            <column name="MODIFY_USER_ID" type="BIGINT">
                <constraints foreignKeyName="ORDERS_MODIFY_FK" referencedTableName="USERS" referencedColumnNames="ID" />
            </column>
        </addColumn>
    </changeSet>
    
    <changeSet id="update the new MODIFY_USER_ID column to get the CREATOR" author="Noam">
        <sql>update ORDERS set MODIFY_USER_ID = CREATOR</sql>
    </changeSet>
    
    <changeSet id="Add not nullable constraint on MODIFY_USER_ID column" author="Noam">
        <addNotNullConstraint tableName="ORDERS" columnName="MODIFY_USER_ID" columnDataType="BIGINT" />
    </changeSet>
    

    I've done this in three different change-sets as the documentation recommends