addDefaultValue

The addDefaultValue Change Type adds a default value to the database definition for the specified column in a table.

Uses

You can typically use the addDefaultValue Change Type when you want to set the default value for the column definition.

Running the addDefaultValue Change Type

To run this Change Type, follow these steps:

  1. Add the Change Type to your changeset, as shown in the examples on this page.
  2. Specify any required attributes. Use the table on this page to see which ones your database requires.
  3. Deploy your changeset by running the update command:
    4. liquibase update

Now, you should see the default value created.

Available attributes

Name Description Required for Supports
catalogName

Name of the catalog

 all
columnDataType

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)

For Snowflake, Liquibase also implements BINARY, TIME, and TIMESTAMP_NTZ.

For Databricks, Liquibase also implements ARRAY<INT> and ARRAY<STRING>, MAP, and STRUCT variable types.

Note: To specify a complex Databricks type like ARRAY<STRING> in an XML changelog, you must specify the escape sequences &lt; and &gt; rather than < and >. See createTable.

For more information, see Liquibase Data Type Handling.

 informix all
columnName Name of the column for which to add a default value. all all
defaultValue

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

 all
defaultValueBoolean

The boolean value that will be used if no value is provided.

Note: Only one of the defaultValue* attributes is allowed.

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

Note: Only one of the defaultValue* attributes is allowed.

 The attribute is not supported by MySQL 8.0 or HyperSQL. In Liquibase 4.25.0+, it is supported by MySQL 5.7.
defaultValueConstraintName

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

The attribute is supported only by MSSQL.
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.

Note: Only one of the defaultValue* attributes is allowed.

 all
defaultValueNumeric

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

Note: Only one of the defaultValue* attributes is allowed.

 all
defaultValueSequenceNext

Sets value for a specified column by using the value of the existing sequence. With every new input, the next value of the sequence will be taken.

Not supported by Apache Derby, Firebird, MySQL, MariaDB, SQLite, Ingress, and Sybase.
schemaName

Name of the schema

 all
tableName

Name of the table containing the column

 all all

Examples

--liquibase formatted sql

--changeset liquibase-docs:addDefaultValue-example
ALTER  TABLE  cat.file  ALTER  fileName  SET  DEFAULT  'Something Else';
databaseChangeLog:
-  changeSet:
    id:  addDefaultValue-example
    author:  liquibase-docs
    changes:
    -  addDefaultValue:
        catalogName:  cat
        columnDataType:  varchar(50)
        columnName:  fileName
        defaultValue:  Something Else
        defaultValueBoolean:  true
        defaultValueComputed:  now
        defaultValueConstraintName:  A String
        defaultValueDate:  2008-02-12T12:34:03
        defaultValueNumeric:  '439.2'
        defaultValueSequenceNext:  seq_name
        schemaName:  public
        tableName:  file
{
    "databaseChangeLog": [
        {
            "changeSet": {
                "id": "addDefaultValue-example",
                "author": "liquibase-docs",
                "changes": [
                    {
                        "addDefaultValue": {
                            "catalogName": "cat",
                            "columnDataType": "varchar(50)",
                            "columnName": "fileName",
                            "defaultValue": "Something Else",
                            "defaultValueBoolean": true,
                            "defaultValueComputed": "now",
                            "defaultValueConstraintName": "A String",
                            "defaultValueDate": "2008-02-12T12:34:03",
                            "defaultValueNumeric": "439.2",
                            "defaultValueSequenceNext": "seq_name",
                            "schemaName": "public",
                            "tableName": "file"
                        }
                    }
                ]
            }
        }
    ]
}
<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="addDefaultValue-example">
        <addDefaultValue  catalogName="cat"
            columnDataType="varchar(50)"
            columnName="fileName"
            defaultValue="Something Else"
            defaultValueBoolean="true"
            defaultValueComputed="now"
            defaultValueConstraintName="A String"
            defaultValueDate="2008-02-12T12:34:03"
            defaultValueNumeric="439.2"
            defaultValueSequenceNext="seq_name"
            schemaName="public"
            tableName="file"/>
    </changeSet>

</databaseChangeLog>

Troubleshooting

In the Liquibase Databricks extension 1.4.0, if you run addDefaultValue on a column in a table that already has columns with default values, you may receive this error:

DEFAULT values are not supported when adding new columns to previously existing Delta tables; please add the column without a default value first, then run a second ALTER TABLE ALTER COLUMN SET DEFAULT command to apply

If you use a YAML, JSON, or XML changelog, instead of using addDefaultValue to generate this SQL query:

ALTER TABLE myTable ADD COLUMN eventShortDescription STRING DEFAULT 'short desc';

You must use the sql or sqlFile Change Types to run two SQL queries:

ALTER TABLE myTable ADD COLUMN eventShortDescription STRING;
ALTER TABLE myTable ALTER COLUMN eventShortDescription SET DEFAULT 'short desc';

Alternatively, you can deploy your addDefaultValue change using a Formatted SQL changelog.

Database support

Database Notes Auto Rollback
DB2/LUW Supported Yes
DB2/z Supported Yes
Derby Supported Yes
Firebird Supported Yes
Google BigQuery Supported Yes
H2 Supported Yes
HyperSQL Supported Yes
INGRES Supported Yes
Informix Supported Yes
MariaDB Supported Yes
MySQL Supported Yes
Oracle Supported Yes
PostgreSQL Supported Yes
Snowflake Not Supported No
SQL Server Supported Yes
SQLite Supported Yes
Sybase Supported Yes
Sybase Anywhere Supported Yes