Not able to insert NULL values from CSV file with liquibase and h2 database latest version

Question

I have an external csv file with the data to be inserted. One of my column is of datatype timestamp (but its a nullable column). Having the data value NULL/null is ending up in below exception.

Caused by: liquibase.exception.DatabaseException: 
org.h2.jdbc.JdbcBatchUpdateException: Cannot parse "TIMESTAMP" constant ; SQL statement:

I debugged to the see the generated insert statement, and it seems like the CommandInterface is having empty "" string value rather than having NULL when I stepped into JdbcPreparedStatement class.

Edit: Accidentally, I tried to set the datatype of the column (that can have NULL) in my liquibase script and that fixed the issue.

Answer 1

I am on liquibase version 3.5.5 and using 'NULL' in my csv worked for me

sample liquibase file:

id,field1,field2,field3
11,NULL,NULL,test

will result in NULL values being inserted into field1 and field2 respectively.

Answer 2

Added these column tag with datatype explicitly in my changelog and that fixed the issue. Seems to be a liquibase issue rather than H2 DB.

<loadData tableName="Order" file="test_data/Order.csv">
  <column name="CreatedAt" type="TIMESTAMP"/>
  <column name="UpdatedAt" type="TIMESTAMP"/>
  <column name="OrderAt" type="TIMESTAMP"/>
  <column name="BusinessDate" type="DATE"/>
  <column name="ClaimedAt" type="TIMESTAMP"/>
  <column name="SnapshotID" type="STRING"/>
</loadData>

Note: My table does have more columns. These are the columns which can accept null values.

It took me 2 full days to find this. As all you see is H2 DB exception. Liquibase seems to default to empty string even though csv have null value, until unless you specify the column with its datatype.