createIndex
The createIndex
Change Type creates an index on an existing column or a set of columns.
Uses
You can typically use the createIndex
Change Type when you want to add an index on one of the columns or a set of columns to increase the searchability of the records in your database.
If you set the clustered
attribute to true
, Liquibase will create a clustered index, which defines the order in which records are stored in a table. If you set the clustered
attribute to false
, Liquibase will create a non-clustered index, where the order of the rows will not match the order of the actual records.
When creating indexes, Liquibase uses the order of columns that you list in the createIndex
Change Type. For example:
<changeSet author="bob" id="1_createTable_department">
<createTable tableName="department">
<column name="col_1" type="integer"/>
<column name="col_2" type="integer"/>
<column name="col_3" type="integer"/>
</createTable>
</changeSet>
<changeSet author="bob" id="1_createIndex_index_one">
<createIndex indexName="index_one" tableName="department">
<column name="col_1"/>
<column name="col_2"/>
<column name="col_3"/>
</createIndex>
</changeSet>
<changeSet author="bob" id="2_createIndex_index_two">
<createIndex indexName="index_two" tableName="department">
<column name="col_3"/>
<column name="col_2"/>
<column name="col_1"/>
</createIndex>
</changeSet>
<changeSet author="bob" id="3_createIndex_index_three">
<createIndex indexName="index_three" tableName="department">
<column name="col_2"/>
<column name="col_3"/>
<column name="col_1"/>
</createIndex>
</changeSet>
These changesets will produce the following (the updateSQL
command example):
CREATE TABLE public.department (col_1 INT, col_2 INT, col_3 INT);
CREATE INDEX index_one ON public.department(col_1, col_2, col_3);
CREATE INDEX index_two ON public.department(col_3, col_2, col_1);
CREATE INDEX index_three ON public.department(col_2, col_3, col_1);
Running the createIndex
Change Type
To create an index on the existing column or set of columns, follow these steps:
Step 1: Add the createIndex
Change Type to your changeset with the needed attributes as it is shown in the examples.
Step 2: Deploy your changeset by running the update
command.
liquibase update
Available attributes
Name | Description | Required for | Supports |
---|---|---|---|
catalogName | The name of the catalog. | all | |
clustered | The attribute that creates a clustered index. | all | |
indexName | The name of the index to create. | firebird, hsqldb | all |
schemaName | The name of the schema. | all | |
tableName | The name of the table for which to add the index. | all | all |
tablespace | The tablespace in which you create the index. | all | |
unique | The unique values in the index. | all |
Nested properties
Name | Description | Required for | Supports | Multiple allowed |
---|---|---|---|---|
columns / column | The column(s) to add to the index. | all | all | yes |
Nested property attributes
Name | Description | Required for | Supports |
---|---|---|---|
name | The name of the column. It can contain the direction by appending ASC or DESC to the name. |
all | all |
computed | The attribute to set to true if the value in name isn't actually a column name but a function. Boolean. |
Since 3.3.0. | |
descending | The attribute which allows to specify that a column should be used in a descending order in the index. (i.e. ascending order) | Since 3.4. |

<changeSet author="liquibase-docs" id="createIndex-example">
<createIndex clustered= "true"
indexName="idx_address"
schemaName="public"
tableName="person"
tablespace="A String"
unique="true">
<column descending="true" name="address"/>
</createIndex>
</changeSet>

changeSet:
id: createIndex-example
author: liquibase-docs
changes:
- createIndex:
clustered: true
columns:
- column:
descending: true
name: address
indexName: idx_address
schemaName: public
tableName: person
tablespace: A String
unique: true

{
"databaseChangeLog":[
{
"changeSet":{
"id":"createIndex-example",
"author":"liquibase-docs",
"changes":[
{
"createIndex":{
"clustered":true,
"columns":[
{
"column":{
"descending":true,
"name":"address"
}
}
],
"indexName":"idx_address",
"schemaName":"public",
"tableName":"person",
"tablespace":"A String",
"unique":true
}
}
]
}
}
]
}

-- changeset DocUser:1:createIndexChangeType
CREATE UNIQUE INDEX idx_address ON department.person(address DESC);
Database support
Database | Notes | Auto rollback |
---|---|---|
DB2/LUW | Supported | Yes |
DB2/z | Supported | Yes |
Derby | Supported | Yes |
Firebird | 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 |
SQL Server | Supported | Yes |
SQLite | Supported | Yes |
Sybase | Supported | Yes |
Sybase Anywhere | Supported | Yes |