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

Commit3b5e03d

Browse files
committed
Provide a FORCE NULL option to COPY in CSV mode.
This forces an input field containing the quoted null string to bereturned as a NULL. Without this option, only unquoted null stringsbehave this way. This helps where some CSV producers insist on quotingevery field, whether or not it is needed. The option takes a list offields, and only applies to those columns. There is an equivalentcolumn-level option added to file_fdw.Ian Barwick, with some tweaking by Andrew Dunstan, reviewed by PayalSingh.
1 parente2a0fc5 commit3b5e03d

File tree

10 files changed

+308
-35
lines changed

10 files changed

+308
-35
lines changed

‎contrib/file_fdw/data/text.csv

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
1-
AAA,aaa
2-
XYZ,xyz
3-
NULL,NULL
4-
ABC,abc
1+
AAA,aaa,123,""
2+
XYZ,xyz,"",321
3+
NULL,NULL,NULL,NULL
4+
NULL,NULL,"NULL",NULL
5+
ABC,abc,"",""

‎contrib/file_fdw/file_fdw.c

Lines changed: 57 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -48,9 +48,9 @@ struct FileFdwOption
4848

4949
/*
5050
* Valid options for file_fdw.
51-
* These options are based on the options for COPY FROM command.
52-
* But note that force_not_nullishandled asabooleanoption attached to
53-
*eachcolumn, not asatableoption.
51+
* These options are based on the options fortheCOPY FROM command.
52+
* But note that force_not_nulland force_null arehandled as booleanoptions
53+
*attached to acolumn, not as tableoptions.
5454
*
5555
* Note: If you are adding new option for user mapping, you need to modify
5656
* fileGetOptions(), which currently doesn't bother to look at user mappings.
@@ -69,7 +69,7 @@ static const struct FileFdwOption valid_options[] = {
6969
{"null",ForeignTableRelationId},
7070
{"encoding",ForeignTableRelationId},
7171
{"force_not_null",AttributeRelationId},
72-
72+
{"force_null",AttributeRelationId},
7373
/*
7474
* force_quote is not supported by file_fdw because it's for COPY TO.
7575
*/
@@ -187,6 +187,7 @@ file_fdw_validator(PG_FUNCTION_ARGS)
187187
Oidcatalog=PG_GETARG_OID(1);
188188
char*filename=NULL;
189189
DefElem*force_not_null=NULL;
190+
DefElem*force_null=NULL;
190191
List*other_options=NIL;
191192
ListCell*cell;
192193

@@ -243,10 +244,10 @@ file_fdw_validator(PG_FUNCTION_ARGS)
243244
}
244245

245246
/*
246-
* Separate out filename and force_not_null, since ProcessCopyOptions
247-
* won't accept them. (force_not_null only comes in a boolean
248-
* per-column flavor here.)
247+
* Separate out filename and column-specific options, since
248+
* ProcessCopyOptions won't accept them.
249249
*/
250+
250251
if (strcmp(def->defname,"filename")==0)
251252
{
252253
if (filename)
@@ -255,16 +256,42 @@ file_fdw_validator(PG_FUNCTION_ARGS)
255256
errmsg("conflicting or redundant options")));
256257
filename=defGetString(def);
257258
}
259+
/*
260+
* force_not_null is a boolean option; after validation we can discard
261+
* it - it will be retrieved later in get_file_fdw_attribute_options()
262+
*/
258263
elseif (strcmp(def->defname,"force_not_null")==0)
259264
{
260265
if (force_not_null)
261266
ereport(ERROR,
262267
(errcode(ERRCODE_SYNTAX_ERROR),
263-
errmsg("conflicting or redundant options")));
268+
errmsg("conflicting or redundant options"),
269+
errhint("option \"force_not_null\" supplied more than once for a column")));
270+
if(force_null)
271+
ereport(ERROR,
272+
(errcode(ERRCODE_SYNTAX_ERROR),
273+
errmsg("conflicting or redundant options"),
274+
errhint("option \"force_not_null\" cannot be used together with \"force_null\"")));
264275
force_not_null=def;
265276
/* Don't care what the value is, as long as it's a legal boolean */
266277
(void)defGetBoolean(def);
267278
}
279+
/* See comments for force_not_null above */
280+
elseif (strcmp(def->defname,"force_null")==0)
281+
{
282+
if (force_null)
283+
ereport(ERROR,
284+
(errcode(ERRCODE_SYNTAX_ERROR),
285+
errmsg("conflicting or redundant options"),
286+
errhint("option \"force_null\" supplied more than once for a column")));
287+
if(force_not_null)
288+
ereport(ERROR,
289+
(errcode(ERRCODE_SYNTAX_ERROR),
290+
errmsg("conflicting or redundant options"),
291+
errhint("option \"force_null\" cannot be used together with \"force_not_null\"")));
292+
force_null=def;
293+
(void)defGetBoolean(def);
294+
}
268295
else
269296
other_options=lappend(other_options,def);
270297
}
@@ -369,8 +396,9 @@ fileGetOptions(Oid foreigntableid,
369396
* Retrieve per-column generic options from pg_attribute and construct a list
370397
* of DefElems representing them.
371398
*
372-
* At the moment we only have "force_not_null", which should be combined into
373-
* a single DefElem listing all such columns, since that's what COPY expects.
399+
* At the moment we only have "force_not_null", and "force_null",
400+
* which should each be combined into a single DefElem listing all such
401+
* columns, since that's what COPY expects.
374402
*/
375403
staticList*
376404
get_file_fdw_attribute_options(Oidrelid)
@@ -380,6 +408,9 @@ get_file_fdw_attribute_options(Oid relid)
380408
AttrNumbernatts;
381409
AttrNumberattnum;
382410
List*fnncolumns=NIL;
411+
List*fncolumns=NIL;
412+
413+
List*options=NIL;
383414

384415
rel=heap_open(relid,AccessShareLock);
385416
tupleDesc=RelationGetDescr(rel);
@@ -410,17 +441,29 @@ get_file_fdw_attribute_options(Oid relid)
410441
fnncolumns=lappend(fnncolumns,makeString(attname));
411442
}
412443
}
444+
elseif (strcmp(def->defname,"force_null")==0)
445+
{
446+
if (defGetBoolean(def))
447+
{
448+
char*attname=pstrdup(NameStr(attr->attname));
449+
450+
fncolumns=lappend(fncolumns,makeString(attname));
451+
}
452+
}
413453
/* maybe in future handle other options here */
414454
}
415455
}
416456

