MIME-Version: 1.0
References: 
 <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Mon, 29 Jan 2024 07:50:00 +0800
Message-ID: 
 <CACJufxExhY9P8a-rk21wR2NXF-UydMjictyiKiMMi_Z4iX6gNQ@mail.gmail.com>
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxExhY9P8a-rk21wR2NXF-UydMjictyiKiMMi_Z4iX6gNQ%40mail.gmail.com>
Precedence: bulk

On Fri, Jan 26, 2024 at 11:09=E2=80=AFPM David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> Hi,
>
> The option choice of "ignore" in the COPY ON_ERROR clause seems overly ge=
neric.  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 se=
e us wanting to provide the set to null option and in any case having the o=
ption 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 );

you can only do:
COPY x1 from stdin (on_error 'null');
but you cannot do
COPY x1 from stdin (on_error null);
we need to hack the gram.y to escape the "null". I don't know how to
make it work.
related post I found:
https://stackoverflow.com/questions/31786611/how-to-escape-flex-keyword

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.