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

• user1822: Have a look at this extension: github.com/MigOpsRepos/pg_dbms_errlog
• SQL RV: That's interesting. Since I'm using Postgresql in an AWS Aurora environment, would extensions like this be possible?

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.

• SQL RV: 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.

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,

• Jens: How would you get from exception data to the faulty row?
• Jasen: in the exception branch of the 'for each row do instead' code insert the bad data into a table that has fewer rules.