Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row

From: jian he <[email-masked]>
Date: Wed, 21 Jan 2026 15:37:00 +0800

On Wed, Jan 21, 2026 at 3:55 AM Matheus Alcantara
<[email-masked]> wrote:
> Hi,
>
> The patch needs a new rebase, could you please send a new version?

sure. please check the attached.
From 30056bdb56d0d8f63333ccc4cb60808abfa91644 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 21 Jan 2026 11:22:47 +0800
Subject: [PATCH v21 1/1] COPY (on_error set_null)

If ON_ERROR SET_NULL is specified during COPY FROM, any data type conversion
errors will result in the affected column being set to NULL. However, column's
not-null constraints are still enforced, attempting to set a NULL value in such
columns will raise a constraint violation error.  This applies to column data
type is a domain with a NOT NULL constraint.

Author: Jian He <[email-masked]>
Author: Kirill Reshke <[email-masked]>

Reviewed-by:
Fujii Masao <[email-masked]>
Jim Jones <[email-masked]>
"David G. Johnston" <[email-masked]>
Yugo NAGATA <[email-masked]>
torikoshia <[email-masked]>
Masahiko Sawada <[email-masked]>
Atsushi Torikoshi <[email-masked]>

discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=[email-masked]
commitfest entry: https://commitfest.postgresql.org/patch/4810
---
 doc/src/sgml/ref/copy.sgml               | 35 +++++++---
 src/backend/commands/copy.c              |  6 +-
 src/backend/commands/copyfrom.c          | 45 ++++++++++---
 src/backend/commands/copyfromparse.c     | 85 ++++++++++++++++++++----
 src/bin/psql/tab-complete.in.c           |  2 +-
 src/include/commands/copy.h              |  1 +
 src/include/commands/copyfrom_internal.h |  7 ++
 src/test/regress/expected/copy2.out      | 55 +++++++++++++++
 src/test/regress/sql/copy2.sql           | 43 ++++++++++++
 9 files changed, 244 insertions(+), 35 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 53b0ea8f573..4d039b66221 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -413,22 +413,38 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       input value into its data type.
       An <replaceable class="parameter">error_action</replaceable> value of
       <literal>stop</literal> means fail the command, while
-      <literal>ignore</literal> means discard the input row and continue with the next one.
+      <literal>ignore</literal> means discard the input row and continue with the next one,
+      and <literal>set_null</literal> means replace column containing invalid
+      input value with <literal>NULL</literal> and continue to the next field.
       The default is <literal>stop</literal>.
      </para>
      <para>
-      The <literal>ignore</literal> option is applicable only for <command>COPY FROM</command>
+      The <literal>ignore</literal> and <literal>set_null</literal>
+      options are applicable only for <command>COPY FROM</command>
       when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
      </para>
+
+    <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>
+
      <para>
-      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. When <literal>LOG_VERBOSITY</literal> option is set to
-      <literal>verbose</literal>, a <literal>NOTICE</literal> message
+      When <literal>LOG_VERBOSITY</literal> option is set to <literal>verbose</literal>,
+      for <literal>ignore</literal> option, a <literal>NOTICE</literal> message
       containing the line of the input file and the column name whose input
-      conversion has failed is emitted for each discarded row.
+      conversion has failed is emitted for each discarded row;
+      for <literal>set_null</literal> option, a <literal>NOTICE</literal>
+      message containing the line of the input file and the column name where
+      value was replaced with <literal>NULL</literal> for each input conversion
+      failure.
       When it is set to <literal>silent</literal>, no message is emitted
-      regarding ignored rows.
+      regarding input conversion failed rows.
      </para>
     </listitem>
    </varlistentry>
@@ -476,7 +492,8 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
      </para>
      <para>
       This is currently used in <command>COPY FROM</command> command when
-      <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>.
+      <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>
+      or <literal>set_null</literal>.
       </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 9c51384ab92..c3eb31556b8 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -456,12 +456,14 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
 				 parser_errposition(pstate, def->location)));
 
 	/*
-	 * Allow "stop", or "ignore" values.
+	 * Allow "stop", "ignore", "set_null" values.
 	 */
 	if (pg_strcasecmp(sval, "stop") == 0)
 		return COPY_ON_ERROR_STOP;
 	if (pg_strcasecmp(sval, "ignore") == 0)
 		return COPY_ON_ERROR_IGNORE;
