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

Commita3b7c1f

Browse files
adunstanpull[bot]
authored andcommitted
Provide FORCE_NULL * and FORCE_NOT_NULL * options for COPY FROM
These options already exist, but you need to specify a column list forthem, which can be cumbersome. We already have the possibility of allcolumns for FORCE QUOTE, so this is simply extending that facility toFORCE_NULL and FORCE_NOT_NULL.Author: Zhang MingliReviewed-By: Richard Guo, Kyatoro Horiguchi, Michael Paquier.Discussion:https://postgr.es/m/CACJufxEnVqzOFtqhexF2+AwOKFrV8zHOY3y=p+gPK6eB14pn_w@mail.gmail.com
1 parentf5a6a77 commita3b7c1f

File tree

8 files changed

+103
-12
lines changed

8 files changed

+103
-12
lines changed

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

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -41,8 +41,8 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
4141
QUOTE '<replaceable class="parameter">quote_character</replaceable>'
4242
ESCAPE '<replaceable class="parameter">escape_character</replaceable>'
4343
FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
44-
FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
45-
FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
44+
FORCE_NOT_NULL{( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
45+
FORCE_NULL{( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
4646
ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
4747
</synopsis>
4848
</refsynopsisdiv>
@@ -350,6 +350,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
350350
In the default case where the null string is empty, this means that
351351
empty values will be read as zero-length strings rather than nulls,
352352
even when they are not quoted.
353+
If <literal>*</literal> is specified, the option will be applied to all columns.
353354
This option is allowed only in <command>COPY FROM</command>, and only when
354355
using <literal>CSV</literal> format.
355356
</para>
@@ -364,6 +365,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
364365
if it has been quoted, and if a match is found set the value to
365366
<literal>NULL</literal>. In the default case where the null string is empty,
366367
this converts a quoted empty string into NULL.
368+
If <literal>*</literal> is specified, the option will be applied to all columns.
367369
This option is allowed only in <command>COPY FROM</command>, and only when
368370
using <literal>CSV</literal> format.
369371
</para>

‎src/backend/commands/copy.c‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -512,9 +512,11 @@ ProcessCopyOptions(ParseState *pstate,
512512
}
513513
elseif (strcmp(defel->defname,"force_not_null")==0)
514514
{
515-
if (opts_out->force_notnull)
515+
if (opts_out->force_notnull||opts_out->force_notnull_all)
516516
errorConflictingDefElem(defel,pstate);
517-
if (defel->arg&&IsA(defel->arg,List))
517+
if (defel->arg&&IsA(defel->arg,A_Star))
518+
opts_out->force_notnull_all= true;
519+
elseif (defel->arg&&IsA(defel->arg,List))
518520
opts_out->force_notnull=castNode(List,defel->arg);
519521
else
520522
ereport(ERROR,
@@ -525,9 +527,11 @@ ProcessCopyOptions(ParseState *pstate,
525527
}
526528
elseif (strcmp(defel->defname,"force_null")==0)
527529
{
528-
if (opts_out->force_null)
530+
if (opts_out->force_null||opts_out->force_null_all)
529531
errorConflictingDefElem(defel,pstate);
530-
if (defel->arg&&IsA(defel->arg,List))
532+
if (defel->arg&&IsA(defel->arg,A_Star))
533+
opts_out->force_null_all= true;
534+
elseif (defel->arg&&IsA(defel->arg,List))
531535
opts_out->force_null=castNode(List,defel->arg);
532536
else
533537
ereport(ERROR,

‎src/backend/commands/copyfrom.c‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1393,7 +1393,9 @@ BeginCopyFrom(ParseState *pstate,
13931393

13941394
/* Convert FORCE_NOT_NULL name list to per-column flags, check validity */
13951395
cstate->opts.force_notnull_flags= (bool*)palloc0(num_phys_attrs*sizeof(bool));
1396-
if (cstate->opts.force_notnull)
1396+
if (cstate->opts.force_notnull_all)
1397+
MemSet(cstate->opts.force_notnull_flags, true,num_phys_attrs*sizeof(bool));
1398+
elseif (cstate->opts.force_notnull)
13971399
{
13981400
List*attnums;
13991401
ListCell*cur;
@@ -1416,7 +1418,9 @@ BeginCopyFrom(ParseState *pstate,
14161418

14171419
/* Convert FORCE_NULL name list to per-column flags, check validity */
14181420
cstate->opts.force_null_flags= (bool*)palloc0(num_phys_attrs*sizeof(bool));
1419-
if (cstate->opts.force_null)
1421+
if (cstate->opts.force_null_all)
1422+
MemSet(cstate->opts.force_null_flags, true,num_phys_attrs*sizeof(bool));
1423+
elseif (cstate->opts.force_null)
14201424
{
14211425
List*attnums;
14221426
ListCell*cur;

‎src/backend/commands/copyto.c‎

Lines changed: 1 addition & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -582,10 +582,7 @@ BeginCopyTo(ParseState *pstate,
582582
cstate->opts.force_quote_flags= (bool*)palloc0(num_phys_attrs*sizeof(bool));
583583
if (cstate->opts.force_quote_all)
584584
{
585-
inti;
586-
587-
for (i=0;i<num_phys_attrs;i++)
588-
cstate->opts.force_quote_flags[i]= true;
585+
MemSet(cstate->opts.force_quote_flags, true,num_phys_attrs*sizeof(bool));
589586
}
590587
elseif (cstate->opts.force_quote)
591588
{

‎src/backend/parser/gram.y‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3419,10 +3419,18 @@ copy_opt_item:
34193419
{
34203420
$$ = makeDefElem("force_not_null", (Node *)$4,@1);
34213421
}
3422+
|FORCENOTNULL_P'*'
3423+
{
3424+
$$ = makeDefElem("force_not_null", (Node *) makeNode(A_Star),@1);
3425+
}
34223426
|FORCENULL_PcolumnList
34233427
{
34243428
$$ = makeDefElem("force_null", (Node *)$3,@1);
34253429
}
3430+
|FORCENULL_P'*'
3431+
{
3432+
$$ = makeDefElem("force_null", (Node *) makeNode(A_Star),@1);
3433+
}
34263434
|ENCODINGSconst
34273435
{
34283436
$$ = makeDefElem("encoding", (Node *) makeString($2),@1);

‎src/include/commands/copy.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -56,8 +56,10 @@ typedef struct CopyFormatOptions
5656
boolforce_quote_all;/* FORCE_QUOTE *? */
5757
bool*force_quote_flags;/* per-column CSV FQ flags */
5858
List*force_notnull;/* list of column names */
59+
boolforce_notnull_all;/* FORCE_NOT_NULL *? */
5960
bool*force_notnull_flags;/* per-column CSV FNN flags */
6061
List*force_null;/* list of column names */
62+
boolforce_null_all;/* FORCE_NULL *? */
6163
bool*force_null_flags;/* per-column CSV FN flags */
6264
boolconvert_selectively;/* do selective binary conversion? */
6365
List*convert_select;/* list of column names (can be NIL) */

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

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -520,6 +520,50 @@ BEGIN;
520520
COPY forcetest (d, e) FROM STDIN WITH (FORMAT csv, FORCE_NULL(b));
521521
ERROR: FORCE_NULL column "b" not referenced by COPY
522522
ROLLBACK;
523+
-- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
524+
BEGIN;
525+
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *, FORCE_NULL *);
526+
COMMIT;
527+
SELECT b, c FROM forcetest WHERE a = 4;
528+
b | c
529+
---+------
530+
| NULL
531+
(1 row)
532+
533+
-- should succeed with effect ("b" remains an empty string)
534+
BEGIN;
535+
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *);
536+
COMMIT;
537+
SELECT b, c FROM forcetest WHERE a = 5;
538+
b | c
539+
---+---
540+
|
541+
(1 row)
542+
543+
-- should succeed with effect ("c" remains NULL)
544+
BEGIN;
545+
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *);
546+
COMMIT;
547+
SELECT b, c FROM forcetest WHERE a = 6;
548+
b | c
549+
---+------
550+
b | NULL
551+
(1 row)
552+
553+
-- should fail with "conflicting or redundant options" error
554+
BEGIN;
555+
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *, FORCE_NOT_NULL(b));
556+
ERROR: conflicting or redundant options
557+
LINE 1: ...c) FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *, FORCE_NOT_...
558+
^
559+
ROLLBACK;
560+
-- should fail with "conflicting or redundant options" error
561+
BEGIN;
562+
COPY forcetest (a, b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL(b));
563+
ERROR: conflicting or redundant options
564+
LINE 1: ... b, c) FROM STDIN WITH (FORMAT csv, FORCE_NULL *, FORCE_NULL...
565+
^
566+
ROLLBACK;
523567
\pset null ''
524568
-- test case with whole-row Var in a check constraint
525569
create table check_con_tbl (f1 int);

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

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -344,6 +344,36 @@ ROLLBACK;
344344
BEGIN;
345345
COPY forcetest (d, e)FROM STDIN WITH (FORMAT csv, FORCE_NULL(b));
346346
ROLLBACK;
347+
-- should succeed with no effect ("b" remains an empty string, "c" remains NULL)
348+
BEGIN;
349+
COPY forcetest (a, b, c)FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL*, FORCE_NULL*);
350+
4,,""
351+
\.
352+
COMMIT;
353+
SELECT b, cFROM forcetestWHERE a=4;
354+
-- should succeed with effect ("b" remains an empty string)
355+
BEGIN;
356+
COPY forcetest (a, b, c)FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL*);
357+
5,,""
358+
\.
359+
COMMIT;
360+
SELECT b, cFROM forcetestWHERE a=5;
361+
-- should succeed with effect ("c" remains NULL)
362+
BEGIN;
363+
COPY forcetest (a, b, c)FROM STDIN WITH (FORMAT csv, FORCE_NULL*);
364+
6,"b",""
365+
\.
366+
COMMIT;
367+
SELECT b, cFROM forcetestWHERE a=6;
368+
-- should fail with "conflicting or redundant options" error
369+
BEGIN;
370+
COPY forcetest (a, b, c)FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL*, FORCE_NOT_NULL(b));
371+
ROLLBACK;
372+
-- should fail with "conflicting or redundant options" error
373+
BEGIN;
374+
COPY forcetest (a, b, c)FROM STDIN WITH (FORMAT csv, FORCE_NULL*, FORCE_NULL(b));
375+
ROLLBACK;
376+
347377
\psetnull''
348378

349379
-- test case with whole-row Var in a check constraint

0 commit comments

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