On Thu, Mar 6, 2014 at 12:09 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Andrew Dunstan <and...@dunslane.net> writes:
>> On 03/05/2014 09:11 AM, Michael Paquier wrote:
>>> After testing this feature, I noticed that FORCE_NULL and
>>> FORCE_NOT_NULL can both be specified with COPY on the same column.
>
>> Strictly they are not actually contradictory, since FORCE NULL relates
>> to quoted null strings and FORCE NOT NULL relates to unquoted null
>> strings. Arguably the docs are slightly loose on this point. Still,
>> applying both FORCE NULL and FORCE NOT NULL to the same column would be
>> rather perverse, since it would result in a quoted null string becoming
>> null and an unquoted null string becoming not null.
>
> Given the remarkable lack of standardization of "CSV" output, who's
> to say that there might not be data sources out there for which this
> is the desired behavior? It's weird, I agree, but I think throwing
> an error for the combination is not going to be helpful. It's not
> like somebody might accidentally write both on the same column.
>
> +1 for clarifying the docs, though, more or less in the words you
> used above.
Following that, I have hacked the patch attached to update the docs
with an additional regression test (actually replaces a test that was
the same as the one before in copy2).
I am attaching as well a second patch for file_fdw, to allow the use
of force_null and force_not_null on the same column, to be consistent
with COPY.
Regards,
--
Michael
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 7fb1dbc..97a35d0 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -267,11 +267,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);
@@ -284,11 +279,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 0c278aa..b608372 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 2bec160..bc183b8 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 5be3514..13cd528 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -477,6 +477,13 @@ COPY <replaceable class="parameter">count</replaceable>
<command>VACUUM</command> to recover the wasted space.
</para>
+ <para>
+ <literal>FORCE_NULL</> and <literal>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.
+ </para>
+
</refsect1>
<refsect1>
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 76dea28..035d843 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 e2be21f..248055f 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));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
