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: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 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

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

addDefaultValue examples

--liquibase formatted sql
--changeset liquibase-docs:addDefaultValue-example
ALTER TABLE cat.file
ALTER fileName
SET DEFAULT 'Something Else';