Skip to content

Commit 7ec7378

Browse files
bmomjianbmomjian
committed
copy: update docs for FORCE_NULL and FORCE_NOT_NULL combination
Also update regression tests Patch by Michael Paquier
1 parent 4a5d55e commit 7ec7378

File tree

6 files changed

+24
-34
lines changed

6 files changed

+24
-34
lines changed

‎contrib/file_fdw/file_fdw.c

Lines changed: 0 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -264,11 +264,6 @@ file_fdw_validator(PG_FUNCTION_ARGS)
264264
(errcode(ERRCODE_SYNTAX_ERROR),
265265
errmsg("conflicting or redundant options"),
266266
errhint("option \"force_not_null\" supplied more than once for a column")));
267-
if(force_null)
268-
ereport(ERROR,
269-
(errcode(ERRCODE_SYNTAX_ERROR),
270-
errmsg("conflicting or redundant options"),
271-
errhint("option \"force_not_null\" cannot be used together with \"force_null\"")));
272267
force_not_null = def;
273268
/* Don't care what the value is, as long as it's a legal boolean */
274269
(void) defGetBoolean(def);
@@ -281,11 +276,6 @@ file_fdw_validator(PG_FUNCTION_ARGS)
281276
(errcode(ERRCODE_SYNTAX_ERROR),
282277
errmsg("conflicting or redundant options"),
283278
errhint("option \"force_null\" supplied more than once for a column")));
284-
if(force_not_null)
285-
ereport(ERROR,
286-
(errcode(ERRCODE_SYNTAX_ERROR),
287-
errmsg("conflicting or redundant options"),
288-
errhint("option \"force_null\" cannot be used together with \"force_not_null\"")));
289279
force_null = def;
290280
(void) defGetBoolean(def);
291281
}

‎contrib/file_fdw/input/file_fdw.source

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -91,24 +91,22 @@ ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
9191
\pset null _null_
9292
SELECT * FROM text_csv;
9393

94+
-- force_not_null and force_null can be used together on the same column
95+
ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true');
96+
ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true');
97+
9498
-- force_not_null is not allowed to be specified at any foreign object level:
9599
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
96100
ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
97101
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
98102
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
99103

100-
-- force_not_null cannot be specified together with force_null
101-
ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); --ERROR
102-
103104
-- force_null is not allowed to be specified at any foreign object level:
104105
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
105106
ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR
106107
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR
107108
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
108109

109-
-- force_null cannot be specified together with force_not_null
110-
ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); --ERROR
111-
112110
-- basic query tests
113111
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
114112
SELECT * FROM agg_csv ORDER BY a;

‎contrib/file_fdw/output/file_fdw.source

Lines changed: 3 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -115,6 +115,9 @@ SELECT * FROM text_csv;
115115
ABC | abc | |
116116
(5 rows)
117117

118+
-- force_not_null and force_null can be used together on the same column
119+
ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true');
120+
ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true');
118121
-- force_not_null is not allowed to be specified at any foreign object level:
119122
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
120123
ERROR: invalid option "force_not_null"
@@ -128,10 +131,6 @@ HINT: There are no valid options in this context.
128131
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
129132
ERROR: invalid option "force_not_null"
130133
HINT: Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding
131-
-- force_not_null cannot be specified together with force_null
132-
ALTER FOREIGN TABLE text_csv ALTER COLUMN word1 OPTIONS (force_null 'true'); --ERROR
133-
ERROR: conflicting or redundant options
134-
HINT: option "force_null" cannot be used together with "force_not_null"
135134
-- force_null is not allowed to be specified at any foreign object level:
136135
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
137136
ERROR: invalid option "force_null"
@@ -145,10 +144,6 @@ HINT: There are no valid options in this context.
145144
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
146145
ERROR: invalid option "force_null"
147146
HINT: Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding
148-
-- force_null cannot be specified together with force_not_null
149-
ALTER FOREIGN TABLE text_csv ALTER COLUMN word3 OPTIONS (force_not_null 'true'); --ERROR
150-
ERROR: conflicting or redundant options
151-
HINT: option "force_not_null" cannot be used together with "force_null"
152147
-- basic query tests
153148
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
154149
a | b

‎doc/src/sgml/ref/copy.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -487,6 +487,13 @@ COPY <replaceable class="parameter">count</replaceable>
487487
<command>VACUUM</command> to recover the wasted space.
488488
</para>
489489

490+
<para>
491+
<literal>FORCE_NULL</> and <literal>FORCE_NOT_NULL</> can be used
492+
simultaneously on the same column. This has as result to convert quoted
493+
null strings to null values and to convert unquoted null strings to
494+
empty strings.
495+
</para>
496+
490497
</refsect1>
491498

492499
<refsect1>

‎src/test/regress/expected/copy2.out

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -383,7 +383,6 @@ SELECT * FROM vistest;
383383
(2 rows)
384384

385385
-- Test FORCE_NOT_NULL and FORCE_NULL options
386-
-- should succeed with "b" set to an empty string and "c" set to NULL
387386
CREATE TEMP TABLE forcetest (
388387
a INT NOT NULL,
389388
b TEXT NOT NULL,
@@ -392,6 +391,7 @@ CREATE TEMP TABLE forcetest (
392391
e TEXT
393392
);
394393
\pset null NULL
394+
-- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
395395
BEGIN;
396396
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
397397
COMMIT;
@@ -401,12 +401,12 @@ SELECT b, c FROM forcetest WHERE a = 1;
401401
| NULL
402402
(1 row)
403403

404-
-- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
404+
-- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified
405405
BEGIN;
406-
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
406+
COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d));
407407
COMMIT;
408-
SELECT b, c FROM forcetest WHERE a = 2;
409-
b | c
408+
SELECT c, d FROM forcetest WHERE a = 2;
409+
c | d
410410
---+------
411411
| NULL
412412
(1 row)

‎src/test/regress/sql/copy2.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -271,7 +271,6 @@ SELECT * FROM vistest;
271271
COMMIT;
272272
SELECT * FROM vistest;
273273
-- Test FORCE_NOT_NULL and FORCE_NULL options
274-
-- should succeed with "b" set to an empty string and "c" set to NULL
275274
CREATE TEMP TABLE forcetest (
276275
a INT NOT NULL,
277276
b TEXT NOT NULL,
@@ -280,19 +279,20 @@ CREATE TEMP TABLE forcetest (
280279
e TEXT
281280
);
282281
\pset null NULL
282+
-- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
283283
BEGIN;
284284
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
285285
1,,""
286286
\.
287287
COMMIT;
288288
SELECT b, c FROM forcetest WHERE a = 1;
289-
-- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
289+
-- should succeed, FORCE_NULL and FORCE_NOT_NULL can be both specified
290290
BEGIN;
291-
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(b), FORCE_NULL(c));
292-
2,,""
291+
COPY forcetest (a, b, c, d) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL(c,d), FORCE_NULL(c,d));
292+
2,'a',,""
293293
\.
294294
COMMIT;
295-
SELECT b, c FROM forcetest WHERE a = 2;
295+
SELECT c, d FROM forcetest WHERE a = 2;
296296
-- should fail with not-null constraint violation
297297
BEGIN;
298298
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b), FORCE_NOT_NULL(c));

0 commit comments

Comments
 (0)