jeacott opened a new issue, #9036: URL: https://github.com/apache/seatunnel/issues/9036
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/seatunnel/issues?q=is%3Aissue+label%3A%22bug%22) and found no similar issues. ### What happened I have a source oracle column of type NUMBER(1, 0); and a matching postgres sink column of type smallint (int2) the seatunnel documentation for the Oracle connector says: Oracle type->Seatunnel type NUMBER(precision <= 9, scale == 0) -> INT no mention of any BOOLEAN at all in the docs: https://seatunnel.apache.org/docs/2.3.9/connector-v2/source/Oracle the docs for the postgres sink says ref: https://seatunnel.apache.org/docs/2.3.9/connector-v2/sink/PostgreSql Postgres type: Seatunnel Type INT2 : INT SMALLSERIAL : INT INT4 : INT SERIAL : INT the problem is Seatunnel is mapping NUMBER(1, 0) -> BOOLEAN and hence I am seeing errors ``` ERROR: column "mycol" is of type smallint but expression is of type boolean Hint: You will need to rewrite or cast the expression. Position: 870 Call getNextException to see other errors in the batch. at org.postgresql.jdbc.BatchResultHandler.handleCompletion(BatchResultHandler.java:186) at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:878) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:916) at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1684) at org.apache.seatunnel.shade.com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127) at org.apache.seatunnel.shade.com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.FieldNamedPreparedStatement.executeBatch(FieldNamedPreparedStatement.java:540) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.SimpleBatchStatementExecutor.executeBatch(SimpleBatchStatementExecutor.java:54) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.BufferReducedBatchStatementExecutor.executeBatch(BufferReducedBatchStatementExecutor.java:89) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.attemptFlush(JdbcOutputFormat.java:172) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:136) ... 10 more Caused by: org.postgresql.util.PSQLException: ERROR: column "mycol" is of type smallint but expression is of type boolean Hint: You will need to rewrite or cast the expression. Position: 870 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:316) at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:874) ... 19 more ``` ### SeaTunnel Version 2.3.9 ### SeaTunnel Config ```conf env { parallelism = 8 job.mode = "BATCH" } source { Jdbc { plugin_output = "s1" driver = oracle.jdbc.driver.OracleDriver url = "jdbc:oracle:thin:@//..." user = sch password = xxx use_select_count = true table_list = [ { table_path = "SCH.MYTABLE" } ] partition_column = "CREATION_DATE" properties { database.oracle.jdbc.timezoneAsRegion = "false" } } } transform { TableRename { plugin_input = "s1" plugin_output = "s1_lc" convert_case = "LOWER" prefix = "" suffix = "" } } sink { Jdbc { plugin_input = "s1_lc" driver = org.postgresql.Driver url = "jdbc:postgresql://xxx..." generate_sink_sql = true user = xxx password = xxx database = "mydb" schema = "public" field_ide = LOWERCASE schema_save_mode = ERROR_WHEN_SCHEMA_NOT_EXIST } } ``` ### Running Command ```shell $ docker run --name seatunnel_client --network compose_seatunnel_network -v /`pwd`/templates:/mnt/templates -e ST_DOCKER_MEMBER_LIST=172.16.0.2:5801 --rm apache/seatunnel ./bin/seatunnel.sh -c '/mnt/templates/jdbc_oracle_to_pg.conf' --async ``` ### Error Exception ```log ERROR: column "mycol" is of type smallint but expression is of type boolean Hint: You will need to rewrite or cast the expression. Position: 870 Call getNextException to see other errors in the batch. at org.postgresql.jdbc.BatchResultHandler.handleCompletion(BatchResultHandler.java:186) at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:878) at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:916) at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1684) at org.apache.seatunnel.shade.com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127) at org.apache.seatunnel.shade.com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.FieldNamedPreparedStatement.executeBatch(FieldNamedPreparedStatement.java:540) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.SimpleBatchStatementExecutor.executeBatch(SimpleBatchStatementExecutor.java:54) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.BufferReducedBatchStatementExecutor.executeBatch(BufferReducedBatchStatementExecutor.java:89) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.attemptFlush(JdbcOutputFormat.java:172) at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:136) ... 10 more Caused by: org.postgresql.util.PSQLException: ERROR: column "mycol" is of type smallint but expression is of type boolean Hint: You will need to rewrite or cast the expression. Position: 870 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:316) at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:874) ... 19 more ``` ### Zeta or Flink or Spark Version _No response_ ### Java or Scala Version _No response_ ### Screenshots _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