+	if (pg_strcasecmp(sval, "set_null") == 0)
+		return COPY_ON_ERROR_SET_NULL;
 
 	ereport(ERROR,
 			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -971,7 +973,7 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
 
-	if (opts_out->reject_limit && !opts_out->on_error)
+	if (opts_out->reject_limit && opts_out->on_error != COPY_ON_ERROR_IGNORE)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 		/*- translator: first and second %s are the names of COPY option, e.g.
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 25ee20b23db..f54168fbb70 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -50,6 +50,7 @@
 #include "utils/portal.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
+#include "utils/typcache.h"
 
 /*
  * No more than this many tuples per CopyMultiInsertBuffer
@@ -1467,14 +1468,22 @@ CopyFrom(CopyFromState cstate)
 	/* Done, clean up */
 	error_context_stack = errcallback.previous;
 
-	if (cstate->opts.on_error != COPY_ON_ERROR_STOP &&
-		cstate->num_errors > 0 &&
+	if (cstate->num_errors > 0 &&
 		cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT)
-		ereport(NOTICE,
-				errmsg_plural("%" PRIu64 " row was skipped due to data type incompatibility",
-							  "%" PRIu64 " rows were skipped due to data type incompatibility",
-							  cstate->num_errors,
-							  cstate->num_errors));
+	{
+		if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+			ereport(NOTICE,
+					errmsg_plural("%" PRIu64 " row was skipped due to data type incompatibility",
+								  "%" PRIu64 " rows were skipped due to data type incompatibility",
+								  cstate->num_errors,
+								  cstate->num_errors));
+		else if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+			ereport(NOTICE,
+					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 data type incompatibility",
+								  cstate->num_errors,
+								  cstate->num_errors));
+	}
 
 	if (bistate != NULL)
 		FreeBulkInsertState(bistate);
@@ -1622,15 +1631,31 @@ BeginCopyFrom(ParseState *pstate,
 		cstate->escontext->error_occurred = false;
 
 		/*
-		 * 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
 		 */
-		if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+		if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE ||
+			cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
 			cstate->escontext->details_wanted = false;
 	}
 	else
 		cstate->escontext = NULL;
 
+	if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+	{
+		int			attr_count = list_length(cstate->attnumlist);
+
+		cstate->domain_with_constraint = (bool *) palloc0(attr_count * sizeof(bool));
+		foreach_int(attno, cstate->attnumlist)
+		{
+			int			i = foreach_current_index(attno);
+
+			Form_pg_attribute att = TupleDescAttr(tupDesc, attno - 1);
+
+			cstate->domain_with_constraint[i] = DomainHasConstraints(att->atttypid);
+		}
+	}
+
 	/* Convert FORCE_NULL name list to per-column flags, check validity */
 	cstate->opts.force_null_flags = (bool *) palloc0(num_phys_attrs * sizeof(bool));
 	if (cstate->opts.force_null_all)
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 5868a7fa11f..1d8a9dbb792 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -956,6 +956,7 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 	int			fldct;
 	int			fieldno;
 	char	   *string;
+	bool		current_row_erroneous = false;
 
 	tupDesc = RelationGetDescr(cstate->rel);
 	attr_count = list_length(cstate->attnumlist);
@@ -1033,7 +1034,8 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 		}
 
 		/*
-		 * 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.
 		 */
 		else if (!InputFunctionCallSafe(&in_functions[m],
 										string,
@@ -1044,7 +1046,51 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 		{
 			Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP);
 
-			cstate->num_errors++;
+			if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+				cstate->num_errors++;
+			else if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+			{
+				cstate->escontext->error_occurred = false;
+
+				Assert(cstate->domain_with_constraint != NULL);
+
+				/*
+				 * If the column type is a constrained domain, an additional
+				 * InputFunctionCallSafe may be needed to raise error for
+				 * domain constraint violation.
+				 */
+				if (!cstate->domain_with_constraint[m] ||
+					InputFunctionCallSafe(&in_functions[m],
+										  NULL,
+										  typioparams[m],
+										  att->atttypmod,
+										  (Node *) cstate->escontext,
+										  &values[m]))
+				{
+					nulls[m] = true;
+					values[m] = (Datum) 0;
+				}
+				else if (string == NULL)
+					ereport(ERROR,
+							errcode(ERRCODE_NOT_NULL_VIOLATION),
+							errmsg("domain %s does not allow null values", format_type_be(typioparams[m])),
+							errdatatype(typioparams[m]));
+				else
+					ereport(ERROR,
+							errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+							errmsg("invalid input value for domain %s: \"%s\"",
+								   format_type_be(typioparams[m]), string));
+
+				/*
+				 * We count only the number of rows (not fields) where
+				 * ON_ERROR SET_NULL was applied.
+				 */
+				if (!current_row_erroneous)
+				{
+					current_row_erroneous = true;
+					cstate->num_errors++;
+				}
+			}
 
 			if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
 			{
@@ -1061,24 +1107,37 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 					char	   *attval;
 
 					attval = CopyLimitPrintoutLength(cstate->cur_attval);
-					ereport(NOTICE,
-							errmsg("skipping row due to data type incompatibility at line %" PRIu64 " for column \"%s\": \"%s\"",
-								   cstate->cur_lineno,
-								   cstate->cur_attname,
-								   attval));
+
+					if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+						ereport(NOTICE,
+								errmsg("skipping row due to data type incompatibility at line %" PRIu64 " for column \"%s\": \"%s\"",
+									   cstate->cur_lineno,
+									   cstate->cur_attname,
+									   attval));
+					else if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+						ereport(NOTICE,
+								errmsg("setting to null due to data type incompatibility at line %" PRIu64 " for column \"%s\": \"%s\"",
+									   cstate->cur_lineno,
+									   cstate->cur_attname,
+									   attval));
 					pfree(attval);
 				}
 				else
-					ereport(NOTICE,
-							errmsg("skipping row due to data type incompatibility at line %" PRIu64 " for column \"%s\": null input",
-								   cstate->cur_lineno,
-								   cstate->cur_attname));
-
+				{
+					if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+						ereport(NOTICE,
+								errmsg("skipping row due to data type incompatibility at line %" PRIu64 " for column \"%s\": null input",
+									   cstate->cur_lineno,
+									   cstate->cur_attname));
+				}
 				/* reset relname_only */
 				cstate->relname_only = false;
 			}
 
-			return true;
+			if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+				return true;
+			else if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+				continue;
 		}
 
 		cstate->cur_attname = NULL;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8b91bc00062..e2c72bd3059 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3425,7 +3425,7 @@ match_previous_words(int pattern_id,
 
 			/* Complete COPY <sth> FROM filename WITH (ON_ERROR */
 			else if (TailMatches("ON_ERROR"))
-				COMPLETE_WITH("stop", "ignore");
+				COMPLETE_WITH("stop", "ignore", "set_null");
 
 			/* Complete COPY <sth> FROM filename WITH (LOG_VERBOSITY */
 			else if (TailMatches("LOG_VERBOSITY"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 43c2580539f..877202af67b 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -35,6 +35,7 @@ typedef enum CopyOnErrorChoice
 {
 	COPY_ON_ERROR_STOP = 0,		/* immediately throw errors, default */
 	COPY_ON_ERROR_IGNORE,		/* ignore errors */
+	COPY_ON_ERROR_SET_NULL,		/* set error field to null */
 } CopyOnErrorChoice;
 
 /*
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 822ef33cf69..f892c343157 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -108,6 +108,13 @@ typedef struct CopyFromStateData
 								 * att */
 	bool	   *defaults;		/* if DEFAULT marker was found for
 								 * corresponding att */
+
+	/*
+	 * True if the corresponding attribute's is a constrained domain. This
+	 * will be populated only when ON_ERROR is SET_NULL, otherwise NULL.
+	 */
+	bool	   *domain_with_constraint;
+
 	bool		volatile_defexprs;	/* is any of defexprs volatile? */
 	List	   *range_table;	/* single element list of RangeTblEntry */
 	List	   *rteperminfos;	/* single element list of RTEPermissionInfo */
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index f3fdce23459..2e1637f2134 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -81,6 +81,10 @@ COPY x from stdin (on_error ignore, on_error ignore);
 ERROR:  conflicting or redundant options
 LINE 1: COPY x from stdin (on_error ignore, on_error ignore);
                                             ^
+COPY x from stdin (on_error set_null, on_error set_null);
+ERROR:  conflicting or redundant options
+LINE 1: COPY x from stdin (on_error set_null, on_error set_null);
+                                              ^
 COPY x from stdin (log_verbosity default, log_verbosity verbose);
 ERROR:  conflicting or redundant options
 LINE 1: COPY x from stdin (log_verbosity default, log_verbosity verb...
@@ -92,6 +96,10 @@ COPY x from stdin (format BINARY, null 'x');
 ERROR:  cannot specify NULL in BINARY mode
 COPY x from stdin (format BINARY, on_error ignore);
 ERROR:  only ON_ERROR STOP is allowed in BINARY mode
+COPY x from stdin (format BINARY, on_error set_null);
+ERROR:  only ON_ERROR STOP is allowed in BINARY mode
+COPY x from stdin (on_error set_null, reject_limit 2);
+ERROR:  COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
 COPY x from stdin (on_error unsupported);
 ERROR:  COPY ON_ERROR "unsupported" not recognized
 LINE 1: COPY x from stdin (on_error unsupported);
@@ -124,6 +132,10 @@ COPY x to stdout (format BINARY, on_error unsupported);
 ERROR:  COPY ON_ERROR cannot be used with COPY TO
 LINE 1: COPY x to stdout (format BINARY, on_error unsupported);
                                          ^
+COPY x to stdout (on_error set_null);
+ERROR:  COPY ON_ERROR cannot be used with COPY TO
+LINE 1: COPY x to stdout (on_error set_null);
+                          ^
 COPY x from stdin (log_verbosity unsupported);
 ERROR:  COPY LOG_VERBOSITY "unsupported" not recognized
 LINE 1: COPY x from stdin (log_verbosity unsupported);
@@ -776,6 +788,46 @@ CONTEXT:  COPY check_ign_err
 NOTICE:  skipping row due to data type incompatibility at line 8 for column "k": "a"
 CONTEXT:  COPY check_ign_err
 NOTICE:  6 rows were skipped due to data type incompatibility
+CREATE DOMAIN d_int_not_null AS integer NOT NULL CHECK(value > 0);
+CREATE DOMAIN d_int_positive_maybe_null AS integer CHECK(value > 0);
+CREATE TABLE t_on_error_null (a d_int_not_null, b d_int_positive_maybe_null, c integer);
+\pset null NULL
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
+ERROR:  domain d_int_not_null does not allow null values
+CONTEXT:  COPY t_on_error_null, line 1, column a: null input
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
+ERROR:  invalid input value for domain d_int_not_null: "ss"
+CONTEXT:  COPY t_on_error_null, line 1, column a: "ss"
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
+ERROR:  invalid input value for domain d_int_not_null: "-1"
+CONTEXT:  COPY t_on_error_null, line 1, column a: "-1"
+--fail, less data.
+COPY t_on_error_null FROM STDIN WITH (delimiter ',', on_error set_null);
+ERROR:  missing data for column "c"
+CONTEXT:  COPY t_on_error_null, line 1: "1,1"
+--fail, extra data.
+COPY t_on_error_null FROM STDIN WITH (delimiter ',', on_error set_null);
+ERROR:  extra data after last expected column
+CONTEXT:  COPY t_on_error_null, line 1: "1,2,3,4"
+COPY t_on_error_null FROM STDIN WITH (on_error set_null, log_verbosity verbose); --ok
+NOTICE:  setting to null due to data type incompatibility at line 1 for column "b": "x1"
+CONTEXT:  COPY t_on_error_null
+NOTICE:  setting to null due to data type incompatibility at line 1 for column "c": "yx"
+CONTEXT:  COPY t_on_error_null
+NOTICE:  setting to null due to data type incompatibility at line 2 for column "b": "zx"
+CONTEXT:  COPY t_on_error_null
+NOTICE:  setting to null due to data type incompatibility at line 3 for column "c": "ea"
+CONTEXT:  COPY t_on_error_null
+NOTICE:  invalid values in 3 rows were replaced with null due to data type incompatibility
+SELECT * FROM t_on_error_null ORDER BY a;
+ a  |  b   |  c   
+----+------+------
+ 10 | NULL | NULL
+ 11 | NULL |   12
+ 13 |   14 | NULL
+(3 rows)
+
+\pset null ''
 -- tests for on_error option with log_verbosity and null constraint via domain
 CREATE DOMAIN dcheck_ign_err2 varchar(15) NOT NULL;
 CREATE TABLE check_ign_err2 (n int, m int[], k int, l dcheck_ign_err2);
@@ -835,6 +887,9 @@ DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
 DROP TABLE check_ign_err;
+DROP TABLE t_on_error_null;
+DROP DOMAIN d_int_not_null;
+DROP DOMAIN d_int_positive_maybe_null;
 DROP TABLE check_ign_err2;
 DROP DOMAIN dcheck_ign_err2;
 DROP TABLE hard_err;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index cef45868db5..132f67d6c11 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -67,12 +67,15 @@ COPY x from stdin (force_null (a), force_null (b));
 COPY x from stdin (convert_selectively (a), convert_selectively (b));
 COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
 COPY x from stdin (on_error ignore, on_error ignore);
+COPY x from stdin (on_error set_null, on_error set_null);
 COPY x from stdin (log_verbosity default, log_verbosity verbose);
 
 -- incorrect options
 COPY x from stdin (format BINARY, delimiter ',');
 COPY x from stdin (format BINARY, null 'x');
 COPY x from stdin (format BINARY, on_error ignore);
+COPY x from stdin (format BINARY, on_error set_null);
+COPY x from stdin (on_error set_null, reject_limit 2);
 COPY x from stdin (on_error unsupported);
 COPY x from stdin (format TEXT, force_quote(a));
 COPY x from stdin (format TEXT, force_quote *);
@@ -87,6 +90,7 @@ COPY x from stdin (format TEXT, force_null *);
 COPY x to stdout (format CSV, force_null(a));
 COPY x to stdout (format CSV, force_null *);
 COPY x to stdout (format BINARY, on_error unsupported);
+COPY x to stdout (on_error set_null);
 COPY x from stdin (log_verbosity unsupported);
 COPY x from stdin with (reject_limit 1);
 COPY x from stdin with (on_error ignore, reject_limit 0);
@@ -537,6 +541,42 @@ a	{2}	2
 8	{8}	8
 \.
 
+CREATE DOMAIN d_int_not_null AS integer NOT NULL CHECK(value > 0);
+CREATE DOMAIN d_int_positive_maybe_null AS integer CHECK(value > 0);
+CREATE TABLE t_on_error_null (a d_int_not_null, b d_int_positive_maybe_null, c integer);
+
+\pset null NULL
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
+\N	11	13
+\.
+
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
+ss	11	14
+\.
+
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
+-1	11	13
+\.
+
+--fail, less data.
+COPY t_on_error_null FROM STDIN WITH (delimiter ',', on_error set_null);
+1,1
+\.
+--fail, extra data.
+COPY t_on_error_null FROM STDIN WITH (delimiter ',', on_error set_null);
+1,2,3,4
+\.
+
+COPY t_on_error_null FROM STDIN WITH (on_error set_null, log_verbosity verbose); --ok
+10	x1	yx
+11	zx	12
+13	14	ea
+\.
+
+SELECT * FROM t_on_error_null ORDER BY a;
+
+\pset null ''
+
 -- tests for on_error option with log_verbosity and null constraint via domain
 CREATE DOMAIN dcheck_ign_err2 varchar(15) NOT NULL;
 CREATE TABLE check_ign_err2 (n int, m int[], k int, l dcheck_ign_err2);
@@ -606,6 +646,9 @@ DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
 DROP TABLE check_ign_err;
+DROP TABLE t_on_error_null;
+DROP DOMAIN d_int_not_null;
+DROP DOMAIN d_int_positive_maybe_null;
 DROP TABLE check_ign_err2;
 DROP DOMAIN dcheck_ign_err2;
 DROP TABLE hard_err;
-- 
2.34.1

---

From: "Matheus Alcantara" <[email-masked]>
Date: Wed, 21 Jan 2026 15:47:51 -0300

> sure. please check the attached.

Thanks for the new version. I have some comments on this first round of
review:

I think that we could remove the "invalid values in" to make it
consistency with the COPY_ON_ERROR_IGNORE NOTICE

----------

I think that we can use palloc_array?

----------

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=# create table t4(a int, b varchar(5));
CREATE TABLE

postgres=# 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 type incompatibility
COPY 5

postgres=# \pset null 'NULL'
Null display is "NULL".
postgres=# 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=# 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?

----------

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</literal>

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?

----------

I may have missing something, but we are still considering implementing
the REJECT_LIMIT + ON_ERROR set_null?

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

---

From: jian he <[email-masked]>
Date: Thu, 22 Jan 2026 22:45:10 +0800

> consistency with the COPY_ON_ERROR_IGNORE NOTICE
sure.

sure.

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/copy2.sql#n330

Am I missing something?

> 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 it.
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</literal>
       option.
      </para></entry>

> the REJECT_LIMIT + ON_ERROR set_null?
Possibly as a separate patch later.

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

---

From: Matheus Alcantara <[email-masked]>
Date: Tue, 27 Jan 2026 15:37:07 -0300

> https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/sql/copy2.sql#n330

Yeah, after some more thinking it seems ok to use both options 
together. I just found a bit strange when using integer columns. 
Consider this example:

cat data.csv
1,11
2,22
3,
4,44

postgres=# create table t(a int not null, b int);
CREATE TABLE

postgres=# copy t from 
'/Users/matheus/dev/pgdev/copy-on-error-set-null/data.csv' with 
(FORCE_NOT_NULL(b), format csv, delimiter ',',  ON_ERROR set_null);
NOTICE:  1 row was replaced with null due to data type incompatibility
COPY 4

postgres=# select * from t where b is null;
  a | b
---+---
  3 |
(1 row)

We are requiring a not null value on column b but we are still 
generating rows with null values on b.

The reasoning on this is that the row 3 would generate a "invalid 
input syntax for type integer" error and the ON_ERROR set_null fix 
this by inserting a NULL value. It make sense I think but I'm 
wondering if it could cause any confusion?

>         <literal>ignore</literal> is specified to the <literal>ON_ERROR</literal>

It looks good, I was thinking in something like this.

> Possibly as a separate patch later. 
Ok, good, thanks.

---

From: jian he <[email-masked]>
Date: Fri, 6 Feb 2026 11:40:02 +0800

> wondering if it could cause any confusion?

After careful reading the FORCE_NOT_NULL, FORCE_NULL option.
It's about dealing with empty value and NULL strings.

copy t from stdin with(FORCE_NOT_NULL (b), format csv, delimiter ',');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1,
ERROR:  invalid input syntax for type integer: ""
CONTEXT:  COPY t, line 1, column b: ""

in this case, FORCE_NOT_NULL will convert the empty value to null
string (empty double quote)

another FORCE_NULL.
copy t from stdin with(FORCE_NULL (b), format csv, delimiter ',');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 1,""
COPY 1
src9=# table  t;
 a | b
---+---
 1 |
(1 row)

In this case, FORCE_NULL will convert null string (empty double quote) to NULL.

ON_ERROR explanation, the first sentence:
"""
Specifies how to behave when encountering an error converting a
column's input value into its data type.
"""
FORCE_NULL, FORCE_NOT_NULL is a special handling of input value, ON_ERROR is
about converting the input value to data type, so it's before ON_ERROR.

Overall the current doc is fine, IMHO.

The attached patch has addressed your other points.

--
jian
https://www.enterprisedb.com/
From b479c57753b51a868d5d19080a070c7aed9026de Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Thu, 5 Feb 2026 16:09:59 +0800
Subject: [PATCH v22 1/1] COPY (on_error set_null)

Reviewed-by:
Matheus Alcantara <[email-masked]>

discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=[email-masked]
commitfest entry: https://commitfest.postgresql.org/patch/4810
---
 doc/src/sgml/monitoring.sgml             |  4 +-
 doc/src/sgml/ref/copy.sgml               | 35 +++++++---
 src/backend/commands/copy.c              |  6 +-
 src/backend/commands/copyfrom.c          | 46 ++++++++++---
 src/backend/commands/copyfromparse.c     | 85 ++++++++++++++++++++----
 src/bin/psql/tab-complete.in.c           |  2 +-
 src/include/commands/copy.h              |  1 +
 src/include/commands/copyfrom_internal.h |  7 ++
 src/test/regress/expected/copy2.out      | 55 +++++++++++++++
 src/test/regress/sql/copy2.sql           | 43 ++++++++++++
 10 files changed, 247 insertions(+), 37 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b77d189a500..dcf6e6a2f48 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6266,8 +6266,8 @@ FROM pg_stat_get_backend_idset() AS backendid;
       </para>
       <para>
        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</literal>
+       This counter only advances when
+       <literal>ignore</literal> is specified to the <literal>ON_ERROR</literal>
        option.
       </para></entry>
      </row>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index afea9caf5ad..98c0e2924ea 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -432,22 +432,38 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       input value into its data type.
       An <replaceable class="parameter">error_action</replaceable> value of
       <literal>stop</literal> means fail the command, while
-      <literal>ignore</literal> means discard the input row and continue with the next one.
+      <literal>ignore</literal> means discard the input row and continue with the next one,
+      and <literal>set_null</literal> means replace field containing invalid
+      input value with <literal>NULL</literal> and continue to the next field.
       The default is <literal>stop</literal>.
      </para>
      <para>
-      The <literal>ignore</literal> option is applicable only for <command>COPY FROM</command>
+      The <literal>ignore</literal> and <literal>set_null</literal>
+      options are applicable only for <command>COPY FROM</command>
       when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
      </para>
+
+    <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>
+
      <para>
-      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. When <literal>LOG_VERBOSITY</literal> option is set to
-      <literal>verbose</literal>, a <literal>NOTICE</literal> message
+      When <literal>LOG_VERBOSITY</literal> option is set to <literal>verbose</literal>,
+      for <literal>ignore</literal> option, a <literal>NOTICE</literal> message
       containing the line of the input file and the column name whose input
-      conversion has failed is emitted for each discarded row.
+      conversion has failed is emitted for each discarded row;
+      for <literal>set_null</literal> option, a <literal>NOTICE</literal>
+      message containing the line of the input file and the column name where
+      value was replaced with <literal>NULL</literal> for each input conversion
+      failure.
       When it is set to <literal>silent</literal>, no message is emitted
-      regarding ignored rows.
+      regarding input conversion failed rows.
      </para>
     </listitem>
    </varlistentry>
@@ -495,7 +511,8 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
      </para>
      <para>
       This is currently used in <command>COPY FROM</command> command when
-      <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>.
+      <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>
+      or <literal>set_null</literal>.
       </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 5d9b08b822c..6263767a903 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -469,12 +469,14 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
 				 parser_errposition(pstate, def->location)));
 
 	/*
 
 	ereport(ERROR,
 			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -1035,7 +1037,7 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
 
index 0c73b0259c3..cc4a2dab348 100644
 
 /*
 
+					errmsg_plural("%" PRIu64 " row was replaced with null due to data type incompatibility",
+								  "%" PRIu64 " rows were replaced with null due to data type incompatibility",
+								  cstate->num_errors,
+								  cstate->num_errors));
+	}
 
 	if (bistate != NULL)
 		FreeBulkInsertState(bistate);
@@ -1622,15 +1631,32 @@ BeginCopyFrom(ParseState *pstate,
 		cstate->escontext->error_occurred = false;
 
 		/*
 
+		cstate->domain_with_constraint = palloc0_array(bool, attr_count);
index 0d9199f5ce8..e12decaa81a 100644
@@ -959,6 +959,7 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 
 	tupDesc = RelationGetDescr(cstate->rel);
 	attr_count = list_length(cstate->attnumlist);
@@ -1036,7 +1037,8 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 		}
 
 		/*
@@ -1047,7 +1049,51 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 
-			cstate->num_errors++;
 
 			if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
 			{
@@ -1064,24 +1110,37 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 					char	   *attval;

-			return true;
 
index a610ef603f4..6810a6d04e6 100644

index b2a68fa6407..abecfe51098 100644
 
 /*
index 3145b314e48..72034796aca 100644
@@ -782,6 +794,46 @@ CONTEXT:  COPY check_ign_err
+NOTICE:  3 rows were replaced with null due to data type incompatibility
@@ -841,6 +893,9 @@ DROP VIEW instead_of_insert_tbl_view;
index 66435167500..5e9f6af2c5c 100644
 
 -- incorrect options
@@ -540,6 +544,42 @@ a	{2}	2
 8	{8}	8
 \.
 
@@ -609,6 +649,9 @@ DROP VIEW instead_of_insert_tbl_view;

---

From: "Matheus Alcantara" <[email-masked]>
Date: Fri, 06 Feb 2026 09:58:43 -0300

> Overall the current doc is fine, IMHO.
Yeah, after also reading more carefully it's sounds correct to me too.

> The attached patch has addressed your other points.
Thanks, overall the patch looks good to me. I'm attaching a diff with
just some small tweaks on documentation and error messages. Please see
and check if it's make sense.

I'm wondering if we should have an else if block on
CopyFromTextLikeOneRow() when cstate->cur_attval is NULL to handle
COPY_ON_ERROR_SET_NULL when log_verbosity is set to
COPY_LOG_VERBOSITY_VERBOSE

if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
    ereport(NOTICE,
            errmsg("skipping row due to data type incompatibility at line %" PRIu64 " for column \"%s\": null input",
                   cstate->cur_lineno,
                   cstate->cur_attname));
+ else if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+     ereport(NOTICE,
+             errmsg("setting to null due to data type incompatibility at line %" PRIu64 " for column \"%s\": null input",
+                    cstate->cur_lineno,
+                    cstate->cur_attname));

---

From: "Matheus Alcantara" <[email-masked]>
Date: Fri, 06 Feb 2026 10:00:36 -0300

> and check if it's make sense.
I miss to include the diff file, sorry about that.

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index dcf6e6a2f48..2aeb38a6e5f 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6265,10 +6265,13 @@ FROM pg_stat_get_backend_idset() AS backendid;
        <structfield>tuples_skipped</structfield> <type>bigint</type>
       </para>
       <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</literal>
-       option.
+       Number of tuples that contained malformed data. When
+       <literal>ON_ERROR</literal> is set to <literal>ignore</literal>,
+       this counts rows that were skipped. When set to
+       <literal>set_null</literal>, this counts rows where at least one
+       column was set to null due to a conversion error.
+       This counter only advances when <literal>ON_ERROR</literal>
+       is set to <literal>ignore</literal> or <literal>set_null</literal>.
       </para></entry>
      </row>
     </tbody>
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 96ba23e961c..0a75800b8ab 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -1052,6 +1052,11 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 				cstate->num_errors++;
 			else if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
 			{
+				/*
+				 * Reset error state so the subsequent InputFunctionCallSafe
+				 * call (for domain constraint check) can properly report
+				 * whether it succeeded or failed.
+				 */
 				cstate->escontext->error_occurred = false;
 
 				Assert(cstate->domain_with_constraint != NULL);
@@ -1075,13 +1080,17 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 				else if (string == NULL)
 					ereport(ERROR,
 							errcode(ERRCODE_NOT_NULL_VIOLATION),
-							errmsg("domain %s does not allow null values", format_type_be(typioparams[m])),
+							errmsg("null value in column \"%s\" violates not-null constraint of domain %s",
+								   cstate->cur_attname, format_type_be(typioparams[m])),
 							errdatatype(typioparams[m]));
 				else
 					ereport(ERROR,
-							errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
-							errmsg("invalid input value for domain %s: \"%s\"",
-								   format_type_be(typioparams[m]), string));
+							errcode(ERRCODE_NOT_NULL_VIOLATION),
+							errmsg("cannot set null value for column \"%s\" with domain %s",
+								   cstate->cur_attname, format_type_be(typioparams[m])),
+							errdetail("Column \"%s\" does not accept null values, so ON_ERROR SET_NULL cannot be applied.",
+									  cstate->cur_attname),
+							errdatatype(typioparams[m]));
 
 				/*
 				 * We count only the number of rows (not fields) where
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 72034796aca..d9cc7bf5f48 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -799,13 +799,15 @@ CREATE DOMAIN d_int_positive_maybe_null AS integer CHECK(value > 0);
 CREATE TABLE t_on_error_null (a d_int_not_null, b d_int_positive_maybe_null, c integer);
 \pset null NULL
 COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
-ERROR:  domain d_int_not_null does not allow null values
+ERROR:  null value in column "a" violates not-null constraint of domain d_int_not_null
 CONTEXT:  COPY t_on_error_null, line 1, column a: null input
 COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
-ERROR:  invalid input value for domain d_int_not_null: "ss"
+ERROR:  cannot set null value for column "a" with domain d_int_not_null
+DETAIL:  Column "a" does not accept null values, so ON_ERROR SET_NULL cannot be applied.
 CONTEXT:  COPY t_on_error_null, line 1, column a: "ss"
 COPY t_on_error_null FROM STDIN WITH (on_error set_null); --fail
-ERROR:  invalid input value for domain d_int_not_null: "-1"
+ERROR:  cannot set null value for column "a" with domain d_int_not_null
+DETAIL:  Column "a" does not accept null values, so ON_ERROR SET_NULL cannot be applied.
 CONTEXT:  COPY t_on_error_null, line 1, column a: "-1"
 --fail, less data.
 COPY t_on_error_null FROM STDIN WITH (delimiter ',', on_error set_null);

---

From: jian he <[email-masked]>
Date: Mon, 9 Feb 2026 11:59:46 +0800

> and check if it's make sense.
In the function CopyFrom, we have:
        if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE &&
            cstate->escontext->error_occurred)
        {
            cstate->escontext->error_occurred = false;
            pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
                                         cstate->num_errors);

That means PROGRESS_COPY_TUPLES_SKIPPED applied for COPY_ON_ERROR_IGNORE only.
So
      <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</literal>
       option.
      </para></entry>
should be ok.

> +             errmsg("setting to null due to data type incompatibility at line %" PRIu64 " for column \"%s\": null input",

CopyFromTextLikeOneRow, we have:
        cstate->cur_attname = NameStr(att->attname);
        cstate->cur_attval = string;

even if "string" is NULL (two InputFunctionCallSafe function call with
"str" value as NULL), it will fail at
```
                else if (string == NULL)
                    ereport(ERROR,
                            errcode(ERRCODE_NOT_NULL_VIOLATION),
                            errmsg("null value in column \"%s\"
violates not-null constraint of domain %s",
                                   cstate->cur_attname,
format_type_be(typioparams[m])),
                            errdatatype(typioparams[m]));
```
so i think condition like:
if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE &&
    cstate->cur_attval == NULL &&
    cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
is not reachable.
therefore I didn't add the ELSE IF block.

inspired by your change, I further simplified the error handling code.

--
jian
https://www.enterprisedb.com/
From 114eeb0338510b6c55d8b0c7657242c6a275eb8d Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Sun, 8 Feb 2026 23:47:03 +0800
Subject: [PATCH v23 1/1] COPY (on_error set_null)

Reviewed-by:

discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=[email-masked]
commitfest entry: https://commitfest.postgresql.org/patch/4810
---
 doc/src/sgml/monitoring.sgml             |  4 +-
 doc/src/sgml/ref/copy.sgml               | 35 +++++++---
 src/backend/commands/copy.c              |  6 +-
 src/backend/commands/copyfrom.c          | 46 +++++++++---
 src/backend/commands/copyfromparse.c     | 89 ++++++++++++++++++++----
 src/bin/psql/tab-complete.in.c           |  2 +-
 src/include/commands/copy.h              |  1 +
 src/include/commands/copyfrom_internal.h |  7 ++
 src/test/regress/expected/copy2.out      | 58 +++++++++++++++
 src/test/regress/sql/copy2.sql           | 43 ++++++++++++
 10 files changed, 254 insertions(+), 37 deletions(-)

index 53b0ea8f573..acc808dbb6e 100644
index 155a79a70c5..6de0b643da1 100644
 
 	/*
 
 	ereport(ERROR,
 			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -984,7 +986,7 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
 
index 25ee20b23db..1213f3d8832 100644
 
 /*

 	if (bistate != NULL)
 
 		/*
 
index 94d6f415a06..c34dd7fcd72 100644
@@ -958,6 +958,7 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 
 	tupDesc = RelationGetDescr(cstate->rel);
 	attr_count = list_length(cstate->attnumlist);
@@ -1035,7 +1036,8 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 		}
 
 		/*
@@ -1046,7 +1048,55 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 
-			cstate->num_errors++;
+							errmsg("domain %s does not allow null values",
+								   format_type_be(typioparams[m])),
+							errdetail("ON_ERROR SET_NULL cannot be applied because column \"%s\" (domain %s) does not accept null values.",
+									  format_type_be(typioparams[m])),
 
 			if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
 			{
@@ -1063,24 +1113,37 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 					char	   *attval;

-			return true;

 
 
 /*
index 3145b314e48..045c0f81243 100644
@@ -782,6 +794,49 @@ CONTEXT:  COPY check_ign_err
+DETAIL:  ON_ERROR SET_NULL cannot be applied because column "a" (domain d_int_not_null) does not accept null values.
+DETAIL:  ON_ERROR SET_NULL cannot be applied because column "a" (domain d_int_not_null) does not accept null values.
+DETAIL:  ON_ERROR SET_NULL cannot be applied because column "a" (domain d_int_not_null) does not accept null values.
@@ -841,6 +896,9 @@ DROP VIEW instead_of_insert_tbl_view;
 
 -- incorrect options

---

From: Matheus Alcantara <[email-masked]>
Date: Mon, 9 Feb 2026 12:36:55 -0300

>         <literal>ignore</literal> is specified to the <literal>ON_ERROR</literal>
Ok, agree.

> therefore I didn't add the ELSE IF block.
Ok, make sense. I've tested and it seems correct.

> inspired by your change, I further simplified the error handling code.
Thanks for the new version. It looks good to me. I don't have any 
other comments.

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 25 Feb 2026 08:36:20 +0100

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

     /*

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.

         /*

Delete this comment.

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

- src/backend/commands/copyfromparse.c

         /*

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.

+           /*

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 (...)".

---

From: jian he <[email-masked]>
Date: Sat, 28 Feb 2026 10:04:53 +0800

> information about domains for later.

Here is what I came up with:

+        /*
+         * When data type conversion fails and ON_ERROR is SET_NULL, we need
+         * ensure that input column allows NULL value, ExecConstraints will
+         * cover most of the cases, however it does not vertify domain
+         * constraints. Therefore, for constrained domains, NULL value check
+         * must be performed during the initial string-to-datum conversion
+         * (see CopyFromTextLikeOneRow).
+         */

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

I changed the comments to:

+                /*
+                 * For constrained domain types, we need an additional
+                 * InputFunctionCallSafe to ensure that an error is thrown if
+                 * the domain constraint rejects NULL.
+                 */

> constraint failure?  How would that be handled?  The flow here is a bit

create domain d3 as int check (value is not null);
create table t(a d3);
src4=# copy t1 from stdin (on_error set_null);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> \N
ERROR:  domain d1 does not allow null values
DETAIL:  ON_ERROR SET_NULL cannot be applied because column "a"
(domain d1) does not accept null values.
CONTEXT:  COPY t1, line 1, column a: null input

It's more about whether all domain constraints allow a NULL value,
In this context, the domain constraint is a CHECK constraint.

``InputFunctionCallSafe(&in_functions[m], NULL,``
this check whether a NULL value is allowed for this domain.
ExecConstraints does not handle domain constraints, so this is needed.

The error message:
``errmsg("domain %s does not allow null values",``
should be fine?

All other suggestions have been incorporated into v24.

--
jian
https://www.enterprisedb.com/
From 61d2a81c50ac1691b4a328ae5a8fc24254f23c8d Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Sat, 28 Feb 2026 09:57:42 +0800
Subject: [PATCH v24 1/1] COPY (on_error set_null)

Reviewed-by:
Peter Eisentraut <[email-masked]>

discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=[email-masked]
commitfest entry: https://commitfest.postgresql.org/patch/4810
---
 doc/src/sgml/monitoring.sgml             |  4 +-
 doc/src/sgml/ref/copy.sgml               | 28 +++++---
 src/backend/commands/copy.c              |  7 +-
 src/backend/commands/copyfrom.c          | 54 ++++++++++----
 src/backend/commands/copyfromparse.c     | 90 ++++++++++++++++++++----
 src/bin/psql/tab-complete.in.c           |  2 +-
 src/include/commands/copy.h              |  1 +
 src/include/commands/copyfrom_internal.h |  7 ++
 src/test/regress/expected/copy2.out      | 58 +++++++++++++++
 src/test/regress/sql/copy2.sql           | 43 +++++++++++
 10 files changed, 251 insertions(+), 43 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b77d189a500..dcf6e6a2f48 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6266,8 +6266,8 @@ FROM pg_stat_get_backend_idset() AS backendid;
       </para>
       <para>
        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</literal>
+       This counter only advances when
+       <literal>ignore</literal> is specified to the <literal>ON_ERROR</literal>
        option.
       </para></entry>
      </row>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 53b0ea8f573..b7270848bab 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -413,22 +413,31 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       input value into its data type.
       An <replaceable class="parameter">error_action</replaceable> value of
       <literal>stop</literal> means fail the command, while
-      <literal>ignore</literal> means discard the input row and continue with the next one.
+      <literal>ignore</literal> means discard the input row and continue with the next one,
+      and <literal>set_null</literal> means replace the field containing the invalid
+      input value with a null value and continue to the next field.
       The default is <literal>stop</literal>.
      </para>
      <para>
-      The <literal>ignore</literal> option is applicable only for <command>COPY FROM</command>
+      The <literal>ignore</literal> and <literal>set_null</literal>
+      options are applicable only for <command>COPY FROM</command>
       when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
+      If <literal>ON_ERROR</literal> is set to <literal>ignore</literal> or
+      <literal>set_null</literal>, 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.
      </para>
      <para>
-      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. When <literal>LOG_VERBOSITY</literal> option is set to
-      <literal>verbose</literal>, a <literal>NOTICE</literal> message
+      When <literal>LOG_VERBOSITY</literal> option is set to <literal>verbose</literal>,
+      for <literal>ignore</literal> option, a <literal>NOTICE</literal> message
       containing the line of the input file and the column name whose input
-      conversion has failed is emitted for each discarded row.
+      conversion has failed is emitted for each discarded row;
+      for <literal>set_null</literal> option, a <literal>NOTICE</literal>
+      message containing the line of the input file and the column name where
+      value was replaced with <literal>NULL</literal> for each input conversion
+      failure.
       When it is set to <literal>silent</literal>, no message is emitted
-      regarding ignored rows.
+      regarding input conversion failed rows.
      </para>
     </listitem>
    </varlistentry>
@@ -476,7 +485,8 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
      </para>
      <para>
       This is currently used in <command>COPY FROM</command> command when
-      <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>.
+      <literal>ON_ERROR</literal> option is set to <literal>ignore</literal>
+      or <literal>set_null</literal>.
       </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 155a79a70c5..63b86802ba2 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -468,13 +468,12 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
 				 errmsg("COPY %s cannot be used with %s", "ON_ERROR", "COPY TO"),
 				 parser_errposition(pstate, def->location)));
 
-	/*
-	 */
 
 	ereport(ERROR,
 			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -984,7 +983,7 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
 
index 2b7556b287c..607bab3fc7c 100644
 
 /*
  * No more than this many tuples per CopyMultiInsertBuffer
@@ -1463,14 +1464,22 @@ CopyFrom(CopyFromState cstate)
 	/* Done, clean up */
 	error_context_stack = errcallback.previous;
 
+					errmsg_plural("in %" PRIu64 " row, columns was set to null due to data type incompatibility",
+								  "in %" PRIu64 " rows, columns were set to null due to data type incompatibility",
+								  cstate->num_errors,
+								  cstate->num_errors));
+	}
 
 	if (bistate != NULL)
 		FreeBulkInsertState(bistate);
