Column "createdtime" is of type timestamp without time zone but expression is of type character varying

Liquibase Development
1

Hello,

I’m struggling and I don’t understand.

It is working at the company and I’m making something for myself - study project- with Java/Spring boot, liquibase. So it is not the same environment, work is cloud and here my localhost.

I’want to make it at least working for H2, MySQL en PostgreSQL database. It is working for H2, maybe is H2 less critical, but with PostgreSQL I get the error: “Reason: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: column “createdtime” is of type timestamp without time zone but expression is of type character varying”

This is my table (entity) definition:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd" >

        <changeSet id="001" author="nkamp">
        <createTable tableName="demos">
            <column name="id" type="BIGINT"  autoIncrement="true" >
                <constraints nullable="false" primaryKey="true" unique="true" primaryKeyName="demo_pkey" />
            </column>
            ... here other columns
            <column name="createdtime" type="TIMESTAMP WITHOUT TIME ZONE" />
            <column name="modifiedtime" type="TIMESTAMP WITHOUT TIME ZONE" />
        </createTable>
        <rollback>
            <dropTable tableName="demos" />/>
        </rollback>
    </changeSet>

Here is my loaddata:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">

    <changeSet id="001" author="nkamp">
        <loadData encoding="UTF-8"
                  catalogName="cat"
                  file="../csv/demos.csv"
                  separator=";"
                  tableName="demos"
                  relativeToChangelogFile="true" >
            ... Other columns
            <column header="createdTime" name="createdtime" type="TIMESTAMP WITHOUT TIME ZONE" />
            <column header="modifiedTime" name="modifiedtime" type="TIMESTAMP WITHOUT TIME ZONE" />
        </loadData>
    </changeSet>

Here is my demo.csv.

naam;omschrijving;status;closure;werk;waardea;waardeb;waardec;createdtime;modifiedtime
Demo 1;omschrijving demo 1;1A;closure 1;werk 1;2;3;2;2021-12-09 11:11:11.953406;2016-11-09 11:11:11.953406

If I google, than I’m not first one with this issue, some of the posts are already 4 years old what is amazing me.

Can anyone put me in the right direction to solve this? Or can anyone tell what todo if I have to import csv data with timestamps?

2

Goodmorning,

Nobody an idea how to solve this? Or is it my question not clear or stupid? I’m little bit confused, how to solve this.

3

I too get the same issue, in my case createdTime is datetime

<column name="performed_at" type="datetime"/>

and my csv is

id;trace_id;performed_at;status
1;bcbbb4db-2a06-4a38-aa51-34d01fd7a1e2;2021-08-10T22:23:41;VERIFIED
4

I resolved it by removing

usePreparedStatements="true"

and using
postgres driver “42.3.2”
liquibase-core “4.7.1”