如何从Liquibase中的现有列中添加具有默认值的新列 [英] How to add new column with default value from existing column in Liquibase

问题描述

我正在使用Postgres DB，而对于迁移，我正在使用Liquibase. 我有一个包含以下各列的ORDERS表:

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

我需要添加一个新列，该列将容纳上次修改订单的用户-此列应不可为空，并且应具有默认值CREATOR. 对于新订单，我可以解决业务逻辑的默认值部分，但是是我已经有一个现有订单，因此在创建新列时需要设置默认值. 现在，我知道我可以在Liquibase中设置硬编码的默认值-但是有没有办法我可以基于该表的其他列(对于每个实体)添加默认值.

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

推荐答案

由于此处没有人回答，因此我发布的是我的处理方式:

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

这篇关于如何从Liquibase中的现有列中添加具有默认值的新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！