<loadData> cannot import columns of type "timestamp with time zone" into PostgreSQL

General Discussion
1

The following CSV import fails when using PostgreSQL:

"id","published","actor","object","sha256sum"
31a42812-e8c9-42e2-a6d2-06ba3df90039,"2021-08-24T09:51:26.000Z",474a7c07-9817-44ff-bb21-5621362df9bb,a60b32c3-bc88-4042-9974-4624279c23af,e716208f0b64b90f103f72272fcad2fc5c9db56de364fafd116d6724cb4940fd

The <loadData> statement looks like this:


      <loadData file="liquibase/migrations/data/atl-77/test/default/csv/tl_activities.csv" tableName="tl_activities" usePreparedStatements="true">
         <column name="ID" type="UUID"/>
         <column name="PUBLISHED" type="timestamp(6) with time zone"/>
         <column name="ACTOR" type="UUID"/>
         <column name="OBJECT" type="UUID"/>
         <column name="SHA256SUM" type="VARCHAR(64)"/>
      </loadData>

The exception is:

liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: column "published" is of type timestamp with time zone but expression is of type character varying

It seems that Liquibase does not set the value correctly in the prepared statement.

Is this a known bug, and does anybody know any workaround?

2

In ExecutablePreparedStatementBase.applyColumnParameter() the debugger tells me that col.getType() returns null, which leads to:

         } else {
            stmt.setString(i, col.getValue());
         }

col.getValue() returns “2021-08-24T09:51:26.000Z”, as expected.

So, even though Liquibase supports the column type timestamp(6) with time zone when creating the table, it does not support it in <loadData>.

3

The problem is in LoadDataColumnConfig:

    public LoadDataChange.LOAD_DATA_TYPE getTypeEnum() {
        final String type = this.getType();
        if (type == null) {
            return null;
        }
        if (this.loadType == null) {
            try {
                this.loadType = LoadDataChange.LOAD_DATA_TYPE.valueOf(type.toUpperCase());
            } catch (IllegalArgumentException e) {
                return LoadDataChange.LOAD_DATA_TYPE.UNKNOWN;
            }
        }
        return this.loadType;
    }

The line this.loadType = LoadDataChange.LOAD_DATA_TYPE.valueOf(type.toUpperCase()) does not work for the type “timestamp(6) with time zone”.

4

Unfortunately Liquibase does not support time zones at all. ISODateFormat can only deal with zoneless timestamps, too.

5

I created issue 2068.