Feedback-ID: ie0a040ee:Fastmail
Message-ID: <6f973222-f306-43af-9df5-38673fe3f7d6@eisentraut.org>
Date: Wed, 25 Feb 2026 08:36:20 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
To: jian he <jian.universality@gmail.com>,
 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>
References: <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@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>
 <CACJufxGYPXQ_Jz1avF5eSh_XJRsxhPSUZ+=RzG3Hz4_XNAc32g@mail.gmail.com>
 <4c540fe3-495c-4bbf-8dcf-2c1e2b88bc3d@gmail.com>
 <CACJufxHFwQMw1As+QFk+fA7S8ZxRG2wOvHcvmsWuj2XJ+W6d_A@mail.gmail.com>
 <DG7WARDX1TY0.2VX3Q7QHSCXE9@gmail.com>
 <CACJufxGUP09S7a2akQ4GD62-Rf2bcV52cqnw4qqBOXcn9bmxCA@mail.gmail.com>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <CACJufxGUP09S7a2akQ4GD62-Rf2bcV52cqnw4qqBOXcn9bmxCA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/6f973222-f306-43af-9df5-38673fe3f7d6%40eisentraut.org>
Precedence: bulk

I have a few more cosmetic changes to suggest:

- doc/src/sgml/ref/copy.sgml

+      and <literal>set_null</literal> means replace field containing 
invalid

should be "the field" and "the invalid"

+      input value with <literal>NULL</literal> and continue to the next 
field.

change <literal>NULL</literal> to "a null value"

+    <para>
+      For <literal>ignore</literal> option, a <literal>NOTICE</literal> 
message
+      containing the ignored row count is emitted at the end of the 
<command>COPY FROM</command>
+      if at least one row was discarded.
+      For <literal>set_null</literal> option, a <literal>NOTICE</literal>
+      message indicating the number of rows where invalid input values were
+      replaced with null is emitted at the end of the <command>COPY 
FROM</command>
+      if at least one row was replaced.
+     </para>

I think this could be written more compactly, like

If on_error is set to ignore or set_null, a NOTICE message is emitted
at the end of the COPY FROM command containing the count of rows that
were ignored or changed, if at least one row was affected.


- src/backend/commands/copy.c

     /*
-    * Allow "stop", or "ignore" values.
+    * Allow "stop", "ignore", "set_null" values.
      */

Just remove that comment.  It is evident from the following code.


- src/backend/commands/copyfrom.c

+ "%" PRIu64 " rows were replaced with null due to data type 
incompatibility"

I think this is not quite correctly worded.  It should be something like

     in NNN rows, columns were set to null due to ...

because you are not setting the whole row to null.

         /*
-        * Currently we only support COPY_ON_ERROR_IGNORE. We'll add other
-        * options later
+        * Currently we only support COPY_ON_ERROR_IGNORE,
+        * COPY_ON_ERROR_SET_NULL. We'll add other options later
          */

Delete this comment.

+   if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+   {
+       int         attr_count = list_length(cstate->attnumlist);
+
+       cstate->domain_with_constraint = palloc0_array(bool, attr_count);

Maybe add a comment for this block to explain that you are collecting 
information about domains for later.


- src/backend/commands/copyfromparse.c

         /*
-        * If ON_ERROR is specified with IGNORE, skip rows with soft errors
+        * If ON_ERROR is specified with IGNORE, skip rows with soft errors.
+        * If ON_ERROR is specified with SET_NULL, try to replace with null.
          */

Trim this comment.  Maybe "If ON_ERROR is specified, handle the 
different options".  We don't need to re-explain here what the options do.

+           /*
+            * If the column type is a constrained domain, an additional
+            * InputFunctionCallSafe may be needed to raise error for
+            * domain constraint violation.
+            */

Why "may be needed"?  Is it sometimes not needed?  Why, under what 
circumstances?

The subsequent error message writes "domain ... does not allow null 
values", but AFAICT a domain input failure could also be due to a check 
constraint failure?  How would that be handled?  The flow here is a bit 
confusing.

- src/test/regress/sql/copy2.sql

I suggest adding a space after "--" in a comment, like "-- error" 
instead of "--error".

Similarly, a space after CHECK, like "CHECK (...)".