v8-0001-Introduce-COPY-option-to-replace-columns-containi.patch

From df39b38589fc8384e0590d76f8b0e2e4295f6f50 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 12 Sep 2024 17:07:02 +0800
Subject: [PATCH v8] Introduce COPY option to replace columns containing
 erroneous data with null

extent "on_error action", introduce new option: on_error set_to_null.

Due to current grammar, we cannot use "on_error null",
so I choose on_error set_to_null.

any data type conversion errors while the COPY FROM process will set that column value to be NULL.
this will only work with COPY FROM and non-binary format.

However this will respect the not-null constraint, meaning, if you actually converted error to null,
but the column has not-null constraint, not-null constraint violation ERROR will be raised.

discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com
---
 doc/src/sgml/ref/copy.sgml           |   9 +--
 src/backend/commands/copy.c          |  12 ++--
 src/backend/commands/copyfrom.c      |  53 +++++++++++---
 src/backend/commands/copyfromparse.c |  69 +++++++++++++++++-
 src/bin/psql/tab-complete.in.c       |   2 +-
 src/include/commands/copy.h          |   1 +
 src/test/regress/expected/copy2.out  | 100 ++++++++++++++++++++++++++-
 src/test/regress/sql/copy2.sql       |  82 ++++++++++++++++++++++
 8 files changed, 303 insertions(+), 25 deletions(-)

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 8394402f096..bce10ea62e5 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -394,12 +394,13 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       Specifies how to behave when encountering an error converting a column's
       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>stop</literal> means fail the command,
+      <literal>ignore</literal> means discard the input row and continue with the next one, and
+      <literal>set_to_null</literal> means replace columns containing erroneous input values with <literal>null</literal> and move to the next row.
       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_to_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>
@@ -422,7 +423,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
       Specifies the maximum number of errors tolerated while converting a
       column's input value to its data type, when <literal>ON_ERROR</literal> is
       set to <literal>ignore</literal>.
-      If the input causes more errors than the specified value, the <command>COPY</command>
+      If the input contains more erroneous rows than the specified value, the <command>COPY</command>
       command fails, even with <literal>ON_ERROR</literal> set to <literal>ignore</literal>.
       This clause must be used with <literal>ON_ERROR</literal>=<literal>ignore</literal>
       and <replaceable class="parameter">maxerror</replaceable> must be positive <type>bigint</type>.
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 3485ba8663f..304022cd867 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -403,12 +403,14 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
 				 parser_errposition(pstate, def->location)));
 
 	/*
-	 * Allow "stop", or "ignore" values.
+	 * Allow "stop", "ignore", "set_to_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_to_null") == 0)
+		return COPY_ON_ERROR_NULL;
 
 	ereport(ERROR,
 			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -904,13 +906,13 @@ 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_NULL || 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.
-		 * ON_ERROR, third is the value of the COPY option, e.g. IGNORE */
-				 errmsg("COPY %s requires %s to be set to %s",
-						"REJECT_LIMIT", "ON_ERROR", "IGNORE")));
+		 * ON_ERROR, third is the value of the COPY option, e.g. IGNORE or SET_TO_NULL */
+				 errmsg("COPY %s requires %s to be set to %s or %s",
+						"REJECT_LIMIT", "ON_ERROR", "IGNORE", "SET_TO_NULL")));
 }
 
 /*
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 754cb496169..557b00266ff 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1003,7 +1003,7 @@ CopyFrom(CopyFromState cstate)
 		if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
 			break;
 
-		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_NULL) &&
 			cstate->escontext->error_occurred)
 		{
 			/*
@@ -1018,12 +1018,30 @@ CopyFrom(CopyFromState cstate)
 			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
 										 cstate->num_errors);
 
-			if (cstate->opts.reject_limit > 0 &&
-				cstate->num_errors > cstate->opts.reject_limit)
-				ereport(ERROR,
-						(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
-						 errmsg("skipped more than REJECT_LIMIT (%lld) rows due to data type incompatibility",
-								(long long) cstate->opts.reject_limit)));
+			if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+			{
+				if (cstate->opts.reject_limit > 0 &&
+					cstate->num_errors > cstate->opts.reject_limit)
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+							errmsg("skipped more than REJECT_LIMIT (%lld) rows due to data type incompatibility",
+									(long long) cstate->opts.reject_limit)));
+			}
+			else
+			{
+				/* Provide different error msg if reject_limit is zero */
+				if (cstate->opts.reject_limit == 0)
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+							errmsg("failed to replace column containing erroneous data with null",
+									(long long) cstate->opts.reject_limit),
+							errhint("Consider specifying the REJECT_LIMIT option to skip erroneous rows.")));
+				else if (cstate->num_errors > cstate->opts.reject_limit)
+					ereport(ERROR,
+							(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+							errmsg("encountered more than REJECT_LIMIT (%lld) rows with data type incompatibility",
+									(long long) cstate->opts.reject_limit)));
+			}
 
 			/* Repeat NextCopyFrom() until no soft error occurs */
 			continue;
