From 10695c9bd5d012b8bd5767bb9630ae00a603a81e Mon Sep 17 00:00:00 2001 From: AyoubKAZ Date: Wed, 4 Nov 2026 00:57:14 +0100 Subject: [PATCH v2] Fix DEFAULT and NULL 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 carried over 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. Similarly, a header column name matching the NULL marker was parsed as NULL, causing HEADER MATCH to reject an otherwise matching column name. Fix by adding an is_header parameter to CopyReadAttributesText() and CopyReadAttributesCSV() to disable DEFAULT and NULL marker recognition while parsing the header line under HEADER MATCH. Reported-by: Ayoub Kazar Author: Ayoub Kazar Author: Chao Li Reviewed-by: Chao Li Discussion: https://postgr.es/m/2da457c8-a9a1-49b1-a89f-719190dff7ae@data-bene.io --- src/backend/commands/copyfromparse.c | 36 ++++++++--------- src/test/regress/expected/copy.out | 2 +- src/test/regress/expected/copy2.out | 52 ++++++++++++++++++++++++ src/test/regress/sql/copy2.sql | 60 ++++++++++++++++++++++++++++ 4 files changed, 131 insertions(+), 19 deletions(-) diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c index 06450535779..1ca1c64f4d7 100644 --- a/src/backend/commands/copyfromparse.c +++ b/src/backend/commands/copyfromparse.c @@ -146,8 +146,8 @@ static const char BinarySignature[11] = "PGCOPY\n\377\r\n\0"; static bool CopyReadLine(CopyFromState cstate, bool is_csv); static pg_always_inline bool CopyReadLineText(CopyFromState cstate, bool is_csv); -static int CopyReadAttributesText(CopyFromState cstate); -static int CopyReadAttributesCSV(CopyFromState cstate); +static int CopyReadAttributesText(CopyFromState cstate, bool is_header); +static int CopyReadAttributesCSV(CopyFromState cstate, bool is_header); static Datum CopyReadBinaryAttribute(CopyFromState cstate, FmgrInfo *flinfo, Oid typioparam, int32 typmod, bool *isnull); @@ -833,9 +833,9 @@ NextCopyFromRawFieldsInternal(CopyFromState cstate, char ***fields, int *nfields int fldnum; if (is_csv) - fldct = CopyReadAttributesCSV(cstate); + fldct = CopyReadAttributesCSV(cstate, true); else - fldct = CopyReadAttributesText(cstate); + fldct = CopyReadAttributesText(cstate, true); if (fldct != list_length(cstate->attnumlist)) ereport(ERROR, @@ -853,11 +853,7 @@ NextCopyFromRawFieldsInternal(CopyFromState cstate, char ***fields, int *nfields Assert(fldnum < cstate->max_fields); colName = cstate->raw_fields[fldnum++]; - if (colName == NULL) - ereport(ERROR, - (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), - errmsg("column name mismatch in header line field %d: got null value (\"%s\"), expected \"%s\"", - fldnum, cstate->opts.null_print, NameStr(attr->attname)))); + Assert(colName != NULL); if (namestrcmp(&attr->attname, colName) != 0) { @@ -888,9 +884,9 @@ NextCopyFromRawFieldsInternal(CopyFromState cstate, char ***fields, int *nfields /* Parse the line into de-escaped field values */ if (is_csv) - fldct = CopyReadAttributesCSV(cstate); + fldct = CopyReadAttributesCSV(cstate, false); else - fldct = CopyReadAttributesText(cstate); + fldct = CopyReadAttributesText(cstate, false); *fields = cstate->raw_fields; *nfields = fldct; @@ -1837,7 +1833,7 @@ GetDecimalFromHex(char hex) * * The input is in line_buf. We use attribute_buf to hold the result * strings. cstate->raw_fields[k] is set to point to the k'th attribute - * string, or NULL when the input matches the null marker string. + * string, or NULL when a data field matches the null marker string. * This array is expanded as necessary. * * (Note that the caller cannot check for nulls since the returned @@ -1848,10 +1844,13 @@ GetDecimalFromHex(char hex) * null_print is the null marker string. Note that this is compared to * the pre-de-escaped input string. * + * If is_header is true, fields are column names, so NULL and DEFAULT + * markers are not recognized. + * * The return value is the number of fields actually read. */ static int -CopyReadAttributesText(CopyFromState cstate) +CopyReadAttributesText(CopyFromState cstate, bool is_header) { char delimc = cstate->opts.delim[0]; int fieldno; @@ -2036,11 +2035,11 @@ CopyReadAttributesText(CopyFromState cstate) /* Check whether raw input matched null marker */ input_len = end_ptr - start_ptr; - if (input_len == cstate->opts.null_print_len && + if (!is_header && input_len == cstate->opts.null_print_len && strncmp(start_ptr, cstate->opts.null_print, input_len) == 0) cstate->raw_fields[fieldno] = NULL; /* Check whether raw input matched default marker */ - else if (fieldno < list_length(cstate->attnumlist) && + else if (!is_header && fieldno < list_length(cstate->attnumlist) && cstate->opts.default_print && input_len == cstate->opts.default_print_len && strncmp(start_ptr, cstate->opts.default_print, input_len) == 0) @@ -2105,7 +2104,7 @@ CopyReadAttributesText(CopyFromState cstate) * "standard" (i.e. common) CSV usage. */ static int -CopyReadAttributesCSV(CopyFromState cstate) +CopyReadAttributesCSV(CopyFromState cstate, bool is_header) { char delimc = cstate->opts.delim[0]; char quotec = cstate->opts.quote[0]; @@ -2251,11 +2250,12 @@ endfield: /* Check whether raw input matched null marker */ input_len = end_ptr - start_ptr; - if (!saw_quote && input_len == cstate->opts.null_print_len && + if (!is_header && !saw_quote && + input_len == cstate->opts.null_print_len && strncmp(start_ptr, cstate->opts.null_print, input_len) == 0) cstate->raw_fields[fieldno] = NULL; /* Check whether raw input matched default marker */ - else if (fieldno < list_length(cstate->attnumlist) && + else if (!is_header && fieldno < list_length(cstate->attnumlist) && cstate->opts.default_print && input_len == cstate->opts.default_print_len && strncmp(start_ptr, cstate->opts.default_print, input_len) == 0) diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out index 979bef5f3f2..be89f1d6bc4 100644 --- a/src/test/regress/expected/copy.out +++ b/src/test/regress/expected/copy.out @@ -480,7 +480,7 @@ copy header_copytest (c, b, a) from stdin with (header match); ERROR: column name mismatch in header line field 1: got "a", expected "c" CONTEXT: COPY header_copytest, line 1: "a b c" copy header_copytest from stdin with (header match); -ERROR: column name mismatch in header line field 3: got null value ("\N"), expected "c" +ERROR: column name mismatch in header line field 3: got "N", expected "c" CONTEXT: COPY header_copytest, line 1: "a b \N" copy header_copytest from stdin with (header match); ERROR: wrong number of fields in header line: got 2, expected 3 diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index df6fcd91799..7546bd95c19 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -1008,7 +1008,59 @@ 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; +copy copy_default from stdin with (format text, 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 text, 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'); ERROR: COPY DEFAULT cannot be used with COPY TO +-- HEADER MATCH must treat NULL markers as column names. +create temp table copy_header ( + id integer, + a text +); +copy copy_header from stdin with (header match, null 'a'); +copy copy_header from stdin with (format csv, header match, null 'a'); +select id, a, a is null as a_is_null from copy_header order by id; + id | a | a_is_null +----+-------+----------- + 1 | hello | f + 2 | | t + 3 | hello | f + 4 | | t + 5 | a | f +(5 rows) + diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index ec6f5e8cfdc..315e15c62b6 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -819,5 +819,65 @@ 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; + +copy copy_default from stdin with (format text, 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 text, 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'); + +-- HEADER MATCH must treat NULL markers as column names. +create temp table copy_header ( + id integer, + a text +); + +copy copy_header from stdin with (header match, null 'a'); +id a +1 hello +2 a +\. + +copy copy_header from stdin with (format csv, header match, null 'a'); +id,a +3,hello +4,a +5,"a" +\. + +select id, a, a is null as a_is_null from copy_header order by id; -- 2.50.1 (Apple Git-155)