insert date in composite type during a @DatabaseSetup

In postgresql, I have a user defined type like that:

create type my_type as (
  ts  timestamptz,
  val int
)

Then a table using that type:

create table my_table (
  id serial,
  ds my_type
)

I can insert and query data into that table in sql easily, but I can't find a way to insert data into it through an xml dataset file used by @DatabaseSetup for a test. I tried a few different variations like:

<my_table id="1"
          ds='("2025-04-03 09:38:08", 2)'
          ds.ts="2025-04-03 09:38:08"
          ds.val="2">

Whatever the syntax I tried, it results in a org.dbunit.dataset.NoSuchColumnException: my_table.ds - (Non-uppercase input column: ds) in ColumnNameToIndexes cache map. Note that the map's column names are NOT case sensitive.

I can't put data into that column with a composite type. Any idea how to solve that?

Solution

I found the solution, it requires creating some classes to explicitly handle the custom type:

One class extending AbstractDataType and overriding setSqlValue like that:

public class DbUnitMyType extends AbstractDataType {
    private static final Class CLASS_TYPE = Struct.class;

    public DbUnitMyType(String name, int sqlType) {
        super(name, sqlType, CLASS_TYPE, false);
    }

    @Override
    public Object typeCast(Object arg0) {
        return arg0 == null ? null : arg0.toString();
    }

    @Override
    public Object getSqlValue(int column, ResultSet resultSet) throws SQLException {
        return resultSet.getString(column);
    }

    @Override
    public void setSqlValue(Object value, int column, PreparedStatement statement) throws SQLException {
        statement.setObject(column, value, 1111);
    }
}

Not too sure about the 1111 value (java.sql.Types.OTHER), but it works.

Another extending PostgresqlDataTypeFactory like that:

public class DbUnitCustomPostgresqlDataTypeFactory extends PostgresqlDataTypeFactory {

    @Override
    public DataType createDataType(int sqlType, String sqlTypeName, String tableName, String columnName) throws DataTypeException {
        if (sqlTypeName.equalsIgnoreCase("my_type")) {
            return new DbUnitMyType(sqlTypeName, sqlType);
        }

        return super.createDataType(sqlType, sqlTypeName, tableName, columnName);
    }
}

And use that factory instead of the default or PostgresqlDataTypeFactory one.