Skip to content

Commit f6d4c9c

Browse files
adunstanadunstan
committed
Provide FORCE_NULL * and FORCE_NOT_NULL * options for COPY FROM
These options already exist, but you need to specify a column list for them, which can be cumbersome. We already have the possibility of all columns for FORCE QUOTE, so this is simply extending that facility to FORCE_NULL and FORCE_NOT_NULL. Author: Zhang Mingli Reviewed-By: Richard Guo, Kyatoro Horiguchi, Michael Paquier. Discussion: https://postgr.es/m/CACJufxEnVqzOFtqhexF2+AwOKFrV8zHOY3y=p+gPK6eB14pn_w@mail.gmail.com
1 parent c181f2e commit f6d4c9c

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
else if (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+
else if (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
else if (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+
else if (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+
else if (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+
else if (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-
int i;
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
else if (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+
| FORCE NOT NULL_P '*'
3423+
{
3424+
$$ = makeDefElem("force_not_null", (Node *) makeNode(A_Star), @1);
3425+
}
34223426
| FORCE NULL_P columnList
34233427
{
34243428
$$ = makeDefElem("force_null", (Node *) $3, @1);
34253429
}
3430+
| FORCE NULL_P '*'
3431+
{
3432+
$$ = makeDefElem("force_null", (Node *) makeNode(A_Star), @1);
3433+
}
34263434
| ENCODING Sconst
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
bool force_quote_all; /* FORCE_QUOTE *? */
5757
bool *force_quote_flags; /* per-column CSV FQ flags */
5858
List *force_notnull; /* list of column names */
59+
bool force_notnull_all; /* FORCE_NOT_NULL *? */
5960
bool *force_notnull_flags; /* per-column CSV FNN flags */
6061
List *force_null; /* list of column names */
62+
bool force_null_all; /* FORCE_NULL *? */
6163
bool *force_null_flags; /* per-column CSV FN flags */
6264
bool convert_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, c FROM forcetest WHERE 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, c FROM forcetest WHERE 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, c FROM forcetest WHERE 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
\pset null ''
348378

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

0 commit comments

Comments
 (0)