Hello,
I noticed an issue when combining the HEADER MATCH option (added in v15)
with the DEFAULT option in COPY FROM (added in v16).
If any column name in the header line matches the configured DEFAULT marker,
two problematic behaviors occur depending on whether the column has a
default:
-When the column has a default value, the input value of the first data row
is discarded and replaced with the column's default expression. All
subsequent data rows are loaded normally:
CREATE TABLE sales (id int, "default" int DEFAULT 0, note text);
COPY sales FROM STDIN WITH (FORMAT csv, HEADER MATCH, DEFAULT 'default');
id,default,note
1,5,first
2,7,second
\.
SELECT * FROM sales ORDER BY id;
id | default | note
----+---------+--------
1 | 0 | first -- Expected 5 here ?
2 | 7 | second
(2 rows)
-When the column does not have a default value, COPY aborts during header
parsing, but not for the correct reason i guess:
CREATE TABLE t (d int, b text);
COPY t FROM STDIN WITH (FORMAT csv, HEADER MATCH, DEFAULT 'd');
d,b
5,x
\.
ERROR: unexpected default marker in COPY data
DETAIL: Column "d" has no default value.
CONTEXT: COPY t, line 1: "d,b"
This also happens with foreign tables via file_fdw when used with
OPTIONS (header 'match', default '...').
Because header parsing and the first row of data are parsed within the same
first call to NextCopyFrom(), and cstate->defaults is initialized to false
only at the top of NextCopyFrom(), the flag set while reading the header
line survived into the first data row. CopyFromTextLikeOneRow() then sees
cstate->defaults[m] set to true and evaluate the default expression
instead of
the supplied field.
The attached fix temporarily disables default marker recognition while
parsing
the header line when HEADER MATCH. I also added two tests for this.
This affects REL_16_STABLE through master.
Thoughts?
Regards,
Ayoub Kazar
From a9ea51eaa4330786e35c1da224fc0659bf27aee8 Mon Sep 17 00:00:00 2001
From: AyoubKAZ <[email protected]>
Date: Wed, 4 Nov 2026 00:57:14 +0100
Subject: [PATCH] Fix DEFAULT marker handling in COPY FROM ... HEADER MATCH
When using COPY FROM with both HEADER MATCH and DEFAULT options,
the header line is parsed using CopyReadAttributesText() or
CopyReadAttributesCSV(). Because default marker matching was active
during header line parsing, any header column name identical to the
DEFAULT marker string was mistakenly processed as a data default marker.
This caused two bugs depending on whether the column had a default:
1. If the column had no default expression, parsing the
header line raised an error ("unexpected default marker in COPY data")
2. If the column had a default expression, CopyReadAttributes*() set cstate->defaults[m] = true while reading
the header. Because header parsing and the first data row are
processed in the same NextCopyFrom() call, and cstate->defaults is
cleared only at the start of NextCopyFrom(), the flag set during header
parsing just followed up into the first actual row . As a result, the input
value in the first row was discarded and replaced with the column's
default value, while subsequent data rows were unaffected.
Fix by temporarily disabling default marker recognition while parsing
the header line under HEADER MATCH.
---
src/backend/commands/copyfromparse.c | 10 ++++++++++
src/test/regress/expected/copy2.out | 18 ++++++++++++++++++
src/test/regress/sql/copy2.sql | 20 ++++++++++++++++++++
3 files changed, 48 insertions(+)
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 06450535779..4cc656117f3 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -831,12 +831,22 @@ NextCopyFromRawFieldsInternal(CopyFromState cstate, char ***fields, int *nfields
if (cstate->opts.header_line == COPY_HEADER_MATCH)
{
int fldnum;
+ char *save_default_print = cstate->opts.default_print;
+
+ /*
+ * Disable DEFAULT marker recognition while parsing the header
+ * line, so that column names matching the default string are not
+ * treated as default markers.
+ */
+ cstate->opts.default_print = NULL;
if (is_csv)
fldct = CopyReadAttributesCSV(cstate);
else
fldct = CopyReadAttributesText(cstate);
+ cstate->opts.default_print = save_default_print;
+
if (fldct != list_length(cstate->attnumlist))
ereport(ERROR,
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index df6fcd91799..57523d8de9c 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -1008,6 +1008,24 @@ select id, text_value, ts_value from copy_default;
3 | test | Tue Jul 05 00:00:00 2022
(3 rows)
+truncate copy_default;
+-- DEFAULT with HEADER MATCH where header field matches DEFAULT marker
+copy copy_default from stdin with (format csv, header match, default 'id');
+select id, text_value, ts_value from copy_default;
+ id | text_value | ts_value
+----+------------+--------------------------
+ 1 | hello | Mon Jul 04 00:00:00 2022
+(1 row)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, header match, default 'text_value');
+select id, text_value, ts_value from copy_default;
+ id | text_value | ts_value
+----+------------+--------------------------
+ 1 | hello | Mon Jul 04 00:00:00 2022
+ 2 | test | Sun Jul 03 00:00:00 2022
+(2 rows)
+
truncate copy_default;
-- DEFAULT cannot be used in COPY TO
copy (select 1 as test) TO stdout with (default '\D');
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index ec6f5e8cfdc..dc31137e51b 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -819,5 +819,25 @@ select id, text_value, ts_value from copy_default;
truncate copy_default;
+-- DEFAULT with HEADER MATCH where header field matches DEFAULT marker
+copy copy_default from stdin with (format csv, header match, default 'id');
+id,text_value,ts_value
+1,hello,2022-07-04
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, header match, default 'text_value');
+id,text_value,ts_value
+1,hello,2022-07-04
+2,text_value,2022-07-03
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
-- DEFAULT cannot be used in COPY TO
copy (select 1 as test) TO stdout with (default '\D');
--
2.34.1
