Skip to content

Commit 9f8377f

Browse files
adunstanadunstan
committed
Add a DEFAULT option to COPY FROM
This allows for a string which if an input field matches causes the column's default value to be inserted. The advantage of this is that the default can be inserted in some rows and not others, for which non-default data is available. The file_fdw extension is also modified to take allow use of this option. Israel Barth Rubio Discussion: https://postgr.es/m/CAO_rXXAcqesk6DsvioOZ5zmeEmpUN5ktZf-9=9yu+DTr0Xr8Uw@mail.gmail.com
1 parent 7b14e20 commit 9f8377f

File tree

15 files changed

+447
-24
lines changed

15 files changed

+447
-24
lines changed

‎contrib/file_fdw/data/copy_default.csv

+3
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
1,value,2022-07-04
2+
2,\D,2022-07-03
3+
3,\D,\D

‎contrib/file_fdw/expected/file_fdw.out

+17
Original file line numberDiff line numberDiff line change
@@ -424,6 +424,23 @@ SELECT a, c FROM gft1;
424424
(2 rows)
425425

426426
DROP FOREIGN TABLE gft1;
427+
-- copy default tests
428+
\set filename :abs_srcdir '/data/copy_default.csv'
429+
CREATE FOREIGN TABLE copy_default (
430+
id integer,
431+
text_value text not null default 'test',
432+
ts_value timestamp without time zone not null default '2022-07-05'
433+
) SERVER file_server
434+
OPTIONS (format 'csv', filename :'filename', default '\D');
435+
SELECT id, text_value, ts_value FROM copy_default;
436+
id | text_value | ts_value
437+
----+------------+--------------------------
438+
1 | value | Mon Jul 04 00:00:00 2022
439+
2 | test | Sun Jul 03 00:00:00 2022
440+
3 | test | Tue Jul 05 00:00:00 2022
441+
(3 rows)
442+
443+
DROP FOREIGN TABLE copy_default;
427444
-- privilege tests
428445
SET ROLE regress_file_fdw_superuser;
429446
SELECT * FROM agg_text ORDER BY a;

‎contrib/file_fdw/file_fdw.c

