Setting PostgreSQL enums using a JDBC ResultSet

I'm trying to copy data from a MySQL database to an equivalent PostgreSQL database, doing a load/insert through Java using JDBC. I get this error whenever I try to copy an enum column:

org.postgresql.util.PSQLException: ERROR: column "mycol" is of type mytable_mycol_enum but expression is of type character varying

Hint: You will need to rewrite or cast the expression.

Position: 194

The Java code (simplified and anonymized):

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection mysqlConn = DriverManager.getConnection(
    "jdbc:mysql://localhost/mysqldb", "user", "pass");
Class.forName("org.postgresql.Driver").newInstance();
Connection pgConn = DriverManager.getConnection(
    "jdbc:postgresql://othercomp/pgdb", "user", "pass");

开发者_高级运维Statement selStatement = mysqlConn.createStatement();
ResultSet selSet = selStatement.executeQuery("SELECT * FROM mytable");

Statement insStatement = pgConn.createStatement(
    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
Resultset insSet = insStatement.executeQuery("SELECT * FROM mytable WHERE 0=1");
insSet.moveToInsertRow();

while(selSet.next())
{
    ResultSetMetaData metaData = selSet.getMetaData();
    for (int i = 1; i <= metaData.getColumnCount(); i++)
    {
        String colName = metaData.getColumnName(i);
        Object obj = selSet.getObject(colName);
        insertSet.updateObject(colName, obj);
    }
    insertSet.insertRow();
}

This works fine for tables without an enum column, but

In MySQL I have a column mycol of type enum('A','B','C')

In PostgreSQL I have mycol of type mytable_mycol_enum with CREATE TYPE mytable_mycol_enum AS ENUM ('A','B','C')

How do I set the enum value?

You have to cast the value, so that postgresql understands it:

insert into mytable (mycol) values ('A'::mytable_mycol_enum)

You might have to use a named insert statement, not the generic one in your example. Also, try it in psql first to see if it works.

HTH