diff-column-order parameter

The diff-column-order global parameter is a Boolean that determines whether the diff command and diffChangeLog command compare the column order of a table between two databases. The default value is false.

Relational databases

Relational databases organize data as relations (tables) that contain rows and columns. This data is ordered in the computer’s physical memory, but you’re meant to access it by name (column name, column id…), not ordinal position (column 1, column 2…).

It’s possible to add or select columns by ordinal position if you use the statement SELECT * FROM my_table and run getValue(1) on the result set rather than getValue(name). However, this is generally considered bad practice.

This means the diff-column-order parameter usually isn’t relevant when you compare two databases.

Uses

The diff command compares two databases and reports any differences to STDOUT. The diffChangeLog command compares two databases and generates a changelog containing changesets that you can deploy to resolve any differences.

If you’re comparing two databases with diff or diffChangeLog, it’s best practice to leave diff-column-order at the default value of false. If you set it to true and run diffChangeLog, Liquibase cannot create changesets to resolve differences in column order.

However, if you use external comparison or reporting tools which cannot easily handle differences in column order data, you can set diff-column-order to true. This way, Liquibase handles order differences before passing any data to these external tools.

Setting the diff-column-order parameter

You can set diff-column-order in four ways:

  • As a JVM system property
  • In your liquibase.properties file
  • As a global parameter in the CLI
  • As an environment variable (Liquibase Pro)

Java system property

You can set diff-column-order as a Java system property by using the JAVA_OPTS Environment Variable in your command line. The syntax on Mac/Linux is as follows:

JAVA_OPTS=-Dliquibase.diffColumnOrder=<true|false> && liquibase diff

The syntax on Windows requires the set command:

set JAVA_OPTS=-Dliquibase.diffColumnOrder=<true|false> && liquibase diff

liquibase.properties parameter

In Liquibase 4.1+, you can set diff-column-order by adding the following to your liquibase.properties file:

liquibase.diffColumnOrder: <true|false>

CLI global parameter

You can use diff-column-order as a global parameter in your command line with a single Liquibase command, such as diff:

liquibase --diff-column-order=<true|false> diff

Environment variable (Liquibase Pro)

If you use Liquibase Pro, you can set diff-column-order as an environment variable. The syntax on Mac/Linux is as follows:

LIQUIBASE_DIFF_COLUMN_ORDER=<true|false>

The syntax on Windows requires the set command:

set LIQUIBASE_DIFF_COLUMN_ORDER=<true|false>

Note: The commands shown above only apply to the current shell. If you need to pass an environment variable to a child process without affecting the parent process, you can use the export command on Mac/Linux or the setx command on Windows.

Output

If you set diff-column-order to true and run the diff command on two databases containing tables with opposite column orders, the output includes:

Changed Column(s):
     PUBLIC.TEST_TABLE.TEST_NAME
          order changed from '2' to '1'
     PUBLIC.TEST_TABLE.TEST_ID
          order changed from '1' to '2'

Related links