On Fri, Jan 26, 2024 at 11:09 PM David G. Johnston <david.g.johnston@gmail.com> wrote: > > Hi, > > The option choice of "ignore" in the COPY ON_ERROR clause seems overly generic. There would seem to be two relevant ways to ignore bad column input data - drop the entire row or just set the column value to null. I can see us wanting to provide the set to null option and in any case having the option name be explicit that it ignores the row seems like a good idea.
two issue I found out while playing around with it; create table x1(a int not null, b int not null );
another issue: COPY x1 from stdin (on_error null);
when we already have `not null` top level constraint for table x1. Do we need an error immediately? "on_error null" seems to conflict with `not null` constraint (assume refers to the same column). it may fail while doing bulk inserts while on_error is set to null because of violating a not null constraint.
You should not error immediately since whether or not there is a problem is table and data dependent. I would not check for the case of all columns being defined not null and just let the mismatch happen.
That said, maybe with this being a string we can accept something like: 'null, ignore'
And so if attempting to place any one null fails, assuming we can make that a soft error too, we would then ignore the entire row.