How to add the string NULL from CSV to the database

Applies to

Liquibase Pro
Liquibase Open Source (Community)

Summary

In database workflows, it’s not uncommon to encounter scenarios where a column contains the string “NULL” rather than the actual NULL value. This distinction is important and can lead to issues if not handled carefully.

When performing the loadData command, Liquibase treats the value null as the actual value instead of treating it like a string. This how-to outlines how to use the loadData command to load a string with the value NULL.

By default, Liquibase will treat the value NULL as the NULL value.

For example, given the following CSV data, Liquibase will have NULL values in the Name column after using the loadData command.

ID	Name
1	NULL
2	John
3	NULL

Environment Conditions

Any version of Liquibase Pro or Open Source

The easiest workaround to take into account in this workflow is to perform a find and replace on the CSV file and modify the keyword NULL to another keyword.

Step 1: Change the NULL value in the CSV

Perform a find all and replace for the NULL value. For example, in the previous example, replace NULL with NULLABLE

ID	Name
1	NULLABLE
2	John
3	NULLABLE

Step 2: Perform the loadData command

Using the modified CSV file, perform the loadData command.

Step 3: Update the temporary keyword

Now perform the SQL to update the NULLABLE value back to the string value of null.

UPDATE PUBLIC.CONFIG SET NAME='NULL' WHERE NAME='NULLABLE';

Combine steps 2 and 3 into a changelog

Since Liquibase is already connecting to the database, steps 2 and 3 can be combined into a single changelog.

For example:

<changeSet id="1" author="liquibase_support">
    <loadData tableName="config.csv" file="config"></loadData>
</changeSet>

<changeSet id="2" author="liquibase_support">
    <sql>
        UPDATE PUBLIC.CONFIG SET NAME='NULL' WHERE NAME='NULLABLE';
    </sql>
</changeSet>

How to add the string NULL from CSV to the database

Applies to

Summary

Environment Conditions