[HOP-4183] JDBC connector for Postgres errors out with UUID datatype

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: Migrated to GHI
• Component/s: Database
• Assignee: Unassigned
• Reporter: Arquimedes
• Created: 01/Sep/22 03:13
• Updated: 15/Feb/23 09:36
• Resolved: 15/Feb/23 09:36

Description

Currently Hop does not support the UUID datatype in Postgres, and it tries to use varchar type, causing an error in the database.

 

For example, in the Update action:

2022/09/01 00:11:40 - Update existing table record.0 - ERROR: Error in transform, asking everyone to stop because of:
2022/09/01 00:11:40 - Update existing table record.0 - ERROR: org.apache.hop.core.exception.HopDatabaseBatchException: 
2022/09/01 00:11:40 - Update existing table record.0 - Error updating batch
2022/09/01 00:11:40 - Update existing table record.0 - Batch entry 0 UPDATE client_crm.listing
2022/09/01 00:11:40 - Update existing table record.0 - SET is_outdated = 'FALSE'
2022/09/01 00:11:40 - Update existing table record.0 - ,   date_last_observation = '2022-09-01 00:11:33.993-03'
2022/09/01 00:11:40 - Update existing table record.0 - WHERE  ( ( idf_record = '6db2a3f5-d76c-445d-b0a6-6a4c845ce94e'  ) )  was aborted: ERROR: operator does not exist: uuid = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 104  Call getNextException to see other errors in the batch.
2022/09/01 00:11:40 - Update existing table record.0 - 
2022/09/01 00:11:40 - Update existing table record.0 -     at org.apache.hop.core.database.Database.createHopDatabaseBatchException(Database.java:1224)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.apache.hop.core.database.Database.insertRow(Database.java:1111)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.apache.hop.pipeline.transforms.update.Update.lookupValues(Update.java:186)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.apache.hop.pipeline.transforms.update.Update.processRow(Update.java:330)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.apache.hop.pipeline.transform.RunThread.run(RunThread.java:51)
2022/09/01 00:11:40 - Update existing table record.0 -     at java.base/java.lang.Thread.run(Thread.java:834)
2022/09/01 00:11:40 - Update existing table record.0 - Caused by: java.sql.BatchUpdateException: Batch entry 0 UPDATE client_crm.listing
2022/09/01 00:11:40 - Update existing table record.0 - SET is_outdated = 'FALSE'
2022/09/01 00:11:40 - Update existing table record.0 - ,   date_last_observation = '2022-09-01 00:11:33.993-03'
2022/09/01 00:11:40 - Update existing table record.0 - WHERE  ( ( idf_record = '6db2a3f5-d76c-445d-b0a6-6a4c845ce94e'  ) )  was aborted: ERROR: operator does not exist: uuid = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 104  Call getNextException to see other errors in the batch.
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:871)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1638)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.apache.hop.core.database.Database.insertRow(Database.java:1098)
2022/09/01 00:11:40 - Update existing table record.0 -     ... 4 more
2022/09/01 00:11:40 - Update existing table record.0 - Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 104
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:315)
2022/09/01 00:11:40 - Update existing table record.0 -     at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:868)
2022/09/01 00:11:40 - Update existing table record.0 -     ... 7 more