I am populating table data from a file using the copy command. The table includes timestamp data in multiple formats. I have set alter session set TIMESTAMP_INPUT_FORMAT = 'dd-mon-yyyy hh24.mi.ss.ff6'; which handles the formatting of certain values thus formatted, but there are other timestamp values in the source file that are formatted differently. To cope with this I am doing e.g.
copy into <table> (
<timestamp_column_1>,
<timestamp_column_2>
...
) from (
SELECT
$1,
TO_TIMESTAMP_TZ(t.$2, 'DD-MON-YY')
This works, but the validate command does not support transformations, so my current validation method is unrealiable.
Is there a way I can achieve what I want in my load without using transformations?