+17-3
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,7 @@ static const struct FileFdwOption valid_options[] = {
7272
{"quote", ForeignTableRelationId},
7373
{"escape", ForeignTableRelationId},
7474
{"null", ForeignTableRelationId},
75+
{"default", ForeignTableRelationId},
7576
{"encoding", ForeignTableRelationId},
7677
{"force_not_null", AttributeRelationId},
7778
{"force_null", AttributeRelationId},
@@ -712,6 +713,9 @@ static TupleTableSlot *
712713
fileIterateForeignScan(ForeignScanState *node)
713714
{
714715
FileFdwExecutionState *festate = (FileFdwExecutionState *) node->fdw_state;
716+
EState *estate = CreateExecutorState();
717+
ExprContext *econtext;
718+
MemoryContext oldcontext;
715719
TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
716720
bool found;
717721
ErrorContextCallback errcallback;
@@ -728,15 +732,25 @@ fileIterateForeignScan(ForeignScanState *node)
728732
* ExecStoreVirtualTuple. If we don't find another row in the file, we
729733
* just skip the last step, leaving the slot empty as required.
730734
*
731-
* We can pass ExprContext = NULL because we read all columns from the
732-
* file, so no need to evaluate default expressions.
735+
* We pass ExprContext because there might be a use of the DEFAULT option
736+
* in COPY FROM, so we may need to evaluate default expressions.
733737
*/
734738
ExecClearTuple(slot);
735-
found = NextCopyFrom(festate->cstate, NULL,
739+
econtext = GetPerTupleExprContext(estate);
740+
741+
/*
742+
* DEFAULT expressions need to be evaluated in a per-tuple context, so
743+
* switch in case we are doing that.
744+
*/
745+
oldcontext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
746+
found = NextCopyFrom(festate->cstate, econtext,
736747
slot->tts_values, slot->tts_isnull);
737748
if (found)
738749
ExecStoreVirtualTuple(slot);
739750

751+
/* Switch back to original memory context */
752+
MemoryContextSwitchTo(oldcontext);
753+
740754
/* Remove error callback. */
741755
error_context_stack = errcallback.previous;
742756

‎contrib/file_fdw/sql/file_fdw.sql

+11
Original file line numberDiff line numberDiff line change
@@ -233,6 +233,17 @@ OPTIONS (format 'csv', filename :'filename', delimiter ',');
233233
SELECT a, c FROM gft1;
234234
DROP FOREIGN TABLE gft1;
235235

236+
-- copy default tests
237+
\set filename :abs_srcdir '/data/copy_default.csv'
238+
CREATE FOREIGN TABLE copy_default (
239+
id integer,
240+
text_value text not null default 'test',
241+
ts_value timestamp without time zone not null default '2022-07-05'
242+
) SERVER file_server
243+
OPTIONS (format 'csv', filename :'filename', default '\D');
244+
SELECT id, text_value, ts_value FROM copy_default;
245+
DROP FOREIGN TABLE copy_default;
246+
236247
-- privilege tests
237248
SET ROLE regress_file_fdw_superuser;
238249
SELECT * FROM agg_text ORDER BY a;

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

+14
Original file line numberDiff line numberDiff line change
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
4343
FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
4444
FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
4545
ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
46+
DEFAULT '<replaceable class="parameter">default_string</replaceable>'
4647
</synopsis>
4748
</refsynopsisdiv>
4849

@@ -368,6 +369,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
368369
</listitem>
369370
</varlistentry>
370371

372+
<varlistentry>
373+
<term><literal>DEFAULT</literal></term>
374+
<listitem>
375+
<para>
376+
Specifies the string that represents a default value. Each time the string
377+
is found in the input file, the default value of the corresponding column
378+
will be used.
379+
This option is allowed only in <command>COPY FROM</command>, and only when
380+
not using <literal>binary</literal> format.
381+
</para>
382+
</listitem>
383+
</varlistentry>
384+
371385
<varlistentry>
372386
<term><literal>WHERE</literal></term>
373387
<listitem>

‎src/backend/commands/copy.c

+51
Original file line numberDiff line numberDiff line change
@@ -464,6 +464,12 @@ ProcessCopyOptions(ParseState *pstate,
464464
errorConflictingDefElem(defel, pstate);
465465
opts_out->null_print = defGetString(defel);
466466
}
467+
else if (strcmp(defel->defname, "default") == 0)
468+
{
469+
if (opts_out->default_print)
470+
errorConflictingDefElem(defel, pstate);
471+
opts_out->default_print = defGetString(defel);
472+
}
467473
else if (strcmp(defel->defname, "header") == 0)
468474
{
469475
if (header_specified)
@@ -577,6 +583,11 @@ ProcessCopyOptions(ParseState *pstate,
577583
(errcode(ERRCODE_SYNTAX_ERROR),
578584
errmsg("cannot specify NULL in BINARY mode")));
579585

586+
if (opts_out->binary && opts_out->default_print)
587+
ereport(ERROR,
588+
(errcode(ERRCODE_SYNTAX_ERROR),
589+
errmsg("cannot specify DEFAULT in BINARY mode")));
590+
580591
/* Set defaults for omitted options */
581592
if (!opts_out->delim)
582593
opts_out->delim = opts_out->csv_mode ? "," : "\t";
@@ -612,6 +623,17 @@ ProcessCopyOptions(ParseState *pstate,
612623
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
613624
errmsg("COPY null representation cannot use newline or carriage return")));
614625

626+
if (opts_out->default_print)
627+
{
628+
opts_out->default_print_len = strlen(opts_out->default_print);
629+
630+
if (strchr(opts_out->default_print, '\r') != NULL ||
631+
strchr(opts_out->default_print, '\n') != NULL)
632+
ereport(ERROR,
633+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
634+
errmsg("COPY default representation cannot use newline or carriage return")));
635+
}
636+
615637
/*
616638
* Disallow unsafe delimiter characters in non-CSV mode. We can't allow
617639
* backslash because it would be ambiguous. We can't allow the other
@@ -705,6 +727,35 @@ ProcessCopyOptions(ParseState *pstate,
705727
ereport(ERROR,
706728
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
707729
errmsg("CSV quote character must not appear in the NULL specification")));
730+
731+
if (opts_out->default_print)
732+
{
733+
if (!is_from)
734+
ereport(ERROR,
735+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
736+
errmsg("COPY DEFAULT only available using COPY FROM")));
737+
738+
/* Don't allow the delimiter to appear in the default string. */
739+
if (strchr(opts_out->default_print, opts_out->delim[0]) != NULL)
740+
ereport(ERROR,
741+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
742+
errmsg("COPY delimiter must not appear in the DEFAULT specification")));
743+
744+
/* Don't allow the CSV quote char to appear in the default string. */
745+
if (opts_out->csv_mode &&
746+
strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
747+
ereport(ERROR,
748+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
749+
errmsg("CSV quote character must not appear in the DEFAULT specification")));
750+
751+
/* Don't allow the NULL and DEFAULT string to be the same */
752+
if (opts_out->null_print_len == opts_out->default_print_len &&
753+
strncmp(opts_out->null_print, opts_out->default_print,
754+
opts_out->null_print_len) == 0)
755+
ereport(ERROR,
756+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
757+
errmsg("NULL specification and DEFAULT specification cannot be the same")));
758+
}
708759
}
709760

