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

Question

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

ID | DATE | NAME | CREATOR | ...

I need to add a new column in which the user who last modified the order will be stored - this column must be invalid and must have a default value that is CREATOR. For new orders, I can solve part of the default cost of the business logic , but . I already have existing orders, and I need to set a default when creating a new column. Now I know that I can set a hard-coded default value in Liquibase - but is there a way to add a default value based on some other column of this table (for each object).

Answer 1

, , :

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

,

Answer 2

defaultValueComputed, . , .

:

<changeSet author="steve" id="createProcedureForDefaultValue">
    <createProcedure procedureName="myCoolProc">
    CREATE OR REPLACE PROCEDURE myCoolProc IS
    BEGIN
       -- actual logic here
    END;
    </createProcedure>
</changeSet>

<changeSet author="steve" id="addDefaultValueColumn">
    <addColumn tableName="ORDERS">
        <column name="LAST_MODIFIED_BY" type="VARCHAR" defaultValueComputed="myCoolProc">
            <constraints nullable="false"/>
        </column>
    </addColumn>
</changeSet>

, <sql>.