Add Computed Column with Liquibase

1
votes

How can I add a computed column in liquibase? When I run the following SQL in a sql tag I get the computed value I'm expecting:

ALTER TABLE TableName
ADD ComputedColumn AS (CASE WHEN DateColumn1 IS NULL OR DateColumn2 IS NULL
THEN 0
ELSE DATEDIFF(DAY, DateColumn1, DateColumn2) END)

How do I do this without using an sql tag? I am able to get a column created with the following yaml:

- addColumn: tableName: TableName columns: - column: name: ComputedColumn type: tinyint constraints: nullable: true defaultValueComputed: 0 valueComputed: DATEDIFF(DAY, DateColumn1, DateColumn2) END

However the column type is not Computed like when I run the raw SQL.

2
sql-serverliquibase

2 Answers

0
votes

Try putting the computed column expression for the type, like this - note that the expression is also a YAML folded style, which I have found prevents Liquibase from mangling the expression:

- addColumn:
  tableName: TableName
  columns:
    - column:
        name: ComputedColumn
        type: > 
            tinyint AS (DATEDIFF(DAY, DateTimestamp, DateTimestamp))
        constraints:
          nullable: true
0
votes

Had no luck using the suggested solution. But here is my working one:

- addColumn:
  tableName: TableName
  columns:
    - column:
        name: ComputedColumn as (case when ((([fieldFlagOne]+(0))+([fieldFlagTwo]+(0)))+([fieldFlagThree]+(0)))=(3) then (1) else (0) end)
        computed: true
        type: ""
        constraints:
          nullable: true