710761
/*

‎src/backend/commands/copyfrom.c

+13-7
Original file line numberDiff line numberDiff line change
@@ -1565,11 +1565,11 @@ BeginCopyFrom(ParseState *pstate,
15651565
&in_func_oid, &typioparams[attnum - 1]);
15661566
fmgr_info(in_func_oid, &in_functions[attnum - 1]);
15671567

1568-
/* Get default info if needed */
1569-
if (!list_member_int(cstate->attnumlist, attnum) && !att->attgenerated)
1568+
/* Get default info if available */
1569+
defexprs[attnum - 1] = NULL;
1570+
1571+
if (!att->attgenerated)
15701572
{
1571-
/* attribute is NOT to be copied from input */
1572-
/* use default value if one exists */
15731573
Expr *defexpr = (Expr *) build_column_default(cstate->rel,
15741574
attnum);
15751575

@@ -1579,9 +1579,15 @@ BeginCopyFrom(ParseState *pstate,
15791579
defexpr = expression_planner(defexpr);
15801580

15811581
/* Initialize executable expression in copycontext */
1582-
defexprs[num_defaults] = ExecInitExpr(defexpr, NULL);
1583-
defmap[num_defaults] = attnum - 1;
1584-
num_defaults++;
1582+
defexprs[attnum - 1] = ExecInitExpr(defexpr, NULL);
1583+
1584+
/* if NOT copied from input */
1585+
/* use default value if one exists */
1586+
if (!list_member_int(cstate->attnumlist, attnum))
1587+
{
1588+
defmap[num_defaults] = attnum - 1;
1589+
num_defaults++;
1590+
}
15851591

15861592
/*
15871593
* If a default expression looks at the table being loaded,

‎src/backend/commands/copyfromparse.c

+77-8
Original file line numberDiff line numberDiff line change
@@ -842,9 +842,10 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
842842
/*
843843
* Read next tuple from file for COPY FROM. Return false if no more tuples.
844844
*
845-
* 'econtext' is used to evaluate default expression for each column not
846-
* read from the file. It can be NULL when no default values are used, i.e.
847-
* when all columns are read from the file.
845+
* 'econtext' is used to evaluate default expression for each column that is
846+
* either not read from the file or is using the DEFAULT option of COPY FROM.
847+
* It can be NULL when no default values are used, i.e. when all columns are
848+
* read from the file, and DEFAULT option is unset.
848849
*
849850
* 'values' and 'nulls' arrays must be the same length as columns of the
850851
* relation passed to BeginCopyFrom. This function fills the arrays.
@@ -870,6 +871,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
870871
/* Initialize all values for row to NULL */
871872
MemSet(values, 0, num_phys_attrs * sizeof(Datum));
872873
MemSet(nulls, true, num_phys_attrs * sizeof(bool));
874+
cstate->defaults = (bool *) palloc0(num_phys_attrs * sizeof(bool));
873875

874876
if (!cstate->opts.binary)
875877
{
@@ -938,12 +940,27 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
938940

939941
cstate->cur_attname = NameStr(att->attname);
940942
cstate->cur_attval = string;
941-
values[m] = InputFunctionCall(&in_functions[m],
942-
string,
943-
typioparams[m],
944-
att->atttypmod);
943+
945944
if (string != NULL)
946945
nulls[m] = false;
946+
947+
if (cstate->defaults[m])
948+
{
949+
/*
950+
* The caller must supply econtext and have switched into the
951+
* per-tuple memory context in it.
952+
*/
953+
Assert(econtext != NULL);
954+
Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
955+
956+
values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
957+
}
958+
else
959+
values[m] = InputFunctionCall(&in_functions[m],
960+
string,
961+
typioparams[m],
962+
att->atttypmod);
963+
947964
cstate->cur_attname = NULL;
948965
cstate->cur_attval = NULL;
949966
}
@@ -1019,10 +1036,12 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
10191036
Assert(econtext != NULL);
10201037
Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
10211038

1022-
values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
1039+
values[defmap[i]] = ExecEvalExpr(defexprs[defmap[i]], econtext,
10231040
&nulls[defmap[i]]);
10241041
}
10251042

1043+
pfree(cstate->defaults);
1044+
10261045
return true;
10271046
}
10281047