@@ -1617,16 +1626,37 @@ BeginCopyFrom(ParseState *pstate,
 		cstate->escontext->type = T_ErrorSaveContext;
 		cstate->escontext->error_occurred = false;
 
-		/*
-		 */
 
+	if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+	{
+		int			attr_count = list_length(cstate->attnumlist);
+
+		/*
+		 * When data type conversion fails and ON_ERROR is SET_NULL, we need
+		 * ensure that input column allows NULL value, ExecConstraints will
+		 * cover most of the cases, however it does not vertify domain
+		 * constraints. Therefore, for constrained domains, NULL value check
+		 * must be performed during the initial string-to-datum conversion
+		 * (see CopyFromTextLikeOneRow).
+		 */
+		cstate->domain_with_constraint = palloc0_array(bool, attr_count);
+
+		foreach_int(attno, cstate->attnumlist)
+		{
+			int			i = foreach_current_index(attno);
+
+			Form_pg_attribute att = TupleDescAttr(tupDesc, attno - 1);
+
+			cstate->domain_with_constraint[i] = DomainHasConstraints(att->atttypid);
+		}
+	}
+
 	/* Convert FORCE_NULL name list to per-column flags, check validity */
 	cstate->opts.force_null_flags = (bool *) palloc0(num_phys_attrs * sizeof(bool));
 	if (cstate->opts.force_null_all)
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 6b00d49c50f..670b188d9a0 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -959,6 +959,7 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 	int			fldct;
 	int			fieldno;
 	char	   *string;
+	bool		current_row_erroneous = false;
 
 	tupDesc = RelationGetDescr(cstate->rel);
 	attr_count = list_length(cstate->attnumlist);
@@ -1035,9 +1036,7 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 			values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
 		}
 
