column

The column tag specifies information about columns you create or modify with Change Types like addColumn, createTable, createIndex, dropColumn, insert, loadData, loadUpdateData, and update. You can specify one or multiple columns in a single changeset.

Uses

Use the column tag to define the behavior of your table columns. For example, with a Change Type like addColumn, setting the defaultValue attribute on a column specifies its default value. Setting the value attribute sets all rows existing to the specified value (such a string) without modifying the column default. Setting the valueComputed attribute as a function name or name of a computed column sets all rows to the value returned (computed) by that function.

You can use the column tag with createIndex to create an index on a particular table column (<column name="address"/>). You can also create an index on a computed version of that column (<column name="lower(address)" computed="true"/>).

Some Change Type that interact with table columns, like addPrimaryKey and modifyDataType, do not use the <column> tag. Instead, they use their own attributes like columnNames and columnName respectively to refer to existing columns specified with the <column> tag.

Available attributes

Note: You can use the <column> tag with multiple Change Types, but not every attribute is valid for every Change Type. For example, if you use column with createTable, both name and value are valid attributes. However, if you use column with createIndex, name is valid but value is not.

Attribute Description
name

Name of the column

type

Data type of the column.

To help make scripts database-independent, Liquibase automatically converts the following generic data types to the correct database implementation: BIGINT, BLOB, BOOLEAN, CHAR, CLOB, CURRENCY, DATE, DATETIME, DECIMAL, DOUBLE, FLOAT, INT, MEDIUMINT, NCHAR, NUMBER, NVARCHAR, SMALLINT, TIME, TIMESTAMP, TINYINT, UUID, VARCHAR, XML.

Also, specifying a java.sql.Types.* type is converted to the correct type as well. For example:

  • java.sql.Types.TIMESTAMP
  • java.sql.Types.VARCHAR(255)
value Sets all fields in the column to the specified value. The value will be surrounded by quote marks. Nested quote marks will be escaped.
afterColumn

Controls the placement of a new column added with the addColumn Change Type. See also: removeChangeSetProperty.

Note: Only one of beforeColumn, afterColumn, or position is allowed.

autoIncrement An integer which specifies how much to increment the column by each time a new record is inserted into the table. Useful to generate unique primary keys for each record. Ignored on databases that do not support auto-increment or identity functionality.
beforeColumn

Controls the placement of a new column added with the addColumn Change Type. See also: removeChangeSetProperty.

Note: Only one of beforeColumn, afterColumn, or position is allowed.

Not supported in MySQL.

computed

Boolean specifying whether the value in name is computed (a function) rather than a column name (a string). Default: false.

With some Change Types, like addColumn, computed tells Liquibase whether the type can be added without specifying a type.

defaultValue

The default value for fields in the column. Either this property or one of the other defaultValue* properties are required.

defaultValueBoolean

The default value for a column of a boolean type.

defaultValueComputed

The default value that is returned from a function or procedure call of the same type as the column. Contains the function or column name to call. Differs from defaultValue by returning the value of the function or column you specify instead of the name of the function/column as a string. Can also perform operations on the returned value.

defaultValueConstraintName

Sets a unique name for default constraint used for a specific column. It works only along with any of the defaultValue* attributes listed.

defaultValueDate

The default date and time value for column. The value is specified in one of the following forms: YYYY-MM-DD, hh:mm:ss, or YYYY-MM-DDThh:mm:ss.

defaultValueNumeric

The default value for a column of a numeric type. For example: integer, bigint, bigdecimal, and others.

descending Defines whether a column is in descending order in the index if it is used in a createIndex command. Default value: false (ascending).
encoding The name of the encoding (specified in java.nio.Charset javadoc, e.g. "UTF-8") of the CLOB file (specified in valueClobFile) contents.

Note: This attribute is used only when valueClobFile attribute is specified. Otherwise, it is ignored.

incrementBy Integer amount to increment by at each call. Ignored on databases that do not support auto-increment or identity functionality.
position

Controls the placement of a new column added with the addColumn Change Type. See also: removeChangeSetProperty.

Note: Only one of beforeColumn, afterColumn, or position is allowed.

remarks

A short descriptive comment

startWith Initial integer value of the increment. Ignored on databases that do not support auto-increment or identity functionality.
valueBlobFile