@@ -1663,6 +1682,31 @@ CopyReadAttributesText(CopyFromState cstate)
16631682
if (input_len == cstate->opts.null_print_len &&
16641683
strncmp(start_ptr, cstate->opts.null_print, input_len) == 0)
16651684
cstate->raw_fields[fieldno] = NULL;
1685+
/* Check whether raw input matched default marker */
1686+
else if (cstate->opts.default_print &&
1687+
input_len == cstate->opts.default_print_len &&
1688+
strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
1689+
{
1690+
/* fieldno is 0-indexed and attnum is 1-indexed */
1691+
int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
1692+
1693+
if (cstate->defexprs[m] != NULL)
1694+
{
1695+
/* defaults contain entries for all physical attributes */
1696+
cstate->defaults[m] = true;
1697+
}
1698+
else
1699+
{
1700+
TupleDesc tupDesc = RelationGetDescr(cstate->rel);
1701+
Form_pg_attribute att = TupleDescAttr(tupDesc, m);
1702+
1703+
ereport(ERROR,
1704+
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
1705+
errmsg("unexpected DEFAULT in COPY data"),
1706+
errdetail("Column \"%s\" has no DEFAULT value.",
1707+
NameStr(att->attname))));
1708+
}
1709+
}
16661710
else
16671711
{
16681712
/*
@@ -1852,6 +1896,31 @@ CopyReadAttributesCSV(CopyFromState cstate)
18521896
if (!saw_quote && input_len == cstate->opts.null_print_len &&
18531897
strncmp(start_ptr, cstate->opts.null_print, input_len) == 0)
18541898
cstate->raw_fields[fieldno] = NULL;
1899+
/* Check whether raw input matched default marker */
1900+
else if (cstate->opts.default_print &&
1901+
input_len == cstate->opts.default_print_len &&
1902+
strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
1903+
{
1904+
/* fieldno is 0-index and attnum is 1-index */
1905+
int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
1906+
1907+
if (cstate->defexprs[m] != NULL)
1908+
{
1909+
/* defaults contain entries for all physical attributes */
1910+
cstate->defaults[m] = true;
1911+
}
1912+
else
1913+
{
1914+
TupleDesc tupDesc = RelationGetDescr(cstate->rel);
1915+
Form_pg_attribute att = TupleDescAttr(tupDesc, m);
1916+
1917+
ereport(ERROR,
1918+
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
1919+
errmsg("unexpected DEFAULT in COPY data"),
1920+
errdetail("Column \"%s\" has no DEFAULT value.",
1921+
NameStr(att->attname))));
1922+
}
1923+
}
18551924

18561925
fieldno++;
18571926
/* Done if we hit EOL instead of a delim */

‎src/bin/psql/t/001_basic.pl

+25
Original file line numberDiff line numberDiff line change
@@ -325,4 +325,29 @@ sub psql_fails_like
325325
'client-side error commits transaction, no ON_ERROR_STOP and multiple -c switches'
326326
);
327327

328+
# Test \copy from with DEFAULT option
329+
$node->safe_psql(
330+
'postgres',
331+
"CREATE TABLE copy_default (
332+
id integer PRIMARY KEY,
333+
text_value text NOT NULL DEFAULT 'test',
334+
ts_value timestamp without time zone NOT NULL DEFAULT '2022-07-05'
335+
)"
336+
);
337+
338+
my $copy_default_sql_file = "$tempdir/copy_default.csv";
339+
append_to_file($copy_default_sql_file, "1,value,2022-07-04\n");
340+
append_to_file($copy_default_sql_file, "2,placeholder,2022-07-03\n");
341+
append_to_file($copy_default_sql_file, "3,placeholder,placeholder\n");
342+
343+
psql_like(
344+
$node,
345+
"\\copy copy_default from $copy_default_sql_file with (format 'csv', default 'placeholder');
346+
SELECT * FROM copy_default",
347+
qr/1\|value\|2022-07-04 00:00:00
348+
2|test|2022-07-03 00:00:00
349+
3|test|2022-07-05 00:00:00/,
350+
'\copy from with DEFAULT'
351+
);
352+
328353
done_testing();

0 commit comments

Comments
 (0)