On Sun, Jan 28, 2024 at 4:51 PM jian he <jian.universal...@gmail.com> wrote:
> On Fri, Jan 26, 2024 at 11:09 PM David G. Johnston > <david.g.johns...@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. David J.
