I am attempting to devise a method that will enable me to load data into a table which will load all valid data and log the invalid records. Ideally, I'd like to do this without looping through the data.
I wrote an example, but it doesn't work as I would like:
do $$ declare myval varchar(64); v_error_message_text text; begin create temporary table mytemp (mycol varchar(6)); insert into mytemp(mycol) values ('abcdef'),('ghijkl'),('mnopqr'),('stuvwxyz') on conflict do nothing returning myval; select * from mytemp; exception when others then get stacked diagnostics v_error_message_text = message_text; raise notice 'Value: %; Error: %', myval, v_error_message_text; end; $$ language plpgsql;
Obviously, the real example will get data from a SELECT statement that will be more complex and the target will be a static table. I would also like to be able to capture the offending values and insert them into a log table along with various error information from the
get stack diagnostics clause.
In the example, I would want the first 3 values to be loaded into the table and the fourth caught in the exception handler (it's too long for the column length). At this point, it seems that nothing gets loaded into the target table and I always get a NULL value for the variable.
I appreciate any assistance! :)