-		/*
-		 * If ON_ERROR is specified with IGNORE, skip rows with soft errors
-		 */
+		/* If ON_ERROR is specified, handle the different options */
 		else if (!InputFunctionCallSafe(&in_functions[m],
 										string,
 										typioparams[m],
@@ -1047,7 +1046,55 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 		{
 			Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP);
 
-			cstate->num_errors++;
+				 * For constrained domain types, we need an additional
+				 * InputFunctionCallSafe to ensure that an error is thrown if
+				 * the domain constraint rejects NULL.
 
 			if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
 			{
@@ -1064,24 +1111,37 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext,
 					char	   *attval;

-			return true;
 
index b2dba6d10ab..61c08241b04 100644

 
 /*
index 3145b314e48..1dd9a63bb80 100644
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); -- fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); -- fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); -- fail
+-- fail, less data.
+-- fail, extra data.
+COPY t_on_error_null FROM STDIN WITH (on_error set_null, log_verbosity verbose); -- ok
+NOTICE:  in 3 rows, columns were set to null due to data type incompatibility
index 66435167500..d3b427857ab 100644
 
 -- incorrect options
 
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); -- fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); -- fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_null); -- fail
+-- fail, less data.
+-- fail, extra data.
+COPY t_on_error_null FROM STDIN WITH (on_error set_null, log_verbosity verbose); -- ok

---

From: Fujii Masao <[email-masked]>
Date: Wed, 4 Mar 2026 22:25:45 +0900

On Tue, Mar 3, 2026 at 3:38 PM Peter Eisentraut <[email-masked]> wrote:
> Thanks, committed.

Thanks for committing the patch!

With this change, ON_ERROR = 'set_null' can now be used with foreign tables
backed by file_fdw. However, unlike ON_ERROR = 'ignore', there is currently
no regression test covering this behavior in file_fdw.

How about adding a regression test to ensure that file_fdw works correctly
with ON_ERROR = 'set_null', and to improve test coverage? Patch attached.

Regards,

-- 
Fujii Masao

---

From: Fujii Masao <[email-masked]>
Date: Fri, 13 Mar 2026 11:57:13 +0900

> with ON_ERROR = 'set_null', and to improve test coverage? Patch attached.

Barring any objections, I will commit the patch.

Regards,

-- 
Fujii Masao

---

From: Fujii Masao <[email-masked]>
Date: Fri, 13 Mar 2026 22:50:07 +0900

On Fri, Mar 13, 2026 at 2:51 PM Yi Ding <[email-masked]> wrote:
> The new test added in v1 makes sense to me. A small suggestion is that to verify if a field is really null, we can do:
>
> ALTER FOREIGN TABLE agg_bad OPTIONS (ADD on_error 'set_null');
> SELECT a, b IS NULL FROM agg_bad;

Since the file_fdw test runs "\pset null _null_", a NULL value is displayed as
"_null_". So you can verify that the value is NULL by checking whether
"_null_" is shown.

One could argue that this cannot be distinguished from the literal text value
"_null_". However, relying on "\pset null _null_" is sufficient for this test,
I think.

Regards,

-- 
Fujii Masao

---

From: Fujii Masao <[email-masked]>
Date: Mon, 16 Mar 2026 12:15:16 +0900

On Mon, Mar 16, 2026 at 11:39 AM Yi Ding <[email-masked]> wrote:
>
>
> At 2026-03-13 21:50:07, "Fujii Masao" <[email-masked]> wrote:
> Sounds reasonable,that addressed my comment.

I've pushed the patch. Thanks!

Regards,

-- 
Fujii Masao