From 7ec73783d88a743799b0c262f1235f772497fb1d Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 22 Apr 2014 16:06:37 -0400 Subject: [PATCH] copy: update docs for FORCE_NULL and FORCE_NOT_NULL combination Also update regression tests Patch by Michael Paquier --- contrib/file_fdw/file_fdw.c | 10 ---------- contrib/file_fdw/input/file_fdw.source | 10 ++++------ contrib/file_fdw/output/file_fdw.source | 11 +++-------- doc/src/sgml/ref/copy.sgml | 7 +++++++ src/test/regress/expected/copy2.out | 10 +++++----- src/test/regress/sql/copy2.sql | 10 +++++----- 6 files changed, 24 insertions(+), 34 deletions(-) diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c index 4f84e3bcea..476e520301 100644 --- a/contrib/file_fdw/file_fdw.c +++ b/contrib/file_fdw/file_fdw.c @@ -264,11 +264,6 @@ file_fdw_validator(PG_FUNCTION_ARGS) (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"), errhint("option \"force_not_null\" supplied more than once for a column"))); - if(force_null) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("conflicting or redundant options"), - errhint("option \"force_not_null\" cannot be used together with \"force_null\""))); force_not_null = def; /* Don't care what the value is, as long as it's a legal boolean */ (void) defGetBoolean(def); @@ -281,11 +276,6 @@ file_fdw_validator(PG_FUNCTION_ARGS) (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"), errhint("option \"force_null\" supplied more than once for a column"))); - if(force_not_null) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("conflicting or redundant options"), - errhint("option \"force_null\" cannot be used together with \"force_not_null\""))); force_null = def; (void) defGetBoolean(def); } diff --git a/contrib/file_fdw/input/file_fdw.source b/contrib/file_fdw/input/file_fdw.source index 0c278aac80..b608372825 100644 --- a/contrib/file_fdw/input/file_fdw.source +++ b/contrib/file_fdw/input/file_fdw.source @@ -91,24 +91,22 @@ ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv'); \pset null _null_ SELECT * FROM text_csv; +-- force_not_null and force_null can be used together on the same column +ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); +ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); + -- force_not_null is not allowed to be specified at any foreign object level: ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR --- force_not_null cannot be specified together with force_null -ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); --ERROR - -- force_null is not allowed to be specified at any foreign object level: ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR --- force_null cannot be specified together with force_not_null -ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); --ERROR - -- basic query tests SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a; SELECT * FROM agg_csv ORDER BY a; diff --git a/contrib/file_fdw/output/file_fdw.source b/contrib/file_fdw/output/file_fdw.source index 2bec160887..bc183b8874 100644 --- a/contrib/file_fdw/output/file_fdw.source +++ b/contrib/file_fdw/output/file_fdw.source @@ -115,6 +115,9 @@ SELECT * FROM text_csv; ABC | abc | | (5 rows) +-- force_not_null and force_null can be used together on the same column +ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); +ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); -- force_not_null is not allowed to be specified at any foreign object level: ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR ERROR: invalid option "force_not_null" @@ -128,10 +131,6 @@ HINT: There are no valid options in this context. CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR ERROR: invalid option "force_not_null" HINT: Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding --- force_not_null cannot be specified together with force_null -ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); --ERROR -ERROR: conflicting or redundant options -HINT: option "force_null" cannot be used together with "force_not_null" -- force_null is not allowed to be specified at any foreign object level: ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR ERROR: invalid option "force_null" @@ -145,10 +144,6 @@ HINT: There are no valid options in this context. CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR ERROR: invalid option "force_null" HINT: Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding --- force_null cannot be specified together with force_not_null -ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); --ERROR -ERROR: conflicting or redundant options -HINT: option "force_not_null" cannot be used together with "force_null" -- basic query tests SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a; a | b diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 0544c68bbc..b100f8bdf7 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -487,6 +487,13 @@ COPY count VACUUM to recover the wasted space. + + FORCE_NULL and FORCE_NOT_NULL can be used + simultaneously on the same column. This has as result to convert quoted + null strings to null values and to convert unquoted null strings to + empty strings. + + diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 76dea287e2..035d843c39 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -383,7 +383,6 @@ SELECT * FROM vistest; (2 rows) -- Test FORCE_NOT_NULL and FORCE_NULL options --- should succeed with "b" set to an empty string and "c" set to NULL CREATE TEMP TABLE forcetest ( a INT NOT NULL, b TEXT NOT NULL, @@ -392,6 +391,7 @@ CREATE TEMP TABLE forcetest ( e TEXT ); \pset null NULL +-- should succeed with no effect ("b" remains an empty string, "c" remains NULL) BEGIN; COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); COMMIT; @@ -401,12 +401,12 @@ SELECT b, c FROM forcetest WHERE a = 1; | NULL (1 row) --- should succeed with no effect ("b" remains an empty string, "c" remains NULL) +-- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified BEGIN; -COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); +COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d)); COMMIT; -SELECT b, c FROM forcetest WHERE a = 2; - b | c +SELECT c, d FROM forcetest WHERE a = 2; + c | d ---+------ | NULL (1 row) diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index e2be21f526..248055f506 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -271,7 +271,6 @@ SELECT * FROM vistest; COMMIT; SELECT * FROM vistest; -- Test FORCE_NOT_NULL and FORCE_NULL options --- should succeed with "b" set to an empty string and "c" set to NULL CREATE TEMP TABLE forcetest ( a INT NOT NULL, b TEXT NOT NULL, @@ -280,19 +279,20 @@ CREATE TEMP TABLE forcetest ( e TEXT ); \pset null NULL +-- should succeed with no effect ("b" remains an empty string, "c" remains NULL) BEGIN; COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); 1,,"" \. COMMIT; SELECT b, c FROM forcetest WHERE a = 1; --- should succeed with no effect ("b" remains an empty string, "c" remains NULL) +-- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified BEGIN; -COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c)); -2,,"" +COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d)); +2,'a',,"" \. COMMIT; -SELECT b, c FROM forcetest WHERE a = 2; +SELECT c, d FROM forcetest WHERE a = 2; -- should fail with not-null constraint violation BEGIN; COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b), FORCE_NOT_NULL(c)); -- 2.40.0