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 update-sql 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 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

Available attributes

Nested tags

Nested property attributes

Examples

--liquibase formatted sql

--changeset liquibase-docs:createIndex-example
CREATE  UNIQUE  INDEX  idx_address  ON  department.person(address  DESC);

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

{
    "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
                        }
                    }
                ]
            }
        }
    ]
}

<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="createIndex-example">
        <createIndex clustered= "true"
              indexName="idx_address"
              schemaName="public"
              tableName="person"
              tablespace="A String"
              unique="true">
            <column descending="true" name="address"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>

Database support