From d697684b4dc1356172d93179b1e5e157893c3e54 Mon Sep 17 00:00:00 2001 From: jian he 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_+Q@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 { table_name [ ( error_action value of stop means fail the command, while ignore means discard the input row and continue with the next one. + set_to_null means the input value will set to null and continue with the next one. The default is stop. 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