Postgres: Insert all valid data into a table while logging errors

Question

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! :)

Answer 1

If you're looking for bad values, you'll need to run INSERT for each value independently. Each command is its own "transaction" so the whole INSERT succeeds or the whole INSERT fails.

Take a look at pgloader which implements bad-value logging while performing batch inserts for performance.

Answer 2

To get an exception called for each bad record you pretty-much need to use a loop.

you could use an on insert do instead rule, but that just creates a loop if you're inserting many rows.

it's probably easier to just try the insert and then process the exception data to find out which row was bad,