Search code examples
postgresqlliquibase

Liquibase: how to load data with CURRENT_TIMESTAMP?

I am trying to update my project, going from

    <liquibase.version>3.5.5</liquibase.version>
    <liquibase-hibernate5.version>3.6</liquibase-hibernate5.version>

to

    <liquibase.version>3.9.0</liquibase.version>
    <liquibase-hibernate5.version>3.8</liquibase-hibernate5.version>

and I'm having some troubles while loading data from CSV files, which contain some columns having the current timestamp as value.

E.g my CSV file:

id;name;created;modified
1;Book A;now();now()
2;Book B;now();now()

The Book table is created with:

<createTable tableName="book">
    <column name="id" type="bigint" autoIncrement="${autoIncrement}">
        <constraints primaryKey="true" nullable="false"/>
    </column>
    <column name="name" type="varchar(255)">
        <constraints nullable="false"/>
    </column>
    <column name="created" type="timestamp">
        <constraints nullable="false"/>
    </column>
    <column name="modified" type="timestamp">
        <constraints nullable="false"/>
    </column>
</createTable>

And the data is loaded with:

<property name="now" value="current_timestamp" dbms="postgresql"/>

<changeSet id="20180508144233-1" author="developer">
    <loadData catalogName="public"
              encoding="UTF-8"
              file="config/liquibase/books.csv"
              schemaName="public"
              separator=";"
              quotchar="'"
              tableName="book">
    </loadData>
</changeSet>

With the previous version of Liquibase it was working fine, however after the update I am getting the following error:

2020-06-26 16:49:57 [project-Executor-1] [ERROR] liquibase.changelog.ChangeSet - Change Set config/liquibase/changelog/20180508144233_added_books_data.xml::20180508144233-1::developer failed.  Error: liquibase.exception.DateParseException: Improper value in 'NOW' value: now(). 'NOW' must be followed by + or -, then numeric offset, then units (h{our{s}}, m{inute{s}}, d{ay{s}}, or y{ears}
Hibernate: select answerweig0_.id as id1_1_, answerweig0_.likelihood as likeliho2_1_, answerweig0_.question_type as question3_1_, answerweig0_.weight as weight4_1_ from answer_weight answerweig0_
2020-06-26 16:49:57 [project-Executor-1] [ERROR] i.g.j.c.l.AsyncSpringLiquibase - Liquibase could not start correctly, your database is NOT ready: Migration failed for change set config/liquibase/changelog/20180508144233_added_books_data.xml::20180508144233-1::developer:
     Reason: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DateParseException: Improper value in 'NOW' value: now(). 'NOW' must be followed by + or -, then numeric offset, then units (h{our{s}}, m{inute{s}}, d{ay{s}}, or y{ears}
liquibase.exception.MigrationFailedException: Migration failed for change set config/liquibase/changelog/20180508144233_added_books_data.xml::20180508144233-1::developer:
     Reason: liquibase.exception.UnexpectedLiquibaseException: liquibase.exception.DateParseException: Improper value in 'NOW' value: now(). 'NOW' must be followed by + or -, then numeric offset, then units (h{our{s}}, m{inute{s}}, d{ay{s}}, or y{ears}
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
    at liquibase.Liquibase.update(Liquibase.java:202)
    at liquibase.Liquibase.update(Liquibase.java:179)
    at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:366)
    at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:314)
    at org.springframework.boot.autoconfigure.liquibase.DataSourceClosingSpringLiquibase.afterPropertiesSet(DataSourceClosingSpringLiquibase.java:46)
    at io.github.jhipster.config.liquibase.AsyncSpringLiquibase.initDb(AsyncSpringLiquibase.java:118)
    at io.github.jhipster.config.liquibase.AsyncSpringLiquibase.lambda$afterPropertiesSet$0(AsyncSpringLiquibase.java:93)
    at io.github.jhipster.async.ExceptionHandlingAsyncTaskExecutor.lambda$createWrappedRunnable$1(ExceptionHandlingAsyncTaskExecutor.java:78)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Solution

  • As a workaround, I was able to set the values for such columns by using a default value in the table creation changelog:

    <createTable tableName="book">
    <column name="id" type="bigint" autoIncrement="${autoIncrement}">
        <constraints primaryKey="true" nullable="false"/>
    </column>
    <column name="name" type="varchar(255)">
        <constraints nullable="false"/>
    </column>
    <column name="created" type="timestamp" defaultValueComputed="CURRENT_TIMESTAMP">
        <constraints nullable="false"/>
    </column>
    <column name="modified" type="timestamp" defaultValueComputed="CURRENT_TIMESTAMP">
        <constraints nullable="false"/>
    </column>
</createTable>

    and removing the corresponding columns from the CSV file:

    id;name
1;Book A
2;Book B

    However, I'm still looking for a way to keep the CURRENT_TIMESTAMP value in the CSV file.