How do you insert a string with the value of “NULL” using loadData?

Applies to: 

• Liquibase Pro
• Liquibase Open Source (Community)

Summary:

As shown at Liquibase - loadData:

A value of NULL in a cell will be converted to a database NULL rather than the string NULL

There are several workarounds to insert the string "null" as database column values from CSV files.

Environment Conditions:

• Any version of Liquibase

How to insert a string with the value of "NULL" using loadData:

Step 1: Edit the CSV file

Load in the CSV values column any other value different from NULL or null, i.e., NULLABLE.

Step 2: Create the changeSet to load data from the CSV file

The changeSet should be something like the following:

<changeSet author="Liquibase" id="1"> 
    <loadData file="YOUR_FILE.csv" tableName="YOUR_TABLE" /> 
<changeSet>

Step 3: Create the changeSet to update values from step 2

Add another changeSet to the changeLog file, which will run a SQL script to perform the NULLABLE value substitution to the null value:

<changeSet author="Liquibase" id="2"> 
    <sqlFile path="update_to_null.sql" relativeToChangelogFile="true" /> 
<changeSet>

UPDATE YOUR_SCHEMA.YOUR_TABLE SET YOUR_FIELD='null' WHERE YOUR_FIELD='NULLABLE'

Step 4: Update the database

Run the liquibase update command to deploy both changeSets.

Related Article(s):

• Liquibase loadData