hi all. patch updated. simplified the code a lot. idea is same: COPY t_on_error_null FROM STDIN WITH (on_error set_to_null);
If the STDIN number of columns is the same as the target table, then InputFunctionCallSafe call failure will make that column values be null. If the STDIN number of columns is not the same as the target table, then error ERROR: missing data for column \"%s\" ERROR: extra data after last expected column which is status quo.
From d697684b4dc1356172d93179b1e5e157893c3e54 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Fri, 23 Aug 2024 22:26:44 +0800 Subject: [PATCH v3 1/1] on_error set_to_null any data type conversion errors while COPY FROM will set that column value to be NULL. discussion: https://postgr.es/m/CAKFQuwawy1e6YR4S=j+y7pXqg_Dw1WBVrgvf=bp3d1_asfe...@mail.gmail.com --- doc/src/sgml/ref/copy.sgml | 1 + src/backend/commands/copy.c | 2 ++ src/backend/commands/copyfrom.c | 8 ++++-- src/backend/commands/copyfromparse.c | 11 ++++++++ src/include/commands/copy.h | 1 + src/test/regress/expected/copy2.out | 39 ++++++++++++++++++++++++++ src/test/regress/sql/copy2.sql | 41 ++++++++++++++++++++++++++++ 7 files changed, 100 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 1518af8a04..b6bdf45e7e 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -394,6 +394,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable 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>set_to_null</literal> means the input value will set to <literal>null</literal> and continue with the next one. The default is <literal>stop</literal>. </para> <para> diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 3bb579a3a4..e4bd310ae5 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -409,6 +409,8 @@ defGetCopyOnErrorChoice(DefElem *def, ParseState *pstate, bool is_from) 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), diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index 2d3462913e..c1e58e49bc 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -1319,7 +1319,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) ereport(NOTICE, errmsg_plural("%llu row was skipped due to data type incompatibility", @@ -1471,11 +1471,13 @@ 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) cstate->escontext->details_wanted = false; + else if (cstate->opts.on_error == COPY_ON_ERROR_NULL) + cstate->escontext->details_wanted = false; } else cstate->escontext = NULL; diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c index 7efcb89159..6fbe975b51 100644 --- a/src/backend/commands/copyfromparse.c +++ b/src/backend/commands/copyfromparse.c @@ -969,6 +969,17 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext, { Assert(cstate->opts.on_error != COPY_ON_ERROR_STOP); + if (cstate->opts.on_error == COPY_ON_ERROR_NULL) + { + values[m] = (Datum) 0; + nulls[m] = true; + /* + * set error_occurred to false, so next + * InputFunctionCallSafe call behave sane. + */ + cstate->escontext->error_occurred = false; + continue; + } cstate->num_errors++; if (cstate->opts.log_verbosity == COPY_LOG_VERBOSITY_VERBOSE) diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h index 141fd48dc1..fa87232ed7 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 e913f683a6..4d23527106 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -753,6 +753,24 @@ 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 TABLE t_on_error_null (a d_int_not_null, c int not null, b int); +--fail +COPY t_on_error_null FROM STDIN WITH (on_error set_to_null); +ERROR: null value in column "c" of relation "t_on_error_null" violates not-null constraint +DETAIL: Failing row contains (11, null, 12). +CONTEXT: COPY t_on_error_null, line 1: "11 a 12" +--ok +COPY t_on_error_null FROM STDIN WITH (on_error set_to_null); +select * from t_on_error_null; + a | c | b +---+----+---- + | 11 | 13 + | 11 | 14 +(2 rows) + +drop table t_on_error_null; +drop domain d_int_not_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); @@ -789,6 +807,27 @@ CONTEXT: COPY check_ign_err, line 1: "1 {1}" COPY check_ign_err FROM STDIN WITH (on_error ignore); ERROR: extra data after last expected column CONTEXT: COPY check_ign_err, line 1: "1 {1} 3 abc" +truncate check_ign_err; +COPY check_ign_err FROM STDIN WITH (on_error set_to_null); +\pset null 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; diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index 8b14962194..4abc18a6db 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -526,6 +526,24 @@ a {2} 2 8 {8} 8 \. +create domain d_int_not_null as int not null check(value > 0); +CREATE TABLE t_on_error_null (a d_int_not_null, c int not null, b int); + +--fail +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 13 +a 11 14 +\. + +select * from t_on_error_null; +drop table t_on_error_null; +drop domain d_int_not_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); @@ -557,6 +575,29 @@ COPY check_ign_err FROM STDIN WITH (on_error ignore); 1 {1} 3 abc \. + +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 +\. + +\pset null NULL +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; -- 2.34.1
