public inbox for [email protected]
help / color / mirror / Atom feedRe: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
15+ messages / 4 participants
[nested] [flat]
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-01-21 07:37 jian he <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: jian he @ 2026-01-21 07:37 UTC (permalink / raw)
To: Matheus Alcantara <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, Jan 21, 2026 at 3:55 AM Matheus Alcantara
<[email protected]> wrote:
> Hi,
>
> The patch needs a new rebase, could you please send a new version?
sure. please check the attached.
Attachments:
[text/x-patch] v21-0001-COPY-on_error-set_null.patch (22.3K, 2-v21-0001-COPY-on_error-set_null.patch)
download | inline diff:
From 30056bdb56d0d8f63333ccc4cb60808abfa91644 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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 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]>
discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-01-21 18:47 Matheus Alcantara <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Matheus Alcantara @ 2026-01-21 18:47 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed Jan 21, 2026 at 4:37 AM -03, jian he wrote:
> On Wed, Jan 21, 2026 at 3:55 AM Matheus Alcantara
> <[email protected]> wrote:
>> Hi,
>>
>> The patch needs a new rebase, could you please send a new version?
>
> sure. please check the attached.
Thanks for the new version. I have some comments on this first round of
review:
+ errmsg_plural("invalid values in %" PRIu64 " row was replaced with null due to data type incompatibility",
+ "invalid values in %" PRIu64 " rows were replaced with null due to data type incompatibility",
I think that we could remove the "invalid values in" to make it
consistency with the COPY_ON_ERROR_IGNORE NOTICE
----------
+ cstate->domain_with_constraint = (bool *) palloc0(attr_count * sizeof(bool));
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-01-22 14:45 jian he <[email protected]>
parent: Matheus Alcantara <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: jian he @ 2026-01-22 14:45 UTC (permalink / raw)
To: Matheus Alcantara <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Jan 22, 2026 at 2:47 AM Matheus Alcantara
<[email protected]> wrote:
>
> Thanks for the new version. I have some comments on this first round of
> review:
>
> + errmsg_plural("invalid values in %" PRIu64 " row was replaced with null due to data type incompatibility",
> + "invalid values in %" PRIu64 " rows were replaced with null due to data type incompatibility",
>
> I think that we could remove the "invalid values in" to make it
> consistency with the COPY_ON_ERROR_IGNORE NOTICE
>
sure.
> ----------
>
> + cstate->domain_with_constraint = (bool *) palloc0(attr_count * sizeof(bool));
>
> I think that we can use palloc_array?
>
sure.
> ----------
>
> Should FORCE_NOT_NULL be allowed to be used with ON_ERROR set_null? It
> seems to me that ON_ERROR set_null overwrite the FORCE_NOT_NULL
> behaviour:
>
> postgres=# 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?
>
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?
>
> 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?
>
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>
>
> I may have missing something, but we are still considering implementing
> the REJECT_LIMIT + ON_ERROR set_null?
Possibly as a separate patch later.
--
jian
https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-01-27 18:37 Matheus Alcantara <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Matheus Alcantara @ 2026-01-27 18:37 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On 22/01/26 11:45, jian he wrote:
>> 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?
>>
> 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?
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?
>> 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?
>>
>
> 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>
It looks good, I was thinking in something like this.
>>
>> I may have missing something, but we are still considering implementing
>> the REJECT_LIMIT + ON_ERROR set_null?
> Possibly as a separate patch later.
Ok, good, thanks.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-02-06 03:40 jian he <[email protected]>
parent: Matheus Alcantara <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: jian he @ 2026-02-06 03:40 UTC (permalink / raw)
To: Matheus Alcantara <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, Jan 28, 2026 at 2:37 AM Matheus Alcantara
<[email protected]> wrote:
>
> 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?
>
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/
Attachments:
[text/x-patch] v22-0001-COPY-on_error-set_null.patch (22.9K, 2-v22-0001-COPY-on_error-set_null.patch)
download | inline diff:
From b479c57753b51a868d5d19080a070c7aed9026de Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 5 Feb 2026 16:09:59 +0800
Subject: [PATCH v22 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_+Q@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 | 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)));
/*
- * 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),
@@ -1035,7 +1037,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 0c73b0259c3..cc4a2dab348 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 0d9199f5ce8..e12decaa81a 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);
@@ -1036,7 +1037,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,
@@ -1047,7 +1049,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)
{
@@ -1064,24 +1110,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 a610ef603f4..6810a6d04e6 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 b2a68fa6407..abecfe51098 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..72034796aca 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,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: 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 +893,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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-02-06 12:58 Matheus Alcantara <[email protected]>
parent: jian he <[email protected]>
0 siblings, 2 replies; 15+ messages in thread
From: Matheus Alcantara @ 2026-02-06 12:58 UTC (permalink / raw)
To: jian he <[email protected]>; Matheus Alcantara <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri Feb 6, 2026 at 12:40 AM -03, jian he wrote:
>> 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?
>>
>
> 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.
>
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));
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-02-06 13:00 Matheus Alcantara <[email protected]>
parent: Matheus Alcantara <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: Matheus Alcantara @ 2026-02-06 13:00 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri Feb 6, 2026 at 9:58 AM -03, Matheus Alcantara 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.
>
I miss to include the diff file, sorry about that.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
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);
Attachments:
[text/plain] v22-0001-tweaks.diff.nocfbot (4.2K, 2-v22-0001-tweaks.diff.nocfbot)
download | inline diff:
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);
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-02-09 03:59 jian he <[email protected]>
parent: Matheus Alcantara <[email protected]>
1 sibling, 2 replies; 15+ messages in thread
From: jian he @ 2026-02-09 03:59 UTC (permalink / raw)
To: Matheus Alcantara <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
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/
Attachments:
[text/x-patch] v23-0001-COPY-on_error-set_null.patch (23.4K, 2-v23-0001-COPY-on_error-set_null.patch)
download | inline diff:
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_+Q@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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-02-09 15:36 Matheus Alcantara <[email protected]>
parent: jian he <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: Matheus Alcantara @ 2026-02-09 15:36 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On 09/02/26 00:59, jian he 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.
>
Ok, agree.
>> 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.
>
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.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-02-25 07:36 Peter Eisentraut <[email protected]>
parent: jian he <[email protected]>
1 sibling, 1 reply; 15+ messages in thread
From: Peter Eisentraut @ 2026-02-25 07:36 UTC (permalink / raw)
To: jian he <[email protected]>; Matheus Alcantara <[email protected]>; +Cc: torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
I have a few more cosmetic changes to suggest:
- doc/src/sgml/ref/copy.sgml
+ and <literal>set_null</literal> means replace field containing
invalid
should be "the field" and "the invalid"
+ input value with <literal>NULL</literal> and continue to the next
field.
change <literal>NULL</literal> to "a null value"
+ <para>
+ For <literal>ignore</literal> option, a <literal>NOTICE</literal>
message
+ containing the ignored row count is emitted at the end of the
<command>COPY FROM</command>
+ if at least one row was discarded.
+ For <literal>set_null</literal> option, a <literal>NOTICE</literal>
+ message indicating the number of rows where invalid input values were
+ replaced with null is emitted at the end of the <command>COPY
FROM</command>
+ if at least one row was replaced.
+ </para>
I think this could be written more compactly, like
If on_error is set to ignore or set_null, a NOTICE message is emitted
at the end of the COPY FROM command containing the count of rows that
were ignored or changed, if at least one row was affected.
- src/backend/commands/copy.c
/*
- * Allow "stop", or "ignore" values.
+ * Allow "stop", "ignore", "set_null" values.
*/
Just remove that comment. It is evident from the following code.
- src/backend/commands/copyfrom.c
+ "%" PRIu64 " rows were replaced with null due to data type
incompatibility"
I think this is not quite correctly worded. It should be something like
in NNN rows, columns were set to null due to ...
because you are not setting the whole row to null.
/*
- * Currently we only support COPY_ON_ERROR_IGNORE. We'll add other
- * options later
+ * Currently we only support COPY_ON_ERROR_IGNORE,
+ * COPY_ON_ERROR_SET_NULL. We'll add other options later
*/
Delete this comment.
+ if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
+ {
+ int attr_count = list_length(cstate->attnumlist);
+
+ cstate->domain_with_constraint = palloc0_array(bool, attr_count);
Maybe add a comment for this block to explain that you are collecting
information about domains for later.
- src/backend/commands/copyfromparse.c
/*
- * If ON_ERROR is specified with IGNORE, skip rows with soft errors
+ * If ON_ERROR is specified with IGNORE, skip rows with soft errors.
+ * If ON_ERROR is specified with SET_NULL, try to replace with null.
*/
Trim this comment. Maybe "If ON_ERROR is specified, handle the
different options". We don't need to re-explain here what the options do.
+ /*
+ * If the column type is a constrained domain, an additional
+ * InputFunctionCallSafe may be needed to raise error for
+ * domain constraint violation.
+ */
Why "may be needed"? Is it sometimes not needed? Why, under what
circumstances?
The subsequent error message writes "domain ... does not allow null
values", but AFAICT a domain input failure could also be due to a check
constraint failure? How would that be handled? The flow here is a bit
confusing.
- src/test/regress/sql/copy2.sql
I suggest adding a space after "--" in a comment, like "-- error"
instead of "--error".
Similarly, a space after CHECK, like "CHECK (...)".
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-02-28 02:04 jian he <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: jian he @ 2026-02-28 02:04 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Matheus Alcantara <[email protected]>; torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, Feb 25, 2026 at 3:36 PM Peter Eisentraut <[email protected]> wrote:
>
> + if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
> + {
> + int attr_count = list_length(cstate->attnumlist);
> +
> + cstate->domain_with_constraint = palloc0_array(bool, attr_count);
>
> Maybe add a comment for this block to explain that you are collecting
> information about domains for later.
>
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).
+ */
> + /*
> + * If the column type is a constrained domain, an additional
> + * InputFunctionCallSafe may be needed to raise error for
> + * domain constraint violation.
> + */
>
> Why "may be needed"? Is it sometimes not needed? Why, under what
> circumstances?
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.
+ */
>
> 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.
>
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/
Attachments:
[text/x-patch] v24-0001-COPY-on_error-set_null.patch (23.6K, 2-v24-0001-COPY-on_error-set_null.patch)
download | inline diff:
From 61d2a81c50ac1691b4a328ae5a8fc24254f23c8d Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 28 Feb 2026 09:57:42 +0800
Subject: [PATCH v24 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]>
Peter Eisentraut <[email protected]>
discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com
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)));
- /*
- * Allow "stop", or "ignore" 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 +983,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 2b7556b287c..607bab3fc7c 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
@@ -1463,14 +1464,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("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;
- /*
- * Currently we only support COPY_ON_ERROR_IGNORE. 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);
+
+ /*
+ * 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++;
+ 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);
+
+ /*
+ * For constrained domain types, we need an additional
+ * InputFunctionCallSafe to ensure that an error is thrown if
+ * the domain constraint rejects NULL.
+ */
+ 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)
{
@@ -1064,24 +1111,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 b2dba6d10ab..61c08241b04 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..1dd9a63bb80 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: in 3 rows, columns were set to 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..d3b427857ab 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-03-04 13:25 Fujii Masao <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-03-04 13:25 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: jian he <[email protected]>; Matheus Alcantara <[email protected]>; torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Mar 3, 2026 at 3:38 PM Peter Eisentraut <[email protected]> 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
Attachments:
[application/octet-stream] v1-0001-file_fdw-Add-regression-test-for-file_fdw-with-ON.patch (2.3K, 2-v1-0001-file_fdw-Add-regression-test-for-file_fdw-with-ON.patch)
download | inline diff:
From 400adb16a50cc817eaea816fc6735a7b4e7af9aa Mon Sep 17 00:00:00 2001
From: Fujii Masao <[email protected]>
Date: Wed, 4 Mar 2026 22:19:06 +0900
Subject: [PATCH v1] file_fdw: Add regression test for file_fdw with
ON_ERROR='set_null'.
Commit 2a525cc97e1 introduced the ON_ERROR = 'set_null' option for COPY,
allowing it to be used with foreign tables backed by file_fdw. However,
unlike ON_ERROR = 'ignore', no regression test was added to verify
this behavior for file_fdw.
This commit adds a regression test to ensure that foreign tables using
file_fdw work correctly with ON_ERROR = 'set_null', improving test coverage.
---
contrib/file_fdw/expected/file_fdw.out | 12 +++++++++++-
contrib/file_fdw/sql/file_fdw.sql | 4 +++-
2 files changed, 14 insertions(+), 2 deletions(-)
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 251f00bd258..640986528ae 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -246,7 +246,17 @@ SELECT * FROM agg_bad; -- ERROR
ERROR: invalid input syntax for type real: "aaa"
CONTEXT: COPY agg_bad, line 3, column b: "aaa"
-- on_error, log_verbosity and reject_limit tests
-ALTER FOREIGN TABLE agg_bad OPTIONS (ADD on_error 'ignore');
+ALTER FOREIGN TABLE agg_bad OPTIONS (ADD on_error 'set_null');
+SELECT * FROM agg_bad;
+ a | b
+-----+--------
+ 100 | 99.097
+ 0 | _null_
+ 42 | 324.78
+ 1 | _null_
+(4 rows)
+
+ALTER FOREIGN TABLE agg_bad OPTIONS (SET on_error 'ignore');
SELECT * FROM agg_bad;
NOTICE: 2 rows were skipped due to data type incompatibility
a | b
diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql
index 2cba84b1db7..56bfc926c00 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -171,7 +171,9 @@ SELECT * FROM agg_csv c JOIN agg_text t ON (t.a = c.a) ORDER BY c.a;
SELECT * FROM agg_bad; -- ERROR
-- on_error, log_verbosity and reject_limit tests
-ALTER FOREIGN TABLE agg_bad OPTIONS (ADD on_error 'ignore');
+ALTER FOREIGN TABLE agg_bad OPTIONS (ADD on_error 'set_null');
+SELECT * FROM agg_bad;
+ALTER FOREIGN TABLE agg_bad OPTIONS (SET on_error 'ignore');
SELECT * FROM agg_bad;
ALTER FOREIGN TABLE agg_bad OPTIONS (ADD log_verbosity 'silent');
SELECT * FROM agg_bad;
--
2.51.2
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-03-13 02:57 Fujii Masao <[email protected]>
parent: Fujii Masao <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-03-13 02:57 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: jian he <[email protected]>; Matheus Alcantara <[email protected]>; torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, Mar 4, 2026 at 10:25 PM Fujii Masao <[email protected]> wrote:
>
> On Tue, Mar 3, 2026 at 3:38 PM Peter Eisentraut <[email protected]> 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.
Barring any objections, I will commit the patch.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-03-13 13:50 Fujii Masao <[email protected]>
parent: Fujii Masao <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-03-13 13:50 UTC (permalink / raw)
To: Yi Ding <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; jian he <[email protected]>; Matheus Alcantara <[email protected]>; torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri, Mar 13, 2026 at 2:51 PM Yi Ding <[email protected]> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Re: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
@ 2026-03-16 03:15 Fujii Masao <[email protected]>
parent: Fujii Masao <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Fujii Masao @ 2026-03-16 03:15 UTC (permalink / raw)
To: Yi Ding <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; jian he <[email protected]>; Matheus Alcantara <[email protected]>; torikoshia <[email protected]>; Masahiko Sawada <[email protected]>; vignesh C <[email protected]>; Jim Jones <[email protected]>; Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; David G. Johnston <[email protected]>; Yugo NAGATA <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Mar 16, 2026 at 11:39 AM Yi Ding <[email protected]> wrote:
>
>
> At 2026-03-13 21:50:07, "Fujii Masao" <[email protected]> wrote:
> >On Fri, Mar 13, 2026 at 2:51 PM Yi Ding <[email protected]> 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
> >
>
> Sounds reasonable,that addressed my comment.
I've pushed the patch. Thanks!
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-03-16 03:15 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-21 07:37 Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row jian he <[email protected]>
2026-01-21 18:47 ` Matheus Alcantara <[email protected]>
2026-01-22 14:45 ` jian he <[email protected]>
2026-01-27 18:37 ` Matheus Alcantara <[email protected]>
2026-02-06 03:40 ` jian he <[email protected]>
2026-02-06 12:58 ` Matheus Alcantara <[email protected]>
2026-02-06 13:00 ` Matheus Alcantara <[email protected]>
2026-02-09 03:59 ` jian he <[email protected]>
2026-02-09 15:36 ` Matheus Alcantara <[email protected]>
2026-02-25 07:36 ` Peter Eisentraut <[email protected]>
2026-02-28 02:04 ` jian he <[email protected]>
2026-03-04 13:25 ` Fujii Masao <[email protected]>
2026-03-13 02:57 ` Fujii Masao <[email protected]>
2026-03-13 13:50 ` Fujii Masao <[email protected]>
2026-03-16 03:15 ` Fujii Masao <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox