Ask Your Question
1

How do I cast a Postgres column type in JDBC Producer when sdc.operation.type is 3 (update)?

asked 2020-01-12 06:13:26 -0500

tmotters gravatar image

updated 2020-01-13 13:10:39 -0500

metadaddy gravatar image

I have a JDBC Producer stage which is writing to a Postgres table. The table contains one column of type ENUM (a user defined ENUM called my_data.enum_stack_status). I am setting record attribute sdc.operation.type to 1 for INSERT and 3 for UDPATE in a previous stage.

For INSERT operations, I have successfully managed to configure SDC to perform type casting from STRING (SDC datatype) to the correct ENUM type by specifying ?::my_data.enum_stack_status in Parameterized Value under Field to Column Mapping for this column in the JDBC Producer stage.

However, when I come to do an UPDATE on a record, the type casting doesn't occur and I get a Postgres error:

Message: Batch entry 0 UPDATE my_data.axxx_logs SET axxx = 5105, stack_status = 'ABC123', cxxx = 115, ... WHERE xxx_id = 558 AND polling_timestamp = '2019-12-31 12:00:18+00' was aborted: ERROR: column "stack_status" is of type my_data.enum_stack_status but expression is of type character varying Hint: You will need to rewrite or cast the expression. Position: 66 Call getNextException to see other errors in the batch. Cause: org.postgresql.util.PSQLException: ERROR: column "stack_status" is of type my_data.enum_stack_status but expression is of type character varying Hint: You will need to rewrite or cast the expression.

I initially received the same error on INSERT which is why I added the type cast in Parameterized Value mentioned above, however the cast seems to be ignored for UPDATEs.

Is this a bug? If not, is there a workaround that applies to INSERTs and UPDATEs?

Thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
0

answered 2020-01-13 13:10:23 -0500

metadaddy gravatar image

Looking at the code, we don't take the Parameterized Value into account when doing an UPDATE. Please file an issue at https://issues.streamsets.com/ with the above information. If you are a StreamSets customer, please open a support ticket with the same info.

edit flag offensive delete link more

Comments

1

Thanks, Pat. I have raised JIRA issue SDC-13274. I started browsing the source code to see if it looked like a fix I could contribute, but was uncertain which git repo to be looking in. Happy to look further if you could point me in the right direction. Thanks, Tim

tmotters gravatar imagetmotters ( 2020-01-17 09:56:00 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-01-12 06:13:26 -0500

Seen: 157 times

Last updated: Jan 13