On Tue, Apr 8, 2025 at 6:42 AM Masahiko Sawada <sawada.m...@gmail.com> wrote: > > > BTW have you measured the overheads of calling InputFunctionCallSafe > twice? If it's significant, we might want to find other ways to > achieve it as it would not be good to incur overhead just for > relatively rare cases. >
Please check the attached two patches
v17-0001-COPY-on_error-set_null.original,
v17-0001-COPY-on_error-set_null.patch
for non-domain types, (on_error set_null), the performance of these
two are the same.
for domain type with or without constraint,
(on_error set_null): v17.original is slower than v17.patch.
test script:
create unlogged table t2(a text);
insert into t2 select 'a' from generate_Series(1, 10_000_000) g;
copy t2 to '/tmp/2.txt';
CREATE DOMAIN d1 AS INT ;
CREATE DOMAIN d2 AS INT check (value > 0);
create unlogged table t3(a int);
create unlogged table t4(a d1);
create unlogged table t5(a d2);
performance result:
v17-0001-COPY-on_error-set_null.patch
-- 764.903 ms
copy t3 from '/tmp/2.txt' (on_error set_null) \watch c=10 i=0.1
-- 779.253 ms
copy t4 from '/tmp/2.txt' (on_error set_null) \watch c=10 i=0.1
-- Time: 750.390 ms
copy t5 from '/tmp/2.txt' (on_error set_null) \watch c=10 i=0.1
v17-0001-COPY-on_error-set_null.original
-- 774.943 ms
copy t3 from '/tmp/2.txt' (on_error set_null) \watch c=10 i=0.1
-- 867.671 ms
copy t4 from '/tmp/2.txt' (on_error set_null) \watch c=10 i=0.1
-- 927.685 ms
copy t5 from '/tmp/2.txt' (on_error set_null) \watch c=10 i=0.1
> Here are some comments:
>
> + if (InputFunctionCallSafe(&in_functions[m],
> + NULL,
> + typioparams[m],
> + att->atttypmod,
> + NULL,
> + &values[m]))
>
> Given that we pass NULL to escontext, does this function return false
> in an error case? Or can we use InputFunctionCall instead?
>
> I think we should mention that SET_NULL still could fail if the data
> type of the column doesn't accept NULL.
>
> How about restructuring the codes around handling data incompatibility
> errors like:
>
> else if (!InputFunctionCallSafe(...))
> {
> if (cstate->opts.on_error == IGNORE)
> {
> cstate->num_errors++;
> if (cstate->opts.log_verbosity == VERBOSE)
> write a NOTICE message;
> return true; // ignore whole row.
> }
> else if (cstate->opts.on_error == SET_NULL)
> {
> current_row_erroneous = true;
> set NULL to the column;
> if (cstate->opts.log_verbosity == VERBOSE)
> write a NOTICE message;
> continue; // go to the next column.
> }
>
> That way, we have similar structures for both on_error handling and
> don't need to reset cstate->cur_attname at the end of SET_NULL
> handling.
>
I think we still need to reset cstate->cur_attname.
the current code structure is
``
foreach(cur, cstate->attnumlist)
{
if (condition x)
continue;
cstate->cur_attname = NULL;
cstate->cur_attval = NULL;
}
``
In some cases (last column , condition x is satisfied), once we reach
the ``continue``, then we cannot reach.
``
cstate->cur_attname = NULL;
cstate->cur_attval = NULL;
``
> ---
> From the regression tests:
>
> --fail, column a is domain with not-null constraint
> COPY t_on_error_null FROM STDIN WITH (on_error set_null);
> a 11 14
> \.
> ERROR: domain d_int_not_null does not allow null values
> CONTEXT: COPY t_on_error_null, line 1, column a: "a"
>
> I guess that the log messages could confuse users since while the
> actual error was caused by setting NULL to the non-NULL domain type
> column, the context message says the data 'a' was erroneous.
>
if the second function is InputFunctionCall, then we cannot customize
the error message.
we can't have both.
I guess we need a second InputFunctionCallSafe with escontext NOT NULL.
now i change it to
if (!cstate->domain_with_constraint[m] ||
InputFunctionCallSafe(&in_functions[m],
NULL,
typioparams[m],
att->atttypmod,
(Node *) cstate->escontext,
&values[m]))
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));
do these ``ELSE IF``, ``ELSE`` error report messages make sense to you?
v17-0001-COPY-on_error-set_null.original
Description: Binary data
From 660389d38a84275a62e497b676c388c063374909 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Tue, 8 Apr 2025 15:07:55 +0800 Subject: [PATCH v17 1/1] COPY (on_error set_null) Extent "on_error action", introduce new option: on_error set_null. Current grammar makes us unable to use "on_error null". if we did it, then in all the COPY command options's value, null will become reserved to non-reserved words. so we choose "on_error set_null". Any data type conversion errors during the COPY FROM process will result in the affected column being set to NULL. This only applies when using the non-binary format for COPY FROM. However, the not-null constraint will still be enforced. If a column has a not-null constraint, successful (on_error set_null) action will cause not-null constraint violation. This also applies to column type is domain with not-null constraint. A regression test for a domain with a not-null constraint has been added. Author: Jian He <jian.universal...@gmail.com> Author: Kirill Reshke <reshkekir...@gmail.com> Reviewed-by: Fujii Masao <masao.fu...@oss.nttdata.com> Jim Jones <jim.jo...@uni-muenster.de> "David G. Johnston" <david.g.johns...@gmail.com> Yugo NAGATA <nag...@sraoss.co.jp> torikoshia <torikos...@oss.nttdata.com> Masahiko Sawada <sawada.m...@gmail.com> discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=bp3d1_asfe...@mail.gmail.com --- doc/src/sgml/ref/copy.sgml | 36 +++++-- src/backend/commands/copy.c | 6 +- src/backend/commands/copyfrom.c | 42 ++++++-- src/backend/commands/copyfromparse.c | 130 ++++++++++++++++++----- src/bin/psql/tab-complete.in.c | 2 +- src/include/commands/copy.h | 1 + src/include/commands/copyfrom_internal.h | 6 ++ src/test/regress/expected/copy2.out | 60 +++++++++++ src/test/regress/sql/copy2.sql | 46 ++++++++ 9 files changed, 277 insertions(+), 52 deletions(-) diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index d6859276bed..db112867fa0 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -394,23 +394,36 @@ 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_null</literal> means replace columns containing invalid + input values with <literal>NULL</literal> and move 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. - When it is set to <literal>silent</literal>, no message is emitted - regarding ignored rows. + 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 input conversion failed rows. </para> </listitem> </varlistentry> @@ -458,7 +471,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 74ae42b19a7..f963d0e51ff 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_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), @@ -918,7 +920,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 fbbbc09a97b..750d597d4d0 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -1467,14 +1467,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); @@ -1614,6 +1622,19 @@ BeginCopyFrom(ParseState *pstate, } } + 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); + } + } + /* Set up soft error handler for ON_ERROR */ if (cstate->opts.on_error != COPY_ON_ERROR_STOP) { @@ -1622,10 +1643,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_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 diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c index f5fc346e201..e638d32e8f5 100644 --- a/src/backend/commands/copyfromparse.c +++ b/src/backend/commands/copyfromparse.c @@ -947,6 +947,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); @@ -1024,7 +1025,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, @@ -1035,47 +1037,119 @@ CopyFromTextLikeOneRow(CopyFromState cstate, ExprContext *econtext, { Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP); - cstate->num_errors++; - - if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE) + if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL) { /* - * Since we emit line number and column info in the below - * notice message, we suppress error context information other - * than the relation name. - */ - Assert(!cstate->relname_only); - cstate->relname_only = true; + * we use it to count number of rows (not fields!) that + * successfully applied on_error set_null. + */ + if (!current_row_erroneous) + current_row_erroneous = true; - if (cstate->cur_attval) + cstate->escontext->error_occurred = false; + Assert(cstate->domain_with_constraint != NULL); + + /* + * when column type is domain with constraints, we may + * need another InputFunctionCallSafe to error out domain + * constraint violation. + */ + if (!cstate->domain_with_constraint[m] || + InputFunctionCallSafe(&in_functions[m], + NULL, + typioparams[m], + att->atttypmod, + (Node *) cstate->escontext, + &values[m])) { - 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)); - pfree(attval); + nulls[m] = true; + values[m] = (Datum) 0; + + if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE) + { + char *attval; + + /* + * Since we emit line number and column info in the below + * notice message, we suppress error context information other + * than the relation name. + */ + Assert(!cstate->relname_only); + Assert(cstate->cur_attval); + + cstate->relname_only = true; + attval = CopyLimitPrintoutLength(cstate->cur_attval); + 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); + + /* reset relname_only */ + cstate->relname_only = false; + } + + cstate->cur_attname = NULL; + continue; } + 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(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; + ereport(ERROR, + errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input value for domain %s: \"%s\"", + format_type_be(typioparams[m]), string)); } + else if (cstate->opts.on_error == COPY_ON_ERROR_IGNORE) + { + cstate->num_errors++; + + if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE) + { + /* + * Since we emit line number and column info in the below + * notice message, we suppress error context information other + * than the relation name. + */ + Assert(!cstate->relname_only); + cstate->relname_only = true; + + if (cstate->cur_attval) + { + char *attval; - return true; + 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)); + 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)); + + /* reset relname_only */ + cstate->relname_only = false; + } + return true; + } } cstate->cur_attname = NULL; cstate->cur_attval = NULL; } + if (current_row_erroneous) + cstate->num_errors++; + Assert(fieldno == attr_count); return true; diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index c916b9299a8..8e6f4930919 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -3291,7 +3291,7 @@ match_previous_words(int pattern_id, /* Complete COPY <sth> FROM filename WITH (ON_ERROR */ else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "ON_ERROR")) - COMPLETE_WITH("stop", "ignore"); + COMPLETE_WITH("stop", "ignore", "set_null"); /* Complete COPY <sth> FROM filename WITH (LOG_VERBOSITY */ else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "LOG_VERBOSITY")) diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h index 06dfdfef721..935d21ee77a 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_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 c8b22af22d8..b427e71b9b3 100644 --- a/src/include/commands/copyfrom_internal.h +++ b/src/include/commands/copyfrom_internal.h @@ -108,6 +108,12 @@ typedef struct CopyFromStateData * att */ bool *defaults; /* if DEFAULT marker was found for * corresponding att */ + /* + * Set to true if the corresponding att data type is domain with constraint. + * normally this field is NULL, except when on_error is specified as SET_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 64ea33aeae8..3f843d1cd5c 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 ignore); +ERROR: conflicting or redundant options +LINE 1: COPY x from stdin (on_error set_null, on_error ignore); + ^ 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); @@ -769,6 +781,51 @@ 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 +--fail, column a cannot set to null value +COPY t_on_error_null FROM STDIN WITH (on_error set_null); +ERROR: domain d_int_not_null does not allow null values +CONTEXT: COPY t_on_error_null, line 1, column a: null input +--fail, column a is domain with not-null constraint +COPY t_on_error_null FROM STDIN WITH (on_error set_null); +ERROR: invalid input value for domain d_int_not_null: "ss" +CONTEXT: COPY t_on_error_null, line 1, column a: "ss" +--fail, column a cannot set to null value +COPY t_on_error_null FROM STDIN WITH (on_error set_null); +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" +--ok +COPY t_on_error_null FROM STDIN WITH (on_error set_null, log_verbosity verbose); +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 +-- check inserted content +select * from t_on_error_null; + 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); @@ -828,6 +885,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 45273557ce0..d77a06668e8 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 ignore); 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); @@ -534,6 +538,45 @@ 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 +--fail, column a cannot set to null value +COPY t_on_error_null FROM STDIN WITH (on_error set_null); +\N 11 13 +\. + +--fail, column a is domain with not-null constraint +COPY t_on_error_null FROM STDIN WITH (on_error set_null); +ss 11 14 +\. + +--fail, column a cannot set to null value +COPY t_on_error_null FROM STDIN WITH (on_error set_null); +-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 +\. + +--ok +COPY t_on_error_null FROM STDIN WITH (on_error set_null, log_verbosity verbose); +10 x1 yx +11 zx 12 +13 14 ea +\. + +-- check inserted content +select * from t_on_error_null; +\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); @@ -603,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
