diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index c25b52d0cb..462e4d338b 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -42,6 +42,8 @@ COPY { table_name [ ( column_name [, ...] ) | * } FORCE_NOT_NULL ( column_name [, ...] ) FORCE_NULL ( column_name [, ...] ) + NULL_ON_ERROR [ boolean ] + WARN_ON_ERROR [ boolean ] ENCODING 'encoding_name' @@ -356,6 +358,27 @@ COPY { table_name [ ( + + NULL_ON_ERROR + + + Requests silently replacing any erroneous input values with + NULL. + + + + + + WARN_ON_ERROR + + + Requests replacing any erroneous input values with + NULL, and emitting a warning message instead of + the usual error. + + + + ENCODING diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index db4c9dbc23..d224167111 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -409,6 +409,7 @@ ProcessCopyOptions(ParseState *pstate, bool format_specified = false; bool freeze_specified = false; bool header_specified = false; + bool on_error_specified = false; ListCell *option; /* Support external use for option sanity checking */ @@ -520,6 +521,20 @@ ProcessCopyOptions(ParseState *pstate, defel->defname), parser_errposition(pstate, defel->location))); } + else if (strcmp(defel->defname, "null_on_error") == 0) + { + if (on_error_specified) + errorConflictingDefElem(defel, pstate); + on_error_specified = true; + opts_out->null_on_error = defGetBoolean(defel); + } + else if (strcmp(defel->defname, "warn_on_error") == 0) + { + if (on_error_specified) + errorConflictingDefElem(defel, pstate); + on_error_specified = true; + opts_out->warn_on_error = defGetBoolean(defel); + } else if (strcmp(defel->defname, "convert_selectively") == 0) { /* @@ -701,6 +716,30 @@ ProcessCopyOptions(ParseState *pstate, ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("CSV quote character must not appear in the NULL specification"))); + + /* + * The XXX_ON_ERROR options are only supported for input, and only in text + * modes. We could in future extend safe-errors support to datatype + * receive functions, but it'd take a lot more work. Moreover, it's not + * clear that receive functions can detect errors very well, so the + * feature likely wouldn't work terribly well. + */ + if (opts_out->null_on_error && !is_from) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("COPY NULL_ON_ERROR only available using COPY FROM"))); + if (opts_out->null_on_error && opts_out->binary) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot specify NULL_ON_ERROR in BINARY mode"))); + if (opts_out->warn_on_error && !is_from) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("COPY WARN_ON_ERROR only available using COPY FROM"))); + if (opts_out->warn_on_error && opts_out->binary) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot specify WARN_ON_ERROR in BINARY mode"))); } /* diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c index 504afcb811..16b01b6598 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -1599,6 +1599,15 @@ BeginCopyFrom(ParseState *pstate, } } + /* For the XXX_ON_ERROR options, we'll need an ErrorSaveContext */ + if (cstate->opts.null_on_error || + cstate->opts.warn_on_error) + { + cstate->es_context = makeNode(ErrorSaveContext); + /* Error details are only needed for warnings */ + if (cstate->opts.warn_on_error) + cstate->es_context->details_wanted = true; + } /* initialize progress */ pgstat_progress_start_command(PROGRESS_COMMAND_COPY, diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c index 097414ef12..9cf7d31dd2 100644 --- a/src/backend/commands/copyfromparse.c +++ b/src/backend/commands/copyfromparse.c @@ -876,6 +876,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext, char **field_strings; ListCell *cur; int fldct; + bool safe_mode; int fieldno; char *string; @@ -889,6 +890,8 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext, (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), errmsg("extra data after last expected column"))); + safe_mode = cstate->opts.null_on_error || cstate->opts.warn_on_error; + fieldno = 0; /* Loop to read the user attributes on the line. */ @@ -938,12 +941,50 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext, cstate->cur_attname = NameStr(att->attname); cstate->cur_attval = string; - values[m] = InputFunctionCall(&in_functions[m], - string, - typioparams[m], - att->atttypmod); - if (string != NULL) - nulls[m] = false; + + if (safe_mode) + { + ErrorSaveContext *es_context = cstate->es_context; + + /* Must reset the error_occurred flag each time */ + es_context->error_occurred = false; + + values[m] = InputFunctionCallSafe(&in_functions[m], + string, + typioparams[m], + att->atttypmod, + es_context); + if (es_context->error_occurred) + { + /* nulls[m] is already true */ + if (cstate->opts.warn_on_error) + { + ErrorData *edata = es_context->error_data; + + /* Note that our errcontext callback wasn't used */ + ereport(WARNING, + errcode(edata->sqlerrcode), + errmsg_internal("invalid input for column %s: %s", + cstate->cur_attname, + edata->message), + errcontext("COPY %s, line %llu", + cstate->cur_relname, + (unsigned long long) cstate->cur_lineno)); + } + } + else if (string != NULL) + nulls[m] = false; + } + else + { + values[m] = InputFunctionCall(&in_functions[m], + string, + typioparams[m], + att->atttypmod); + if (string != NULL) + nulls[m] = false; + } + cstate->cur_attname = NULL; cstate->cur_attval = NULL; } diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h index b77b935005..ee38bd0e28 100644 --- a/src/include/commands/copy.h +++ b/src/include/commands/copy.h @@ -57,6 +57,8 @@ typedef struct CopyFormatOptions bool *force_notnull_flags; /* per-column CSV FNN flags */ List *force_null; /* list of column names */ bool *force_null_flags; /* per-column CSV FN flags */ + bool null_on_error; /* replace erroneous inputs with NULL? */ + bool warn_on_error; /* ... and warn about it? */ bool convert_selectively; /* do selective binary conversion? */ List *convert_select; /* list of column names (can be NIL) */ } CopyFormatOptions; diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h index 8d9cc5accd..ee6a11306d 100644 --- a/src/include/commands/copyfrom_internal.h +++ b/src/include/commands/copyfrom_internal.h @@ -16,6 +16,7 @@ #include "commands/copy.h" #include "commands/trigger.h" +#include "nodes/miscnodes.h" /* * Represents the different source cases we need to worry about at @@ -97,6 +98,7 @@ typedef struct CopyFromStateData int *defmap; /* array of default att numbers */ ExprState **defexprs; /* array of default att expressions */ bool volatile_defexprs; /* is any of defexprs volatile? */ + ErrorSaveContext *es_context; /* used for XXX_ON_ERROR options */ List *range_table; ExprState *qualexpr; diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out index 3fad1c52d1..f848ce124d 100644 --- a/src/test/regress/expected/copy.out +++ b/src/test/regress/expected/copy.out @@ -240,3 +240,27 @@ SELECT * FROM header_copytest ORDER BY a; (5 rows) drop table header_copytest; +-- "safe" error handling +create table on_error_copytest(i int, b bool, ai int[]); +copy on_error_copytest from stdin with (null_on_error); +copy on_error_copytest from stdin with (warn_on_error); +WARNING: invalid input for column b: invalid input syntax for type boolean: "b" +WARNING: invalid input for column ai: malformed array literal: "[0:1000]={3,4}" +WARNING: invalid input for column i: invalid input syntax for type integer: "err" +WARNING: invalid input for column i: invalid input syntax for type integer: "bad" +WARNING: invalid input for column b: invalid input syntax for type boolean: "z" +WARNING: invalid input for column ai: invalid input syntax for type integer: "zed" +select * from on_error_copytest; + i | b | ai +---+---+------------- + 1 | | + | t | + 2 | f | {3,4} + | | + 3 | f | [3:4]={3,4} + 4 | | + | t | {} + | | +(8 rows) + +drop table on_error_copytest; diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql index 285022e07c..ff77d27cfc 100644 --- a/src/test/regress/sql/copy.sql +++ b/src/test/regress/sql/copy.sql @@ -268,3 +268,23 @@ a c b SELECT * FROM header_copytest ORDER BY a; drop table header_copytest; + +-- "safe" error handling +create table on_error_copytest(i int, b bool, ai int[]); + +copy on_error_copytest from stdin with (null_on_error); +1 a {1,} +err 1 {x} +2 f {3,4} +bad x {, +\. + +copy on_error_copytest from stdin with (warn_on_error); +3 0 [3:4]={3,4} +4 b [0:1000]={3,4} +err t {} +bad z {"zed"} +\. + +select * from on_error_copytest; +drop table on_error_copytest;