'Add Computed Column with Liquibase

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.

sql-serverliquibase


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

I had to fixup helpful @Philipp's answer with modifySql as Liquibase 3.x kept generating empty type placeholder [] for SQL Server like in

ALTER TABLE [dbo].[TableName] ADD ComputedColumn as (case when ((([fieldFlagOne]+(0))+([fieldFlagTwo]+(0)))+([fieldFlagThree]+(0)))=(3) then (1) else (0) end) []

that led to [S1000][1038] An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. error:

- 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
- modifySql:
    replace:
      replace: "[]"
      with: ""

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 SeanN
Solution 3 Vadzim

Related Questions