Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7ec7378

Browse files
committed
copy: update docs for FORCE_NULL and FORCE_NOT_NULL combination
Also update regression testsPatch by Michael Paquier
1 parent4a5d55e commit7ec7378

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-
SELECTb, c FROM forcetest WHERE a = 2;
409-
b |c
408+
SELECTc, 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
aINTNOT NULL,
277276
bTEXTNOT NULL,
@@ -280,19 +279,20 @@ CREATE TEMP TABLE forcetest (
280279
eTEXT
281280
);
282281
\psetnullNULL
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, cFROM forcetestWHERE 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-
SELECTb, cFROM forcetestWHERE a=2;
295+
SELECTc, dFROM forcetestWHERE 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)
[8]ページ先頭
©2009-2025 Movatter.jp