@@ -1321,7 +1339,7 @@ CopyFrom(CopyFromState cstate)
 	/* Done, clean up */
 	error_context_stack = errcallback.previous;
 
-	if (cstate->opts.on_error != COPY_ON_ERROR_STOP &&
+	if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE &&
 		cstate->num_errors > 0 &&
 		cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT)
 		ereport(NOTICE,
@@ -1330,6 +1348,18 @@ CopyFrom(CopyFromState cstate)
 							  (unsigned long long) cstate->num_errors,
 							  (unsigned long long) cstate->num_errors));
 
+	/* In case on_error SET_TO_NULL, if COPY succeed, it means that
+	 * all erroneous rows attributes filled with NULL
+	 */
+	if (cstate->opts.on_error == COPY_ON_ERROR_NULL &&
+		cstate->num_errors > 0 &&
+		cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT)
+		ereport(NOTICE,
+				errmsg_plural("Erroneous values in %llu row were replaced with NULL",
+							  "Erroneous values in %llu rows were replaced with NULL",
+							  (unsigned long long) cstate->num_errors,
+							  (unsigned long long) cstate->num_errors));
+
 	if (bistate != NULL)
 		FreeBulkInsertState(bistate);
 
@@ -1474,10 +1504,11 @@ 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_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_NULL)
 			cstate->escontext->details_wanted = false;
 	}
 	else
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index d1d43b53d83..943926ad1d9 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -871,6 +871,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		int			fldct;
 		int			fieldno;
 		char	   *string;
+		bool		current_row_erroneous = false;
 
 		/* read raw fields in the next line */
 		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
