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
Appearance settings

Commit f6d4c9c

Browse filesBrowse 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
Copy full SHA for f6d4c9c

File tree

Expand file treeCollapse file tree

8 files changed

+103
-12
lines changed
Filter options
Expand file treeCollapse file tree

8 files changed

+103
-12
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/copy.sgml
+4-2Lines 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

Copy file name to clipboardExpand all lines: src/backend/commands/copy.c
+8-4Lines 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

Copy file name to clipboardExpand all lines: src/backend/commands/copyfrom.c
+6-2Lines 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

Copy file name to clipboardExpand all lines: src/backend/commands/copyto.c
+1-4Lines 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

Copy file name to clipboardExpand all lines: src/backend/parser/gram.y
+8Lines 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

Copy file name to clipboardExpand all lines: src/include/commands/copy.h
+2Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/expected/copy2.out
+44Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/copy2.sql
+30Lines 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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.