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

Question

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

The documentation on postgresql says for the `default clause` (on `create table` statement): *The `DEFAULT` clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed)...* And I don't think that liquibase has some functionality to add this in any way... — Jens, Feb 03 '16 at 09:49

score 39 · Accepted Answer · edited Aug 19 '23 at 18:09

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

score 9 · Answer 2 · answered Feb 03 '16 at 16:22

9

You could use the defaultValueComputed attribute, which takes the name of a procedure or function. You would have to also create a changeset that creates the procedure.

That might look something like 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.

answered Feb 03 '16 at 16:22

SteveDonie

8,700
3
43
43

Thanks @SteveDonie, but what should be the WHERE clause in the procedure or the SQL? – Noam Feb 04 '16 at 13:28
I don't know what your where clause should be - you didn't specify the condition in your question. – SteveDonie Feb 04 '16 at 15:30
What I want to accomplish here is mainly to create a new column with the default value - the default value (for existing entries in the table) should be the same value from another column. For example, if I already have an entry in the table like this: ID = 333 DATE = ... NAME = name CREATOR = Steve I want the new created column to have the value 'Steve' as a default value - for new created entries - I will handle the value in the business logic – Noam Feb 04 '16 at 16:08
1

Eventually I've managed it in the following manner (all in one changeset): 1) Create a new nullable column 2) Use the tag to update the new column 3) Add non-nullable constraint on the new column – Noam Feb 15 '16 at 15:03
Notice that `` combined with adding not-null constraint is bad practice. It will fail if table contains data. See topic: [Adding a non-nullable column to existing table fails](https://stackoverflow.com/a/8906534/5770135). – Raf Oct 16 '19 at 18:29

score 1 · Answer 3 · answered Feb 24 '22 at 10:49

1

you can try this:

<column name="columnName" type="VARCHAR(255 BYTE)" defaultValue="def_value" value="def_value">

Regards, L.

answered Feb 24 '22 at 10:49

user18298281

11
1

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

3 Answers3

Linked