The path to a file whose contents will be written as a BLOB (binary large object).

The path to the changelog file location must be either absolute or relative. An example of an absolute path is: /usr/local/somefile.dat on Unix or c:\Directory\somefile.dat on Windows.

Note: Refer to java.io.File javadoc for the details of what to consider relative or absolute path.

valueBoolean The boolean value to set fields in the column to. The actual value string inserted depends on the database implementation.
valueClobFile

The path to a file whose contents will be written as a CLOB (character large object).

The path to the changelog file location must be either absolute or relative. An example of an absolute path is: /usr/local/somefile.dat on Unix or c:\Directory\somefile.dat on Windows.

Note: Refer to java.io.File javadoc for the details of what to consider relative or absolute path.

valueComputed The value that is returned from a function or procedure call. Contains the function or column name to call. Differs from value by returning the value of the function or column you specify instead of the name of the function/column as a string. Can also perform operations on the returned value.
valueDate The date and time value to set the column to. Accepts the following formats: YYYY-MM-DD, hh:mm:ss, or YYYY-MM-DDThh:mm:ss.
valueNumeric The numeric value to set fields in the column to. The value will not be escaped and will not be nested in quote marks.

Available sub-tags

Tag Description
constraints Constraint definitions

Constraints sub-tag

The <constraints> tag contains information about constraints on the column.

Available attributes

Attribute Description
checkConstraint Defines whether to validate the defined check constraint. Default: database-dependent
deleteCascade Sets DELETE CASCADE.
deferrable Defines whether constraints are deferrable. Default: database-dependent
foreignKeyName The foreign key name.
initiallyDeferred Defines whether constraints are initially deferred. Default: database-dependent
notNullConstraintName The NOT NULL CONSTRAINT name.
nullable Defines whether the column is nullable. Default: database-dependent
primaryKey Defines whether the column is a primary key. Default: database-dependent
primaryKeyName The name of the primary key.
primaryKeyTablespace The tablespace to use for the defined primary key.
unique Defines whether a unique clause should be applied. Default: database-dependent
uniqueConstraintName Name of the constraint (unique)
references The foreign key definition. (This value is used as-is in the generated SQL so ensure all required quoting or other required syntax is being used. If you do not need to specify the exact SQL, use referencedTableName and referencedColumnNames instead)
referencedColumnNames

Name of the column

referencedTableCatalogName

Name of the catalog

referencedTableName

Name of the table

referencedTableSchemaName

Name of the schema

validateForeignKey Defines whether to validate the defined foreign key constraint. Default: database-dependent
validateNullable Defines whether to validate the defined not null constraint. Default: database-dependent
validatePrimaryKey Defines whether to validate the defined primary key constraint. Default: database-dependent
validateUnique Defines whether to validate the defined unique constraint. Default: database-dependent

Note: YAML and JSON changelogs using the column tag must nest it within a columns tag.

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet author="liquibase-docs" id="addColumn-example">
        <addColumn catalogName="cat"
            schemaName= "public"
            tableName="person">
            <column name="address"
                type="varchar(255)"/>
            <column name="name"
                type="varchar(50)">
                <constraints nullable="false" />
            </column>
        </addColumn>
    </changeSet>

</databaseChangeLog>
databaseChangeLog:
- changeSet:
    id: addColumn-example
    author: liquibase-docs
    changes:
      - addColumn:
          tableName: person
          columns:
          - column:
              name: middlename
              type: varchar(50)
{
    "databaseChangeLog": [
        {
            "changeSet": {
                "id": "addColumn-example",
                "author": "liquibase-docs",
                "changes": [
                    {
                        "addColumn": {
                            "catalogName": "cat",
                            "columns": [
                                {
                                    "column": {
                                        "name": "address",
                                        "type": "varchar(255)"
                                    }
                                },
                                {
                                    "column": {
                                        "constraints": {
                                            "nullable": false
                                        },
                                        "name": "name",
                                        "type": "varchar(50)"
                                    }
                                }
                            ],
                            "schemaName": "public",
                            "tableName": "person"
                        }
                    }
                ]
            }
        }
    ]
}
--liquibase formatted sql

--changeset liquibase-docs:addColumn-example
ALTER TABLE cat.person ADD address VARCHAR(255) NULL,
ADD name VARCHAR(50) NOT NULL;

Related links