1

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

2
  • Have a look at this extension: github.com/MigOpsRepos/pg_dbms_errlog
    – user1822
    Sep 15, 2021 at 21:44
  • That's interesting. Since I'm using Postgresql in an AWS Aurora environment, would extensions like this be possible?
    – SQL RV
    Sep 17, 2021 at 0:51

2 Answers 2

0

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.

1
  • Yeah, I was hoping to avoid the single-record insert scenario as 99+% of the records would most likely be just fine. It's either that or validate the data ahead of time as much as possible, but it's possible I could still miss something.
    – SQL RV
    Sep 17, 2021 at 0:52
0

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,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.