postgresql 如何从 Liquibase 中的现有列添加具有默认值的新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35172172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add new column with default value from existing column in Liquibase
提问by Noam
I'm using Postgres DB and for migration I'm using Liquibase. I have an ORDERS table with the following columns:
我正在使用 Postgres DB，并且我正在使用 Liquibase 进行迁移。我有一个包含以下列的 ORDERS 表：
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, butthing 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).
我需要添加一个新列，该列将保存上次修改订单的用户 - 此列不应为空，并且应具有默认值，即 CREATOR。对于新订单，我可以解决业务逻辑的默认值部分，但问题是我已经有一个现有订单，我需要在创建新列时设置默认值。现在，我知道我可以在 Liquibase 中设置一个硬编码的默认值- 但是有没有一种方法可以根据该表的其他列（对于每个实体）添加默认值。
回答by Noam
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
我已经按照文档的建议在三个不同的变更集中完成了这项工作
回答by SteveDonie
You could use the defaultValueComputedattribute, which takes the name of a procedure or function. You would have to also create a changeset that creates the procedure.
您可以使用defaultValueComputed属性，它采用过程或函数的名称。您还必须创建一个用于创建过程的变更集。
That might look somethinglike this:
这可能看起来事情是这样的：
<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>
Alternatively, you could do this using the <sql>tag.
或者，您可以使用<sql>标签执行此操作。
