public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matheus Alcantara <[email protected]>
To: jian he <[email protected]>
Cc: torikoshia <[email protected]>
Cc: Masahiko Sawada <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Jim Jones <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Yugo NAGATA <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
Date: Fri, 06 Feb 2026 10:00:36 -0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com>
<CALDaNm0FUtPjH9wz5dgwOBNtsAXEZj=0-TYGsyQXchQy-hXGrw@mail.gmail.com>
<CACJufxFpm2Gzx4AD9qKtiQSiiiunX02wNTxu0JoFm7nEKF2KUw@mail.gmail.com>
<CAD21AoBjmEYjZT9A6P-vAuwboiiYtXaMc12pX2ySmh3RXi=v8w@mail.gmail.com>
<CACJufxF6_YwAboiCaVYLRtNpO4kGbXqkXzH_7W=pUvrNXK8WuQ@mail.gmail.com>
<CAD21AoBWwEzpp3Z6tp-O-AQGftK-kuj6vRva2L5daWoWBtbnRg@mail.gmail.com>
<CACJufxGEHmijmP-QqvrmqU6cxmhgpdjY7ewQBQ=E9NmdyEcqmw@mail.gmail.com>
<[email protected]>
<CACJufxG=em0PHZvy1EAZ+vxPZ8UA68MfQ-Hji+h+WgnWNpqmVQ@mail.gmail.com>
<CACJufxF0c3k5O8up9NOY-m02nyJ0f6N1tKxZwjCewTqvvFmbLw@mail.gmail.com>
<CACJufxHr-LBV2pB9m64mA=1pgVMM4LvUn+-MzpeViWV=Ks_cyg@mail.gmail.com>
<[email protected]>
<CACJufxEs_VBV39gYHnpLFOMcUaUD-ADAst_ePTCgmoDR8O=ekg@mail.gmail.com>
<[email protected]>
<CACJufxGYPXQ_Jz1avF5eSh_XJRsxhPSUZ+=RzG3Hz4_XNAc32g@mail.gmail.com>
<[email protected]>
<CACJufxHFwQMw1As+QFk+fA7S8ZxRG2wOvHcvmsWuj2XJ+W6d_A@mail.gmail.com>
<[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);
view thread (15+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox