public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirill Reshke <[email protected]>
To: jian he <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: Jim Jones <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Yugo NAGATA <[email protected]>
Cc: torikoshia <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
Date: Sat, 16 Nov 2024 14:55:10 +0500
Message-ID: <CALdSSPh13qiKmhqwj=bR_3seZJVkP9E6BDbLfHXQ_DbaxQL4FA@mail.gmail.com> (raw)
In-Reply-To: <CACJufxGsGqU0BnHw9+Vk3KhBSA3951fZs+JQnM387CiJD3Qn+g@mail.gmail.com>
References: <CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com>
<CACJufxFFU92-H7G8tmpxt9oTSfL062OA7n5rPx-YbOAtDUUzGw@mail.gmail.com>
<[email protected]>
<CACJufxGnc+=No=Ua6NFT2ADt0vRL=m1QsuCOM=9aKPKWh9_L6Q@mail.gmail.com>
<[email protected]>
<CACJufxFT9j8o5kEC8dPCQqLomWjeJm9V9m8eZjj2Gvc_F5ha=g@mail.gmail.com>
<[email protected]>
<CAKFQuwaYNw8U-9JkFdyOX4i4Y3J1sp6+dk-sh8YmZGCq8gMeVQ@mail.gmail.com>
<[email protected]>
<CACJufxFFdtPKk4B5rSVNEk6yCH2Amvi_8w3Gaz5wg9M_t9c5Rw@mail.gmail.com>
<[email protected]>
<CACJufxEgiysa2SMJPGp0aN476Ojm636MfJK88DZC7TVYsXYBBQ@mail.gmail.com>
<CALdSSPhgjCbyb=ZRgr4LaCFJV2-F9_CxMeX6poHuGCt_f9GYAw@mail.gmail.com>
<[email protected]>
<CALdSSPi1JE9xc31W6DPAdk-bQHeo3HNAYB-10Biruu-w4GJN0Q@mail.gmail.com>
<[email protected]>
<CALdSSPjYw5g7_sc++bRcxOnC7jW6O2qiSkgdKRUYFXZZv3-Ktw@mail.gmail.com>
<CACJufxGsGqU0BnHw9+Vk3KhBSA3951fZs+JQnM387CiJD3Qn+g@mail.gmail.com>
On Sat, 16 Nov 2024 at 13:27, jian he <[email protected]> wrote:
>
> On Sat, Nov 9, 2024 at 8:55 PM Kirill Reshke <[email protected]> wrote:
> >
>
> > > > But while I was trying to implement that, I realized that I don't
> > > > understand v4 of this patch. My misunderstanding is about
> > > > `t_on_error_null` tests. We are allowed to insert a NULL value for the
> > > > first column of t_on_error_null using COPY ON_ERROR SET_TO_NULL. Why
> > > > do we do that? My thought is we should try to execute
> > > > InputFunctionCallSafe with NULL value (i mean, here [1]) for the
> > > > column after we failed to insert the input value. And, if this second
> > > > call is successful, we do replacement, otherwise we count the row as
> > > > erroneous.
> > >
> > > Your concern is valid. Allowing NULL to be stored in a column with a NOT NULL
> > > constraint via COPY ON_ERROR=SET_TO_NULL does seem unexpected. As you suggested,
> > > NULL values set by SET_TO_NULL should probably be re-evaluated.
> >
> > Thank you. I updated the patch with a NULL re-evaluation.
> >
>
>
> take me sometime to understand your change with InputFunctionCallSafe.
> it actually works fine with domain,
> i think mainly because domain_in proisstrict is false and all other
> type input function proisstrict is true!
>
>
> --case1
> create table t1(a dnn);
> copy t1 from stdin(on_error set_to_null);
> A
> \.
>
> --case2
> create table t2(a int not null);
> copy t2 from stdin(on_error set_to_null);
> A
> \.
>
> I think it should be to either let domains with not-null behave the
> same as column level not-null
> or just insert NULL to a column with domain not-null constraint.
>
>
> in doc[1], we already mentioned that a column with a not-null domain
> is possible to have null value .
> https://www.postgresql.org/docs/current/sql-createdomain.html
>
> attached v8, based on your v7, main change it NextCopyFrom,
> InputFunctionCallSafe.
> The idea is when InputFunctionCallSafe fails, on_error set_to_null
> needs to check if this is a type as not-null domain.
> pass NULL string to InputFunctionCallSafe again to check if this type
> allows null or not.
> If not allow null then error out (ereport(ERROR)).
> i think this will align with column level not-null constraint, what do
> you guys think?
>
>
> i am mainly change copyfromparse.c's for now.
> other places no change, same as v7.
Hello. I received your email just as I was ready to send my version
eight of this thread.
Your patch does not apply due to 9a70f67.
```
reshke@ygp-jammy:~/pg$ git apply v8-0001-COPY-option-on_error-set_to_null.patch
error: patch failed: src/backend/commands/copyfrom.c:1018
error: src/backend/commands/copyfrom.c: patch does not apply
```
1) Your v8 does not fix tab-complete issue mentioned by Atsushi
Torikoshi in [1].
2) Your version does not address discussion about SET_TO_NULL vs
REJECT_LIMIT (see [2] & [3]). I am leaning towards option 1 from [3],
and my v8 implements that.
```
reshke=# create domain dd as int not null;
CREATE DOMAIN
reshke=# create table tt(i dd);
CREATE TABLE
reshke=# copy tt from stdin with (on_error set_to_null, log_verbosity
verbose, reject_limit 2);
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.
>> s
>> 1
>> \.
ERROR: domain dd does not allow null values
CONTEXT: COPY tt, line 1, column i: "s"
reshke=#
```
I expect no error here, as reject_limit is specified.
Regression test that checks for this in v7 were changed, in my
opinion, incorrectly.
I am attaching my v8 for reference.
[1] https://www.postgresql.org/message-id/501dd655ddb04693c15baeb6485bc601%40oss.nttdata.com
[2] https://www.postgresql.org/message-id/07587c36-18b3-4ccb-b5fb-579bcb04ed37%40oss.nttdata.com
[3] https://www.postgresql.org/message-id/1462d79784b2475f1c714c65a6f25652%40oss.nttdata.com
--
Best regards,
Kirill Reshke
Attachments:
[application/octet-stream] v8-0001-Introduce-COPY-option-to-replace-columns-containi.patch (24.7K, 2-v8-0001-Introduce-COPY-option-to-replace-columns-containi.patch)
download | inline diff:
From df39b38589fc8384e0590d76f8b0e2e4295f6f50 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 12 Sep 2024 17:07:02 +0800
Subject: [PATCH v8] Introduce COPY option to replace columns containing
erroneous data with null
extent "on_error action", introduce new option: on_error set_to_null.
Due to current grammar, we cannot use "on_error null",
so I choose on_error set_to_null.
any data type conversion errors while the COPY FROM process will set that column value to be NULL.
this will only work with COPY FROM and non-binary format.
However this will respect the not-null constraint, meaning, if you actually converted error to null,
but the column has not-null constraint, not-null constraint violation ERROR will be raised.
discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=BP3d1_aSfe_+Q@mail.gmail.com
---
doc/src/sgml/ref/copy.sgml | 9 +--
src/backend/commands/copy.c | 12 ++--
src/backend/commands/copyfrom.c | 53 +++++++++++---
src/backend/commands/copyfromparse.c | 69 +++++++++++++++++-
src/bin/psql/tab-complete.in.c | 2 +-
src/include/commands/copy.h | 1 +
src/test/regress/expected/copy2.out | 100 ++++++++++++++++++++++++++-
src/test/regress/sql/copy2.sql | 82 ++++++++++++++++++++++
8 files changed, 303 insertions(+), 25 deletions(-)
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 8394402f096..bce10ea62e5 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -394,12 +394,13 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
Specifies how to behave when encountering an error converting a column's
input value into its data type.
An <replaceable class="parameter">error_action</replaceable> value of
- <literal>stop</literal> means fail the command, while
- <literal>ignore</literal> means discard the input row and continue with the next one.
+ <literal>stop</literal> means fail the command,
+ <literal>ignore</literal> means discard the input row and continue with the next one, and
+ <literal>set_to_null</literal> means replace columns containing erroneous input values with <literal>null</literal> and move to the next row.
The default is <literal>stop</literal>.
</para>
<para>
- The <literal>ignore</literal> option is applicable only for <command>COPY FROM</command>
+ The <literal>ignore</literal> and <literal>set_to_null</literal> options are applicable only for <command>COPY FROM</command>
when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
</para>
<para>
@@ -422,7 +423,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
Specifies the maximum number of errors tolerated while converting a
column's input value to its data type, when <literal>ON_ERROR</literal> is
set to <literal>ignore</literal>.
- If the input causes more errors than the specified value, the <command>COPY</command>
+ If the input contains more erroneous rows than the specified value, the <command>COPY</command>
command fails, even with <literal>ON_ERROR</literal> set to <literal>ignore</literal>.
This clause must be used with <literal>ON_ERROR</literal>=<literal>ignore</literal>
and <replaceable class="parameter">maxerror</replaceable> must be positive <type>bigint</type>.
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 3485ba8663f..304022cd867 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -403,12 +403,14 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from)
parser_errposition(pstate, def->location)));
/*
- * Allow "stop", or "ignore" values.
+ * Allow "stop", "ignore", "set_to_null" values.
*/
if (pg_strcasecmp(sval, "stop") == 0)
return COPY_ON_ERROR_STOP;
if (pg_strcasecmp(sval, "ignore") == 0)
return COPY_ON_ERROR_IGNORE;
+ if (pg_strcasecmp(sval, "set_to_null") == 0)
+ return COPY_ON_ERROR_NULL;
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -904,13 +906,13 @@ ProcessCopyOptions(ParseState *pstate,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("only ON_ERROR STOP is allowed in BINARY mode")));
- if (opts_out->reject_limit && !opts_out->on_error)
+ if (opts_out->reject_limit && !(opts_out->on_error == COPY_ON_ERROR_NULL || opts_out->on_error == COPY_ON_ERROR_IGNORE))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
/*- translator: first and second %s are the names of COPY option, e.g.
- * ON_ERROR, third is the value of the COPY option, e.g. IGNORE */
- errmsg("COPY %s requires %s to be set to %s",
- "REJECT_LIMIT", "ON_ERROR", "IGNORE")));
+ * ON_ERROR, third is the value of the COPY option, e.g. IGNORE or SET_TO_NULL */
+ errmsg("COPY %s requires %s to be set to %s or %s",
+ "REJECT_LIMIT", "ON_ERROR", "IGNORE", "SET_TO_NULL")));
}
/*
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 754cb496169..557b00266ff 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1003,7 +1003,7 @@ CopyFrom(CopyFromState cstate)
if (!NextCopyFrom(cstate, econtext, myslot->tts_values, myslot->tts_isnull))
break;
- if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE &&
+ if ((cstate->opts.on_error == COPY_ON_ERROR_IGNORE || cstate->opts.on_error == COPY_ON_ERROR_NULL) &&
cstate->escontext->error_occurred)
{
/*
@@ -1018,12 +1018,30 @@ CopyFrom(CopyFromState cstate)
pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
cstate->num_errors);
- if (cstate->opts.reject_limit > 0 &&
- cstate->num_errors > cstate->opts.reject_limit)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
- errmsg("skipped more than REJECT_LIMIT (%lld) rows due to data type incompatibility",
- (long long) cstate->opts.reject_limit)));
+ if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+ {
+ if (cstate->opts.reject_limit > 0 &&
+ cstate->num_errors > cstate->opts.reject_limit)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("skipped more than REJECT_LIMIT (%lld) rows due to data type incompatibility",
+ (long long) cstate->opts.reject_limit)));
+ }
+ else
+ {
+ /* Provide different error msg if reject_limit is zero */
+ if (cstate->opts.reject_limit == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("failed to replace column containing erroneous data with null",
+ (long long) cstate->opts.reject_limit),
+ errhint("Consider specifying the REJECT_LIMIT option to skip erroneous rows.")));
+ else if (cstate->num_errors > cstate->opts.reject_limit)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("encountered more than REJECT_LIMIT (%lld) rows with data type incompatibility",
+ (long long) cstate->opts.reject_limit)));
+ }
/* Repeat NextCopyFrom() until no soft error occurs */
continue;
@@ -1321,7 +1339,7 @@ CopyFrom(CopyFromState cstate)
/* Done, clean up */
error_context_stack = errcallback.previous;
- if (cstate->opts.on_error != COPY_ON_ERROR_STOP &&
+ if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE &&
cstate->num_errors > 0 &&
cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT)
ereport(NOTICE,
@@ -1330,6 +1348,18 @@ CopyFrom(CopyFromState cstate)
(unsigned long long) cstate->num_errors,
(unsigned long long) cstate->num_errors));
+ /* In case on_error SET_TO_NULL, if COPY succeed, it means that
+ * all erroneous rows attributes filled with NULL
+ */
+ if (cstate->opts.on_error == COPY_ON_ERROR_NULL &&
+ cstate->num_errors > 0 &&
+ cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT)
+ ereport(NOTICE,
+ errmsg_plural("Erroneous values in %llu row were replaced with NULL",
+ "Erroneous values in %llu rows were replaced with NULL",
+ (unsigned long long) cstate->num_errors,
+ (unsigned long long) cstate->num_errors));
+
if (bistate != NULL)
FreeBulkInsertState(bistate);
@@ -1474,10 +1504,11 @@ BeginCopyFrom(ParseState *pstate,
cstate->escontext->error_occurred = false;
/*
- * Currently we only support COPY_ON_ERROR_IGNORE. We'll add other
- * options later
+ * Currently we only support COPY_ON_ERROR_IGNORE, COPY_ON_ERROR_NULL.
+ * We'll add other options later
*/
- if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE)
+ if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE ||
+ cstate->opts.on_error == COPY_ON_ERROR_NULL)
cstate->escontext->details_wanted = false;
}
else
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index d1d43b53d83..943926ad1d9 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -871,6 +871,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
int fldct;
int fieldno;
char *string;
+ bool current_row_erroneous = false;
/* read raw fields in the next line */
if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
@@ -949,7 +950,8 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
/*
* If ON_ERROR is specified with IGNORE, skip rows with soft
- * errors
+ * errors. If ON_ERROR is specified with SET_TO_NULL, try
+ * to replace attribute value with NULL.
*/
else if (!InputFunctionCallSafe(&in_functions[m],
string,
@@ -960,9 +962,63 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
{
Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP);
- cstate->num_errors++;
+ /*
+ * Regardless of NULL substrition success, we count
+ * current row as erroneous
+ */
+ current_row_erroneous = true;
- if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+ /*
+ * We encountered an error while parsing one of attributes.
+ *
+ */
+ if (cstate->opts.on_error == COPY_ON_ERROR_NULL && string != NULL)
+ {
+ /*
+ * Temporary unset error_occurred, for next InputFunctionCallSafe
+ * sanity. If null substitution for this attribute will
+ * succeed, we will accept this row.
+ */
+ cstate->escontext->error_occurred = false;
+
+ if (InputFunctionCallSafe(&in_functions[m],
+ NULL,
+ typioparams[m],
+ att->atttypmod,
+ (Node *) cstate->escontext,
+ &values[m]))
+ {
+ /* If datatype if okay with NULL, replace
+ * with null
+ */
+ nulls[m] = true;
+
+ if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+ ereport(NOTICE,
+ errmsg("replaced row attribute \"%s\" with NULL due to data type incompatibility at line %llu.",
+ cstate->cur_attname, (unsigned long long) cstate->cur_lineno));
+ continue;
+ }
+
+ if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE)
+ ereport(NOTICE,
+ errmsg("failed to replace row attribute \"%s\" with NULL at line %llu.",
+ cstate->cur_attname, (unsigned long long)cstate->cur_lineno));
+ }
+
+ /*
+ * Here we end processing of current COPY row.
+ * Update copy state counter for number of erroneous rows.
+ */
+ cstate->num_errors++;
+ cstate->escontext->error_occurred = true;
+
+ /* Only print this NOTICE message, if it will not be followed by ERROR */
+ if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE &&
+ (
+ (cstate->opts.on_error == COPY_ON_ERROR_NULL && cstate->opts.reject_limit > 0 && cstate->num_errors <= cstate->opts.reject_limit) ||
+ (cstate->opts.on_error == COPY_ON_ERROR_IGNORE && (cstate->opts.reject_limit == 0 || cstate->num_errors <= cstate->opts.reject_limit))
+ ))
{
/*
* Since we emit line number and column info in the below
@@ -1001,6 +1057,13 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
cstate->cur_attval = NULL;
}
+ /*
+ * Update copy state counter for number of erroneous rows.
+ * But do not set error_occurred, since row was actually accepted
+ */
+ if (current_row_erroneous)
+ cstate->num_errors++;
+
Assert(fieldno == attr_count);
}
else
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index fad2277991d..c2902ffc339 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3235,7 +3235,7 @@ match_previous_words(int pattern_id,
COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
"HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
"FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT",
- "ON_ERROR", "LOG_VERBOSITY");
+ "ON_ERROR", "SET_TO_NULL", "LOG_VERBOSITY");
/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 4002a7f5382..051ca12d107 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -38,6 +38,7 @@ typedef enum CopyOnErrorChoice
{
COPY_ON_ERROR_STOP = 0, /* immediately throw errors, default */
COPY_ON_ERROR_IGNORE, /* ignore errors */
+ COPY_ON_ERROR_NULL, /* set error field to null */
} CopyOnErrorChoice;
/*
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 64ea33aeae8..d9d64082478 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -81,6 +81,10 @@ COPY x from stdin (on_error ignore, on_error ignore);
ERROR: conflicting or redundant options
LINE 1: COPY x from stdin (on_error ignore, on_error ignore);
^
+COPY x from stdin (on_error set_to_null, on_error set_to_null);
+ERROR: conflicting or redundant options
+LINE 1: COPY x from stdin (on_error set_to_null, on_error set_to_nul...
+ ^
COPY x from stdin (log_verbosity default, log_verbosity verbose);
ERROR: conflicting or redundant options
LINE 1: COPY x from stdin (log_verbosity default, log_verbosity verb...
@@ -92,6 +96,8 @@ COPY x from stdin (format BINARY, null 'x');
ERROR: cannot specify NULL in BINARY mode
COPY x from stdin (format BINARY, on_error ignore);
ERROR: only ON_ERROR STOP is allowed in BINARY mode
+COPY x from stdin (format BINARY, on_error set_to_null);
+ERROR: only ON_ERROR STOP is allowed in BINARY mode
COPY x from stdin (on_error unsupported);
ERROR: COPY ON_ERROR "unsupported" not recognized
LINE 1: COPY x from stdin (on_error unsupported);
@@ -124,12 +130,16 @@ COPY x to stdout (format BINARY, on_error unsupported);
ERROR: COPY ON_ERROR cannot be used with COPY TO
LINE 1: COPY x to stdout (format BINARY, on_error unsupported);
^
+COPY x to stdin (on_error set_to_null);
+ERROR: COPY ON_ERROR cannot be used with COPY TO
+LINE 1: COPY x to stdin (on_error set_to_null);
+ ^
COPY x from stdin (log_verbosity unsupported);
ERROR: COPY LOG_VERBOSITY "unsupported" not recognized
LINE 1: COPY x from stdin (log_verbosity unsupported);
^
COPY x from stdin with (reject_limit 1);
-ERROR: COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE
+ERROR: COPY REJECT_LIMIT requires ON_ERROR to be set to IGNORE or SET_TO_NULL
COPY x from stdin with (on_error ignore, reject_limit 0);
ERROR: REJECT_LIMIT (0) must be greater than zero
-- too many columns in column list: should fail
@@ -769,6 +779,50 @@ CONTEXT: COPY check_ign_err
NOTICE: skipping row due to data type incompatibility at line 8 for column "k": "a"
CONTEXT: COPY check_ign_err
NOTICE: 6 rows were skipped due to data type incompatibility
+CREATE DOMAIN d_int_not_null AS INT NOT NULL CHECK(value > 0);
+CREATE DOMAIN d_int_positive_maybe_null AS INT CHECK(value > 0);
+CREATE TABLE t_on_error_null (a d_int_not_null, b d_int_positive_maybe_null, c INT);
+\pset null NULL
+--ok
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+NOTICE: Erroneous values in 1 row were replaced with NULL
+--ok
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+NOTICE: Erroneous values in 1 row were replaced with NULL
+-- check inserted content
+TABLE t_on_error_null;
+ a | b | c
+----+------+------
+ 11 | NULL | 12
+ 1 | 11 | NULL
+(2 rows)
+
+--fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+ERROR: failed to replace column containing erroneous data with null
+HINT: Consider specifying the REJECT LIMIT option to skip erroneous rows.
+CONTEXT: COPY t_on_error_null, line 1, column a: "a"
+--fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+ERROR: failed to replace column containing erroneous data with null
+HINT: Consider specifying the REJECT LIMIT option to skip erroneous rows.
+CONTEXT: COPY t_on_error_null, line 1, column a: "-1"
+--ok. Check interaction with REJECT LIMIT
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null, reject_limit 2);
+NOTICE: Erroneous values in 2 rows were replaced with NULL
+-- check inserted content
+TABLE t_on_error_null;
+ a | b | c
+----+------+------
+ 11 | NULL | 12
+ 1 | 11 | NULL
+ 1 | 11 | 14
+(3 rows)
+
+--fail. Check interaction with REJECT LIMIT
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null, reject_limit 2);
+ERROR: encountered more than REJECT_LIMIT (2) rows with data type incompatibility
+CONTEXT: COPY t_on_error_null, line 3, column a: null input
-- tests for on_error option with log_verbosity and null constraint via domain
CREATE DOMAIN dcheck_ign_err2 varchar(15) NOT NULL;
CREATE TABLE check_ign_err2 (n int, m int[], k int, l dcheck_ign_err2);
@@ -776,6 +830,26 @@ COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity verbose);
NOTICE: skipping row due to data type incompatibility at line 2 for column "l": null input
CONTEXT: COPY check_ign_err2
NOTICE: 1 row was skipped due to data type incompatibility
+-- check null substitution massages.
+COPY check_ign_err2 FROM STDIN WITH (on_error set_to_null, log_verbosity verbose);
+NOTICE: failed to replace row attribute "l" with NULL at line 1.
+CONTEXT: COPY check_ign_err2, line 1, column l: "'foooooooooooooooo'"
+ERROR: failed to replace column containing erroneous data with null
+HINT: Consider specifying the REJECT LIMIT option to skip erroneous rows.
+CONTEXT: COPY check_ign_err2, line 1, column l: "'foooooooooooooooo'"
+COPY check_ign_err2 FROM STDIN WITH (on_error set_to_null, reject_limit 2, log_verbosity verbose);
+NOTICE: failed to replace row attribute "l" with NULL at line 1.
+CONTEXT: COPY check_ign_err2, line 1, column l: "'foooooooooooooooo'"
+NOTICE: skipping row due to data type incompatibility at line 1 for column "l": "'foooooooooooooooo'"
+CONTEXT: COPY check_ign_err2
+NOTICE: failed to replace row attribute "l" with NULL at line 2.
+CONTEXT: COPY check_ign_err2, line 2, column l: "'foooooooooooooooo'"
+NOTICE: skipping row due to data type incompatibility at line 2 for column "l": "'foooooooooooooooo'"
+CONTEXT: COPY check_ign_err2
+NOTICE: failed to replace row attribute "l" with NULL at line 3.
+CONTEXT: COPY check_ign_err2, line 3, column l: "'foooooooooooooooo'"
+ERROR: encountered more than REJECT_LIMIT (2) rows with data type incompatibility
+CONTEXT: COPY check_ign_err2, line 3, column l: "'foooooooooooooooo'"
COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity silent);
-- reset context choice
\set SHOW_CONTEXT errors
@@ -813,6 +887,28 @@ ERROR: skipped more than REJECT_LIMIT (3) rows due to data type incompatibility
CONTEXT: COPY check_ign_err, line 5, column n: ""
COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 4);
NOTICE: 4 rows were skipped due to data type incompatibility
+-- tests for on_error set_to_null option
+truncate check_ign_err;
+COPY check_ign_err FROM STDIN WITH (on_error set_to_null);
+NOTICE: Erroneous values in 3 rows were replaced with NULL
+SELECT * FROM check_ign_err;
+ n | m | k
+------+-----+------
+ 1 | {1} | NULL
+ 2 | {2} | 1
+ 3 | {3} | 2
+ 4 | {4} | NULL
+ NULL | {5} | NULL
+(5 rows)
+
+--should fail.
+COPY check_ign_err FROM STDIN WITH (delimiter ',', on_error set_to_null);
+ERROR: missing data for column "k"
+CONTEXT: COPY check_ign_err, line 1, column m: ""
+--should fail.
+COPY check_ign_err FROM STDIN WITH (delimiter ',', on_error set_to_null);
+ERROR: extra data after last expected column
+CONTEXT: COPY check_ign_err, line 1: "1,{1},1,1"
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
@@ -828,6 +924,8 @@ DROP VIEW instead_of_insert_tbl_view;
DROP VIEW instead_of_insert_tbl_view_2;
DROP FUNCTION fun_instead_of_insert_tbl();
DROP TABLE check_ign_err;
+DROP TABLE t_on_error_null;
+DROP DOMAIN d_int_not_null;
DROP TABLE check_ign_err2;
DROP DOMAIN dcheck_ign_err2;
DROP TABLE hard_err;
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index 45273557ce0..d1dd61b4bf2 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -67,12 +67,14 @@ COPY x from stdin (force_null (a), force_null (b));
COPY x from stdin (convert_selectively (a), convert_selectively (b));
COPY x from stdin (encoding 'sql_ascii', encoding 'sql_ascii');
COPY x from stdin (on_error ignore, on_error ignore);
+COPY x from stdin (on_error set_to_null, on_error set_to_null);
COPY x from stdin (log_verbosity default, log_verbosity verbose);
-- incorrect options
COPY x from stdin (format BINARY, delimiter ',');
COPY x from stdin (format BINARY, null 'x');
COPY x from stdin (format BINARY, on_error ignore);
+COPY x from stdin (format BINARY, on_error set_to_null);
COPY x from stdin (on_error unsupported);
COPY x from stdin (format TEXT, force_quote(a));
COPY x from stdin (format TEXT, force_quote *);
@@ -87,6 +89,7 @@ COPY x from stdin (format TEXT, force_null *);
COPY x to stdout (format CSV, force_null(a));
COPY x to stdout (format CSV, force_null *);
COPY x to stdout (format BINARY, on_error unsupported);
+COPY x to stdin (on_error set_to_null);
COPY x from stdin (log_verbosity unsupported);
COPY x from stdin with (reject_limit 1);
COPY x from stdin with (on_error ignore, reject_limit 0);
@@ -534,6 +537,52 @@ a {2} 2
8 {8} 8
\.
+CREATE DOMAIN d_int_not_null AS INT NOT NULL CHECK(value > 0);
+CREATE DOMAIN d_int_positive_maybe_null AS INT CHECK(value > 0);
+CREATE TABLE t_on_error_null (a d_int_not_null, b d_int_positive_maybe_null, c INT);
+
+\pset null NULL
+
+--ok
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+11 a 12
+\.
+
+--ok
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+1 11 d
+\.
+
+-- check inserted content
+TABLE t_on_error_null;
+
+--fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+a 11 14
+\.
+
+--fail
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
+-1 11 13
+\.
+
+--ok. Check interaction with REJECT LIMIT
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null, reject_limit 2);
+-1 11 13
+a 11 14
+1 11 14
+\.
+
+-- check inserted content
+TABLE t_on_error_null;
+
+--fail. Check interaction with REJECT LIMIT
+COPY t_on_error_null FROM STDIN WITH (on_error set_to_null, reject_limit 2);
+-1 11 13
+a 11 14
+\N 11 14
+\.
+
-- tests for on_error option with log_verbosity and null constraint via domain
CREATE DOMAIN dcheck_ign_err2 varchar(15) NOT NULL;
CREATE TABLE check_ign_err2 (n int, m int[], k int, l dcheck_ign_err2);
@@ -541,6 +590,15 @@ COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity verbose);
1 {1} 1 'foo'
2 {2} 2 \N
\.
+-- check null substitution massages.
+COPY check_ign_err2 FROM STDIN WITH (on_error set_to_null, log_verbosity verbose);
+2 {2} 2 'foooooooooooooooo'
+\.
+COPY check_ign_err2 FROM STDIN WITH (on_error set_to_null, reject_limit 2, log_verbosity verbose);
+2 {2} 2 'foooooooooooooooo'
+2 {2} 2 'foooooooooooooooo'
+2 {2} 2 'foooooooooooooooo'
+\.
COPY check_ign_err2 FROM STDIN WITH (on_error ignore, log_verbosity silent);
3 {3} 3 'bar'
4 {4} 4 \N
@@ -588,6 +646,28 @@ a {7} 7
10 {10} 10
\.
+-- tests for on_error set_to_null option
+truncate check_ign_err;
+COPY check_ign_err FROM STDIN WITH (on_error set_to_null);
+1 {1} a
+2 {2} 1
+3 {3} 2
+4 {4} b
+a {5} c
+\.
+
+SELECT * FROM check_ign_err;
+
+--should fail.
+COPY check_ign_err FROM STDIN WITH (delimiter ',', on_error set_to_null);
+1,
+\.
+
+--should fail.
+COPY check_ign_err FROM STDIN WITH (delimiter ',', on_error set_to_null);
+1,{1},1,1
+\.
+
-- clean up
DROP TABLE forcetest;
DROP TABLE vistest;
@@ -603,6 +683,8 @@ DROP VIEW instead_of_insert_tbl_view;
DROP VIEW instead_of_insert_tbl_view_2;
DROP FUNCTION fun_instead_of_insert_tbl();
DROP TABLE check_ign_err;
+DROP TABLE t_on_error_null;
+DROP DOMAIN d_int_not_null;
DROP TABLE check_ign_err2;
DROP DOMAIN dcheck_ign_err2;
DROP TABLE hard_err;
--
2.34.1
view thread (29+ 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]
Subject: Re: Change COPY ... ON_ERROR ignore to ON_ERROR ignore_row
In-Reply-To: <CALdSSPh13qiKmhqwj=bR_3seZJVkP9E6BDbLfHXQ_DbaxQL4FA@mail.gmail.com>
* 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