Configuring PostgreSQL Enumerations Using JDBC ResultSet

Question

I am trying to copy data from a MySQL database to an equivalent PostgreSQL database by doing a upload / paste through Java using JDBC. I get this error whenever I try to copy an enumeration column:

org.postgresql.util.PSQLException: ERROR: the "mycol" column is of type mytable_mycol_enum, but the expression is of a type that varies depending on the type of Tip: you will need to rewrite or pass the expression.
Position: 194

Java code (simplified and anonymous):

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 enumeration column, but

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

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

How to set the value of an enumeration?

Answer 1

You must specify a value for postgresql to understand:

enter the values ​​mytable (mycol) ('A' :: mytable_mycol_enum)

You may need to use the named insert statement rather than the generic one in your example. Also, first try in psql to see if it works.

NTN