417457
heap_close(rel,AccessShareLock);
418458

419-
/* Return DefElem only when some column(s) have force_not_null */
459+
/* Return DefElem only when some column(s) have force_not_null/ force_null options set*/
420460
if (fnncolumns!=NIL)
421-
returnlist_make1(makeDefElem("force_not_null", (Node*)fnncolumns));
422-
else
423-
returnNIL;
461+
options=lappend(options,makeDefElem("force_not_null", (Node*)fnncolumns));
462+
463+
if (fncolumns!=NIL)
464+
options=lappend(options,makeDefElem("force_null", (Node*)fncolumns));
465+
466+
returnoptions;
424467
}
425468

426469
/*

‎contrib/file_fdw/input/file_fdw.source

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -81,11 +81,14 @@ OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', deli
8181
-- per-column options tests
8282
CREATE FOREIGN TABLE text_csv (
8383
word1 text OPTIONS (force_not_null 'true'),
84-
word2 text OPTIONS (force_not_null 'off')
84+
word2 text OPTIONS (force_not_null 'off'),
85+
word3 text OPTIONS (force_null 'true'),
86+
word4 text OPTIONS (force_null 'off')
8587
) SERVER file_server
8688
OPTIONS (format 'text', filename '@abs_srcdir@/data/text.csv', null 'NULL');
8789
SELECT * FROM text_csv; -- ERROR
8890
ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
91+
\pset null _null_
8992
SELECT * FROM text_csv;
9093

9194
-- force_not_null is not allowed to be specified at any foreign object level:
@@ -94,6 +97,18 @@ ALTER SERVER file_server OPTIONS (ADD force_not_null '*'); -- ERROR
9497
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
9598
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
9699

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+
103+
-- force_null is not allowed to be specified at any foreign object level:
104+
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
105+
ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR
106+
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR
107+
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
108+
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+
97112
-- basic query tests
98113
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
99114
SELECT * FROM agg_csv ORDER BY a;

‎contrib/file_fdw/output/file_fdw.source

Lines changed: 33 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -96,20 +96,24 @@ OPTIONS (format 'csv', filename '@abs_srcdir@/data/agg.bad', header 'true', deli
9696
-- per-column options tests
9797
CREATE FOREIGN TABLE text_csv (
9898
word1 text OPTIONS (force_not_null 'true'),
99-
word2 text OPTIONS (force_not_null 'off')
99+
word2 text OPTIONS (force_not_null 'off'),
100+
word3 text OPTIONS (force_null 'true'),
101+
word4 text OPTIONS (force_null 'off')
100102
) SERVER file_server
101103
OPTIONS (format 'text', filename '@abs_srcdir@/data/text.csv', null 'NULL');
102104
SELECT * FROM text_csv; -- ERROR
103105
ERROR: COPY force not null available only in CSV mode
104106
ALTER FOREIGN TABLE text_csv OPTIONS (SET format 'csv');
107+
\pset null _null_
105108
SELECT * FROM text_csv;
106-
word1 | word2
107-
-------+-------
108-
AAA | aaa
109-
XYZ | xyz
110-
NULL |
111-
ABC | abc
112-
(4 rows)
109+
word1 | word2 | word3 | word4
110+
-------+--------+--------+--------
111+
AAA | aaa | 123 |
112+
XYZ | xyz | | 321
113+
NULL | _null_ | _null_ | _null_
114+
NULL | _null_ | _null_ | _null_
115+
ABC | abc | |
116+
(5 rows)
113117

114118
-- force_not_null is not allowed to be specified at any foreign object level:
115119
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_not_null '*'); -- ERROR
@@ -124,6 +128,27 @@ HINT: There are no valid options in this context.
124128
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_not_null '*'); -- ERROR
125129
ERROR: invalid option "force_not_null"
126130
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"
135+
-- force_null is not allowed to be specified at any foreign object level:
136+
ALTER FOREIGN DATA WRAPPER file_fdw OPTIONS (ADD force_null '*'); -- ERROR
137+
ERROR: invalid option "force_null"
138+
HINT: There are no valid options in this context.
139+
ALTER SERVER file_server OPTIONS (ADD force_null '*'); -- ERROR
140+
ERROR: invalid option "force_null"
141+
HINT: There are no valid options in this context.
142+
CREATE USER MAPPING FOR public SERVER file_server OPTIONS (force_null '*'); -- ERROR
143+
ERROR: invalid option "force_null"
144+
HINT: There are no valid options in this context.
145+
CREATE FOREIGN TABLE tbl () SERVER file_server OPTIONS (force_null '*'); -- ERROR
146+
ERROR: invalid option "force_null"
147+
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"
127152
-- basic query tests
128153
SELECT * FROM agg_text WHERE b > 10.0 ORDER BY a;
129154
a | b

‎doc/src/sgml/file-fdw.sgml

Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -112,11 +112,11 @@
112112
</variablelist>
113113

114114
<para>
115-
Note that while <command>COPY</> allows options such as OIDS and HEADER
115+
Note that while <command>COPY</> allows options such as OIDS and HEADER
116116
to be specified without a corresponding value, the foreign data wrapper
117-
syntax requires a value to be present in all cases. To activate
117+
syntax requires a value to be present in all cases. To activate
118118
<command>COPY</> options normally supplied without a value, you can
119-
instead pass the value TRUE.
119+
instead pass the value TRUE.
120120
</para>
121121

122122
<para>
@@ -140,6 +140,21 @@
140140
</listitem>
141141
</varlistentry>
142142

143+
<varlistentry>
144+
<term><literal>force_null</literal></term>
145+
146+
<listitem>
147+
<para>
148+
This is a Boolean option. If true, it specifies that values of the
149+
column which match the null string are returned as <literal>NULL</>
150+
even if the value is quoted. Without this option, only unquoted
151+
values matching the null string are returned as <literal>NULL</>.
152+
This has the same effect as listing the column in
153+
<command>COPY</>'s <literal>FORCE_NULL</literal> option.
154+
</para>
155+
</listitem>
156+
</varlistentry>
157+
143158
</variablelist>
144159

145160
<para>

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

Lines changed: 18 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
4242
ESCAPE '<replaceable class="parameter">escape_character</replaceable>'
4343
FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
4444
FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
45+
FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
4546
ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
4647
</synopsis>
4748
</refsynopsisdiv>
@@ -328,6 +329,20 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
328329
</listitem>
329330
</varlistentry>
330331

332+
<varlistentry>
333+
<term><literal>FORCE_NULL</></term>
334+
<listitem>
335+
<para>
336+
Match the specified columns' values against the null string, even
337+
if it has been quoted, and if a match is found set the value to
338+
<literal>NULL</>. In the default case where the null string is empty,
339+
this converts a quoted empty string into NULL.
340+
This option is allowed only in <command>COPY FROM</>, and only when
341+
using <literal>CSV</> format.
342+
</para>
343+
</listitem>
344+
</varlistentry>
345+
331346
<varlistentry>
332347
<term><literal>ENCODING</></term>
333348
<listitem>
@@ -637,7 +652,9 @@ COPY <replaceable class="parameter">count</replaceable>
637652
string, while an empty string data value is written with double quotes
638653
(<literal>""</>). Reading values follows similar rules. You can
639654
use <literal>FORCE_NOT_NULL</> to prevent <literal>NULL</> input
640-
comparisons for specific columns.
655+
comparisons for specific columns. You can also use
656+
<literal>FORCE_NULL</> to convert quoted null string data values to
657+
<literal>NULL</>.
641658
</para>
642659

643660
<para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp