On Fri, Feb 6, 2026 at 8:58 PM Matheus Alcantara
<[email protected]> wrote:
> >
> 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.
>
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.
> 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));
>
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 protected]>
Date: Sun, 8 Feb 2026 23:47:03 +0800
Subject: [PATCH v23 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 protected]>
Author: Kirill Reshke <[email protected]>
Reviewed-by:
Fujii Masao <[email protected]>
Jim Jones <[email protected]>
"David G. Johnston" <[email protected]>
Yugo NAGATA <[email protected]>
torikoshia <[email protected]>
Masahiko Sawada <[email protected]>
Atsushi Torikoshi <[email protected]>
Matheus Alcantara <[email protected]>
discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=bp3d1_asfe...@mail.gmail.com
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(-)
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..acc808dbb6e 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 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>
@@ -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 155a79a70c5..6de0b643da1 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)));
/*
- * 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),
@@ -984,7 +986,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..1213f3d8832 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("%" 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;
/*
- * 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 = 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 94d6f415a06..c34dd7fcd72 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -958,6 +958,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,7 +1036,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,
@@ -1046,7 +1048,55 @@ 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)
+ {
+ /*
+ * 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);
+
+ /*
+ * 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
+ ereport(ERROR,
+ errcode(ERRCODE_NOT_NULL_VIOLATION),
+ 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.",
+ cstate->cur_attname,
+ format_type_be(typioparams[m])),
+ errdatatype(typioparams[m]));
+
+ /*
+ * 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)
{
@@ -1063,24 +1113,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 3145b314e48..045c0f81243 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);
@@ -782,6 +794,49 @@ 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
+DETAIL: ON_ERROR SET_NULL cannot be applied because column "a" (domain d_int_not_null) does not accept 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: domain d_int_not_null does not allow null values
+DETAIL: ON_ERROR SET_NULL cannot be applied because column "a" (domain d_int_not_null) does not accept null values.
+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: domain d_int_not_null does not allow null values
+DETAIL: ON_ERROR SET_NULL cannot be applied because column "a" (domain d_int_not_null) does not accept null values.
+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: 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);
@@ -841,6 +896,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 66435167500..5e9f6af2c5c 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);
@@ -540,6 +544,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);
@@ -609,6 +649,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
