I have data loaded from source table (On-premise Oracle 12c) and I’m supposed to write it to target table (PostgreSQL 10.5). The structure of the tables is the same except that target table contains several columns that don’t occur in the source table. The one I need your help with is a timestamp value. It’s supposed to be a time when data is loaded. I don’t want current timestamp of source database which is the reason I don’t use the simplest way like select src.*, current_date as sa_timestamp, … Instead I need to set the column value in my workflow. The problem is I can’t insert it into target table without annoying messages like:
ERROR Database Writer 2:2 Execute failed: java.lang.RuntimeException: Column “sa_timestamp” of type “Local Date Time” from input does not match type “timestamp” in database at position 17
ERROR Database Writer 2:2 Execute failed: java.lang.RuntimeException: Column “sa_timestamp” of type “Zoned Date Time” from input does not match type “timestamp” in database at position 17
ERROR Database Writer 2:2 Execute failed: java.lang.RuntimeException: Column “sa_timestamp” of type “String” from input does not match type “timestamp” in database at position 17
I tried using Date & Time Input node with “Use execution time” option checked.
I tried changing db query to “select src.*, ‘$${Scurrent_timestamp}$$’ sa_timestamp …” subsequently with no success.
I tried using String to Date/Time node too.
Each attempt resulted in error message listed above.
I’d appreciate any advice. Please note changing type of the target column is the last option I’d rather avoid.
Thanks,
Jan