MIME-Version: 1.0
References: <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com>
 <CACJufxHj0PejJC8BMq1cCiFC5uFdnazqrHh_VEm=s65-cNSnwQ@mail.gmail.com>
 <CALDaNm0Q3hA9w76gvB=goOY4ktKb-wjrbZbOcrqSwvxdm0yrcw@mail.gmail.com>
 <CACJufxEueK_MFebYqp73e+P+ykbD4+QbONptMg=iaaxUEGU7EQ@mail.gmail.com>
 <CALDaNm0FUtPjH9wz5dgwOBNtsAXEZj=0-TYGsyQXchQy-hXGrw@mail.gmail.com>
 <CACJufxFpm2Gzx4AD9qKtiQSiiiunX02wNTxu0JoFm7nEKF2KUw@mail.gmail.com>
 <CAD21AoBjmEYjZT9A6P-vAuwboiiYtXaMc12pX2ySmh3RXi=v8w@mail.gmail.com>
 <CACJufxF6_YwAboiCaVYLRtNpO4kGbXqkXzH_7W=pUvrNXK8WuQ@mail.gmail.com>
 <CAD21AoBWwEzpp3Z6tp-O-AQGftK-kuj6vRva2L5daWoWBtbnRg@mail.gmail.com>
 <CACJufxGEHmijmP-QqvrmqU6cxmhgpdjY7ewQBQ=E9NmdyEcqmw@mail.gmail.com>
 <f5ebfa4b318e97c41c0919304b94c77f@oss.nttdata.com> <CACJufxG=em0PHZvy1EAZ+vxPZ8UA68MfQ-Hji+h+WgnWNpqmVQ@mail.gmail.com>
 <CACJufxF0c3k5O8up9NOY-m02nyJ0f6N1tKxZwjCewTqvvFmbLw@mail.gmail.com>
 <CACJufxHr-LBV2pB9m64mA=1pgVMM4LvUn+-MzpeViWV=Ks_cyg@mail.gmail.com>
 <901967e5-e5dc-42c6-b2bf-fb3a49d7e787@gmail.com> <CACJufxEs_VBV39gYHnpLFOMcUaUD-ADAst_ePTCgmoDR8O=ekg@mail.gmail.com>
 <DFUHPCSP86G0.11VU7FGWRRXM2@gmail.com>
In-Reply-To: <DFUHPCSP86G0.11VU7FGWRRXM2@gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Thu, 22 Jan 2026 22:45:10 +0800
Message-ID: <CACJufxGYPXQ_Jz1avF5eSh_XJRsxhPSUZ+=RzG3Hz4_XNAc32g@mail.gmail.com>
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
To: Matheus Alcantara <matheusssilv97@gmail.com>
Cc: torikoshia <torikoshia@oss.nttdata.com>, Masahiko Sawada <sawada.mshk@gmail.com>, 
	vignesh C <vignesh21@gmail.com>, Jim Jones <jim.jones@uni-muenster.de>, 
	Kirill Reshke <reshkekirill@gmail.com>, Fujii Masao <masao.fujii@oss.nttdata.com>, 
	"David G. Johnston" <david.g.johnston@gmail.com>, Yugo NAGATA <nagata@sraoss.co.jp>, 
	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/CACJufxGYPXQ_Jz1avF5eSh_XJRsxhPSUZ%2B%3DRzG3Hz4_XNAc32g%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 22, 2026 at 2:47=E2=80=AFAM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:
>
> Thanks for the new version. I have some comments on this first round of
> review:
>
> + errmsg_plural("invalid values in %" PRIu64 " row was replaced with null=
 due to data type incompatibility",
> +   "invalid values in %" PRIu64 " rows were replaced with null due to da=
ta type incompatibility",
>
> I think that we could remove the "invalid values in" to make it
> consistency with the COPY_ON_ERROR_IGNORE NOTICE
>
sure.

> ----------
>
> +               cstate->domain_with_constraint =3D (bool *) palloc0(attr_=
count * sizeof(bool));
>
> I think that we can use palloc_array?
>
sure.

> ----------
>
> Should FORCE_NOT_NULL be allowed to be used with ON_ERROR set_null? It
> seems to me that ON_ERROR set_null overwrite the FORCE_NOT_NULL
> behaviour:
>
> postgres=3D# create table t4(a int, b varchar(5));
> CREATE TABLE
>
> postgres=3D# copy t4 from 'data.csv' with (FORCE_NOT_NULL(b), format csv,=
 delimiter ',', NULL 'NULL', ON_ERROR set_null);
> NOTICE:  invalid values in 2 rows were replaced with null due to data typ=
e incompatibility
> COPY 5
>
> postgres=3D# \pset null 'NULL'
> Null display is "NULL".
> postgres=3D# select * from t4;
>  a |  b
> ---+------
>  1 | aaaa
>  2 | bbbb
>  2 | NULL
>  2 | NULL
>  5 | NULL
> (5 rows)
>
> Note that only the ccccc rows on .csv file was inserted with a NULL
> value on b column. The 5,NULL row was inserted with a "NULL" string as a
> value:
>
> postgres=3D# select * from t4 where b is null;
>  a |  b
> ---+------
>  2 | NULL
>  2 | NULL
> (2 rows)
>
> The contents of data.csv:
>     1,aaaa
>     2,bbbb
>     2,ccccc
>     2,ccccc
>     5,NULL
>
> Perhaps we should block the usage of FORCE_NOT_NULL with ON_ERROR
> SET_NULL?
>
FORCE_NOT_NULL is related to how we handle NULL string in column value.

We first process cstate->opts.force_notnull_flags, cstate->opts.force_null_=
flags
then InputFunctionCallSafe.
see copyfromparse.c, CopyFromTextLikeOneRow ``if (is_csv)``loop.

I think these two are unrelated things, FORCE_NOT_NULL should be fine with
ON_ERROR SET_NULL.
you can see related tests in
https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/sql/co=
py2.sql#n330

Am I missing something?

>
> On monitoring.sgml we have the following for pg_stat_progress_copy
> tuples_skipped:
>        Number of tuples skipped because they contain malformed data.
>        This counter only advances when a value other than
>        <literal>stop</literal> is specified to the <literal>ON_ERROR</lit=
eral>
>
> IIUC we are not updating this view if we set a column to NULL due to an
> error, perhaps this documentation should be updated to mention that it
> will not be updated with ON_ERROR set_null?
>

IMHO, we don't need to mention ON_ERROR set_null, since we do not support i=
t.
change to the following should be ok, i think.

      <para>
       Number of tuples skipped because they contain malformed data.
       This counter only advances when
       <literal>ignore</literal> is specified to the <literal>ON_ERROR</lit=
eral>
       option.
      </para></entry>

>
> I may have missing something, but we are still considering implementing
> the REJECT_LIMIT + ON_ERROR set_null?
Possibly as a separate patch later.


--
jian
https://www.enterprisedb.com/