@@ -949,7 +950,8 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
 			/*
 			 * If ON_ERROR is specified with IGNORE, skip rows with soft
-			 * errors
+			 * errors. If ON_ERROR is specified with SET_TO_NULL, try
+			 * to replace attribute value with NULL.
 			 */
 			else if (!InputFunctionCallSafe(&in_functions[m],
 											string,
@@ -960,9 +962,63 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 			{
 				Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP);
 
-				cstate->num_errors++;
+				/*
+				* Regardless of NULL substrition success, we count
+				* current row as erroneous
+				*/
+				current_row_erroneous = true;
 
-				if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+				/*
+				* We encountered an error while parsing one of attributes.
+				*
+				*/
+				if (cstate->opts.on_error == COPY_ON_ERROR_NULL && string != NULL)
+				{
+					/*
+					* Temporary unset error_occurred, for next InputFunctionCallSafe
+					* sanity. If null substitution for this attribute will
+					* succeed, we will accept this row.
+					*/
+					cstate->escontext->error_occurred = false;
+
+					if (InputFunctionCallSafe(&in_functions[m],
+											NULL,
+											typioparams[m],
+											att->atttypmod,
+											(Node *) cstate->escontext,
+											&values[m]))
+					{
+						/* If datatype if okay with NULL, replace
+						* with null
+						*/
+						nulls[m] = true;
+
+						if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+							ereport(NOTICE,
+									errmsg("replaced row attribute \"%s\" with NULL due to data type incompatibility at line %llu.",
+										cstate->cur_attname, (unsigned long long) cstate->cur_lineno));
+						continue;
+					}
+
+					if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+						ereport(NOTICE,
+								errmsg("failed to replace row attribute \"%s\" with NULL at line %llu.",
+									   cstate->cur_attname, (unsigned long long)cstate->cur_lineno));
+				}
+
+				/*
+				* Here we end processing of current COPY row.
+				* Update copy state counter for number of erroneous rows.
+				*/
+				cstate->num_errors++;
+				cstate->escontext->error_occurred = true;
+
+				/* Only print this NOTICE message, if it will not be followed by ERROR */
+				if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE &&
+					 (
+						 (cstate->opts.on_error == COPY_ON_ERROR_NULL && cstate->opts.reject_limit > 0 && cstate->num_errors <= cstate->opts.reject_limit) ||
+						 (cstate->opts.on_error == COPY_ON_ERROR_IGNORE && (cstate->opts.reject_limit ==  0 || cstate->num_errors <= cstate->opts.reject_limit))
+					 ))
 				{
 					/*
 					 * Since we emit line number and column info in the below
@@ -1001,6 +1057,13 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 			cstate->cur_attval = NULL;
 		}
 
+		/*
+		* Update copy state counter for number of erroneous rows.
+		* But do not set error_occurred, since row was actually accepted
+		*/
+		if (current_row_erroneous)
+			cstate->num_errors++;
+
 		Assert(fieldno == attr_count);
 	}
 	else
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index fad2277991d..c2902ffc339 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3235,7 +3235,7 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
 					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT",
-					  "ON_ERROR", "LOG_VERBOSITY");
+					  "ON_ERROR", "SET_TO_NULL", "LOG_VERBOSITY");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 4002a7f5382..051ca12d107 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -38,6 +38,7 @@ typedef enum CopyOnErrorChoice
 {
 	COPY_ON_ERROR_STOP = 0,		/* immediately throw errors, default */
 	COPY_ON_ERROR_IGNORE,		/* ignore errors */
+	COPY_ON_ERROR_NULL,			/* set error field to null */
 } CopyOnErrorChoice;
 
 /*
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..d9d64082478 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_to_null, on_error set_to_null);
+ERROR:  conflicting or redundant options
+LINE 1: COPY x from stdin (on_error set_to_null, on_error set_to_nul...
+                                                 ^
 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,8 @@ 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_to_null);
+ERROR:  only ON_ERROR STOP is allowed in BINARY mode
 COPY x from stdin (on_error unsupported);
 ERROR:  COPY ON_ERROR "unsupported" not recognized
 LINE 1: COPY x from stdin (on_error unsupported);
@@ -124,12 +130,16 @@ 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 stdin (on_error set_to_null);
+ERROR:  COPY ON_ERROR cannot be used with COPY TO
+LINE 1: COPY x to stdin (on_error set_to_null);
+                         ^
 COPY x from stdin (log_verbosity unsupported);
 ERROR:  COPY LOG_VERBOSITY "unsupported" not recognized
 LINE 1: COPY x from stdin (log_verbosity unsupported);
                            ^
 COPY x from stdin with (reject_limit 1);
-ERROR:  COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
+ERROR:  COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE or SET_TO_NULL
 COPY x from stdin with (on_error ignore, reject_limit 0);
 ERROR:  REJECT_LIMIT (0) must be greater than zero
 -- too many columns in column list: should fail
@@ -769,6 +779,50 @@ 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 INT NOT NULL CHECK(value > 0);
+CREATE DOMAIN d_int_positive_maybe_null AS INT CHECK(value > 0);
+CREATE TABLE t_on_error_null (a d_int_not_null, b d_int_positive_maybe_null, c INT);
+\pset null NULL
+--ok
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+NOTICE:  Erroneous values in 1 row were replaced with NULL
+--ok
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+NOTICE:  Erroneous values in 1 row were replaced with NULL
+-- check inserted content
+TABLE t_on_error_null;
+ a  |  b   |  c   
+----+------+------
+ 11 | NULL |   12
+  1 |   11 | NULL
+(2 rows)
+
+--fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+ERROR:  failed to replace column containing erroneous data with null
+HINT:  Consider specifying the REJECT LIMIT option to skip erroneous rows.
+CONTEXT:  COPY t_on_error_null, line 1, column a: "a"
+--fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+ERROR:  failed to replace column containing erroneous data with null
+HINT:  Consider specifying the REJECT LIMIT option to skip erroneous rows.
+CONTEXT:  COPY t_on_error_null, line 1, column a: "-1"
+--ok. Check interaction with REJECT LIMIT
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null, reject_limit 2);
+NOTICE:  Erroneous values in 2 rows were replaced with NULL
+-- check inserted content
+TABLE t_on_error_null;
+ a  |  b   |  c   
+----+------+------
+ 11 | NULL |   12
+  1 |   11 | NULL
+  1 |   11 |   14
+(3 rows)
+
+--fail. Check interaction with REJECT LIMIT
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null, reject_limit 2);
+ERROR:  encountered more than REJECT_LIMIT (2) rows with data type incompatibility
+CONTEXT:  COPY t_on_error_null, line 3, column a: null input
 -- 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);
@@ -776,6 +830,26 @@ COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity verbose);
 NOTICE:  skipping row due to data type incompatibility at line 2 for column "l": null input
 CONTEXT:  COPY check_ign_err2
 NOTICE:  1 row was skipped due to data type incompatibility
+-- check null substitution massages.
+COPY check_ign_err2 FROM STDIN WITH (on_error set_to_null, log_verbosity verbose);
+NOTICE:  failed to replace row attribute "l" with NULL at line 1.
+CONTEXT:  COPY check_ign_err2, line 1, column l: "'foooooooooooooooo'"
+ERROR:  failed to replace column containing erroneous data with null
+HINT:  Consider specifying the REJECT LIMIT option to skip erroneous rows.
+CONTEXT:  COPY check_ign_err2, line 1, column l: "'foooooooooooooooo'"
+COPY check_ign_err2 FROM STDIN WITH (on_error set_to_null, reject_limit 2, log_verbosity verbose);
+NOTICE:  failed to replace row attribute "l" with NULL at line 1.
+CONTEXT:  COPY check_ign_err2, line 1, column l: "'foooooooooooooooo'"
+NOTICE:  skipping row due to data type incompatibility at line 1 for column "l": "'foooooooooooooooo'"
+CONTEXT:  COPY check_ign_err2
+NOTICE:  failed to replace row attribute "l" with NULL at line 2.
+CONTEXT:  COPY check_ign_err2, line 2, column l: "'foooooooooooooooo'"
+NOTICE:  skipping row due to data type incompatibility at line 2 for column "l": "'foooooooooooooooo'"
+CONTEXT:  COPY check_ign_err2
+NOTICE:  failed to replace row attribute "l" with NULL at line 3.
+CONTEXT:  COPY check_ign_err2, line 3, column l: "'foooooooooooooooo'"
+ERROR:  encountered more than REJECT_LIMIT (2) rows with data type incompatibility
+CONTEXT:  COPY check_ign_err2, line 3, column l: "'foooooooooooooooo'"
 COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity silent);
 -- reset context choice
 \set SHOW_CONTEXT errors
@@ -813,6 +887,28 @@ ERROR:  skipped more than REJECT_LIMIT (3) rows due to data type incompatibility
 CONTEXT:  COPY check_ign_err, line 5, column n: ""
 COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 4);
 NOTICE:  4 rows were skipped due to data type incompatibility
+-- tests for on_error set_to_null option
+truncate check_ign_err;
+COPY check_ign_err FROM STDIN WITH (on_error set_to_null);
+NOTICE:  Erroneous values in 3 rows were replaced with NULL
+SELECT * FROM check_ign_err;
+  n   |  m  |  k   
+------+-----+------
+    1 | {1} | NULL
+    2 | {2} |    1
+    3 | {3} |    2
+    4 | {4} | NULL
+ NULL | {5} | NULL
+(5 rows)
+
+--should fail.
+COPY check_ign_err FROM STDIN WITH (delimiter ',', on_error set_to_null);
+ERROR:  missing data for column "k"
+CONTEXT:  COPY check_ign_err, line 1, column m: ""
+--should fail.
+COPY check_ign_err FROM STDIN WITH (delimiter ',', on_error set_to_null);
+ERROR:  extra data after last expected column
+CONTEXT:  COPY check_ign_err, line 1: "1,{1},1,1"
 -- clean up
 DROP TABLE forcetest;
 DROP TABLE vistest;
@@ -828,6 +924,8 @@ 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 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 45273557ce0..d1dd61b4bf2 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -67,12 +67,14 @@ 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_to_null, on_error set_to_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_to_null);
 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 +89,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 stdin (on_error set_to_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);
@@ -534,6 +537,52 @@ a	{2}	2
 8	{8}	8
 \.
 
+CREATE DOMAIN d_int_not_null AS INT NOT NULL CHECK(value > 0);
+CREATE DOMAIN d_int_positive_maybe_null AS INT CHECK(value > 0);
+CREATE TABLE t_on_error_null (a d_int_not_null, b d_int_positive_maybe_null, c INT);
+
+\pset null NULL
+
+--ok
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+11	a	12
+\.
+
+--ok
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+1	11	d
+\.
+
+-- check inserted content
+TABLE t_on_error_null;
+
+--fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+a	11	14
+\.
+
+--fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+-1	11	13
+\.
+
+--ok. Check interaction with REJECT LIMIT
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null, reject_limit 2);
+-1	11	13
+a	11	14
+1	11	14
+\.
+
+-- check inserted content
+TABLE t_on_error_null;
+
+--fail. Check interaction with REJECT LIMIT
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null, reject_limit 2);
+-1	11	13
+a	11	14
+\N	11	14
+\.
+
 -- 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);
@@ -541,6 +590,15 @@ COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity verbose);
 1	{1}	1	'foo'
 2	{2}	2	\N
 \.
+-- check null substitution massages.
+COPY check_ign_err2 FROM STDIN WITH (on_error set_to_null, log_verbosity verbose);
+2	{2}	2	'foooooooooooooooo'
+\.
+COPY check_ign_err2 FROM STDIN WITH (on_error set_to_null, reject_limit 2, log_verbosity verbose);
+2	{2}	2	'foooooooooooooooo'
+2	{2}	2	'foooooooooooooooo'
+2	{2}	2	'foooooooooooooooo'
+\.
 COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity silent);
 3	{3}	3	'bar'
 4	{4}	4	\N
@@ -588,6 +646,28 @@ a	{7}	7
 10	{10}	10
 \.
 
+-- tests for on_error set_to_null option
+truncate check_ign_err;
+COPY check_ign_err FROM STDIN WITH (on_error set_to_null);
+1	{1}	a
+2	{2}	1
+3	{3}	2
+4	{4}	b
+a	{5}	c
+\.
+
+SELECT * FROM check_ign_err;
+
+--should fail.
+COPY check_ign_err FROM STDIN WITH (delimiter ',', on_error set_to_null);
+1,
+\.
+
+--should fail.
+COPY check_ign_err FROM STDIN WITH (delimiter ',', on_error set_to_null);
+1,{1},1,1
+\.
+
 -- clean up
 DROP TABLE forcetest;
 DROP TABLE vistest;
@@ -603,6 +683,8 @@ 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 TABLE check_ign_err2;
 DROP DOMAIN dcheck_ign_err2;
 DROP TABLE hard_err;
-- 
2.34.1