Add support for DEFAULT specification in COPY FROM

From c5945d4a5ce82f74fea43372e73afeec14f6a7b2 Mon Sep 17 00:00:00 2001
From: Israel Barth <israel.barth@laptop428-ma-us.local>
Date: Fri, 15 Jul 2022 20:56:36 -0300
Subject: [PATCH v2] Added support for DEFAULT in COPY FROM

Previous to this commit, COPY FROM command used to load the column
DEFAULT value if the column was missing in the command specification.

With this commit we introduce a new feature that works like the NULL
feature from COPY command. The user will be able to specify a marker,
and whenever that marker is found in the input of COPY FROM, it will
be replaced with the DEFAULT value of the corresponding column.

In order to make "the new" COPY from backward compatible, the DEFAULT
feature will only take place if the use specifies some value for that
option.

We are taking advantage of the code that was already implemented in
COPY FROM to find and evaluate the DEFAULT expressions, with the only
difference that now we check both for columns that are missing in the
command specification, and for columns where the DEFAULT marker was
found.

Signed-off-by: Israel Barth <israel.barth@laptop428-ma-us.local>
---
 doc/src/sgml/ref/copy.sgml                |  13 +++
 src/backend/commands/copy.c               |  46 ++++++++++
 src/backend/commands/copyfrom.c           |  22 +++--
 src/backend/commands/copyfromparse.c      | 100 ++++++++++++++++++----
 src/include/commands/copy.h               |   4 +-
 src/include/commands/copyfrom_internal.h  |   6 +-
 src/test/regress/expected/copydefault.out |  97 +++++++++++++++++++++
 src/test/regress/parallel_schedule        |   2 +-
 src/test/regress/sql/copydefault.sql      |  99 +++++++++++++++++++++
 9 files changed, 361 insertions(+), 28 deletions(-)
 create mode 100644 src/test/regress/expected/copydefault.out
 create mode 100644 src/test/regress/sql/copydefault.sql

diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 63afa0d97e..4cac339b34 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
+    DEFAULT '<replaceable class="parameter">default_string</replaceable>'
 </synopsis>
  </refsynopsisdiv>
 
@@ -368,6 +369,18 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>DEFAULT</literal></term>
+    <listitem>
+     <para>
+      Specifies the string that represents a default value. Each time this string
+      is found in the input file, the default value of the corresponding column
+      will be used.
+      This option is not allowed when using <literal>binary</literal> format.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>WHERE</literal></term>
     <listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 49924e476a..2d59556c4b 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -460,6 +460,12 @@ ProcessCopyOptions(ParseState *pstate,
 				errorConflictingDefElem(defel, pstate);
 			opts_out->null_print = defGetString(defel);
 		}
+		else if (strcmp(defel->defname, "default") == 0)
+		{
+			if (opts_out->default_print)
+				errorConflictingDefElem(defel, pstate);
+			opts_out->default_print = defGetString(defel);
+		}
 		else if (strcmp(defel->defname, "header") == 0)
 		{
 			if (header_specified)
@@ -573,6 +579,11 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify NULL in BINARY mode")));
 
+	if (opts_out->binary && opts_out->default_print)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify DEFAULT in BINARY mode")));
+
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
@@ -608,6 +619,17 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("COPY null representation cannot use newline or carriage return")));
 
+	if (opts_out->default_print)
+	{
+		opts_out->default_print_len = strlen(opts_out->default_print);
+
+		if (strchr(opts_out->default_print, '\r') != NULL ||
+			strchr(opts_out->default_print, '\n') != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("COPY default representation cannot use newline or carriage return")));
+	}
+
 	/*
 	 * Disallow unsafe delimiter characters in non-CSV mode.  We can't allow
 	 * backslash because it would be ambiguous.  We can't allow the other
@@ -701,6 +723,30 @@ ProcessCopyOptions(ParseState *pstate,
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("CSV quote character must not appear in the NULL specification")));
+
+	if (opts_out->default_print)
+	{
+		/* Don't allow the delimiter to appear in the default string. */
+		if (strchr(opts_out->default_print, opts_out->delim[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY delimiter must not appear in the DEFAULT specification")));
+
+		/* Don't allow the CSV quote char to appear in the default string. */
+		if (opts_out->csv_mode &&
+			strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("CSV quote character must not appear in the DEFAULT specification")));
+
+		/* Don't allow the NULL and DEFAULT string to be the same */
+		if (opts_out->null_print_len == opts_out->default_print_len &&
+			strncmp(opts_out->null_print, opts_out->default_print,
+			opts_out->null_print_len) == 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("NULL specification and DEFAULT specification cannot be the same")));
+	}
 }
 
 /*
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index a976008b3d..1c430217bb 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1418,12 +1418,12 @@ BeginCopyFrom(ParseState *pstate,
 							 &in_func_oid, &typioparams[attnum - 1]);
 		fmgr_info(in_func_oid, &in_functions[attnum - 1]);
 
-		/* Get default info if needed */
-		if (!list_member_int(cstate->attnumlist, attnum) && !att->attgenerated)
+		/* Get default info if available */
+		defexprs[attnum - 1] = NULL;
+
+		if(!att->attgenerated)
 		{
-			/* attribute is NOT to be copied from input */
-			/* use default value if one exists */
-			Expr	   *defexpr = (Expr *) build_column_default(cstate->rel,
+			Expr       *defexpr = (Expr *) build_column_default(cstate->rel,
 																attnum);
 
 			if (defexpr != NULL)
@@ -1432,9 +1432,15 @@ BeginCopyFrom(ParseState *pstate,
 				defexpr = expression_planner(defexpr);
 
 				/* Initialize executable expression in copycontext */
-				defexprs[num_defaults] = ExecInitExpr(defexpr, NULL);
-				defmap[num_defaults] = attnum - 1;
-				num_defaults++;
+				defexprs[attnum - 1] = ExecInitExpr(defexpr, NULL);
+
+				/* attribute is NOT to be copied from input */
+				/* use default value if one exists */
+				if (!list_member_int(cstate->attnumlist, attnum))
+				{
+					defmap[num_defaults] = attnum - 1;
+					num_defaults++;
+				}
 
 				/*
 				 * If a default expression looks at the table being loaded,
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 57813b3458..0f8d79a30b 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -151,8 +151,8 @@ static const char BinarySignature[11] = "PGCOPY\n\377\r\n\0";
 /* non-export function prototypes */
 static bool CopyReadLine(CopyFromState cstate);
 static bool CopyReadLineText(CopyFromState cstate);
-static int	CopyReadAttributesText(CopyFromState cstate);
-static int	CopyReadAttributesCSV(CopyFromState cstate);
+static int	CopyReadAttributesText(CopyFromState cstate, bool *defaults);
+static int	CopyReadAttributesCSV(CopyFromState cstate, bool *defaults);
 static Datum CopyReadBinaryAttribute(CopyFromState cstate, FmgrInfo *flinfo,
 									 Oid typioparam, int32 typmod,
 									 bool *isnull);
@@ -751,7 +751,7 @@ CopyReadBinaryData(CopyFromState cstate, char *dest, int nbytes)
  * NOTE: force_not_null option are not applied to the returned fields.
  */
 bool
-NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
+NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields, bool *defaults)
 {
 	int			fldct;
 	bool		done;
@@ -775,9 +775,9 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 			int			fldnum;
 
 			if (cstate->opts.csv_mode)
-				fldct = CopyReadAttributesCSV(cstate);
+				fldct = CopyReadAttributesCSV(cstate, defaults);
 			else
-				fldct = CopyReadAttributesText(cstate);
+				fldct = CopyReadAttributesText(cstate, defaults);
 
 			if (fldct != list_length(cstate->attnumlist))
 				ereport(ERROR,
@@ -830,9 +830,9 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 
 	/* Parse the line into de-escaped field values */
 	if (cstate->opts.csv_mode)
-		fldct = CopyReadAttributesCSV(cstate);
+		fldct = CopyReadAttributesCSV(cstate, defaults);
 	else
-		fldct = CopyReadAttributesText(cstate);
+		fldct = CopyReadAttributesText(cstate, defaults);
 
 	*fields = cstate->raw_fields;
 	*nfields = fldct;
@@ -862,6 +862,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	int			i;
 	int		   *defmap = cstate->defmap;
 	ExprState **defexprs = cstate->defexprs;
+	bool	   *defaults;
 
 	tupDesc = RelationGetDescr(cstate->rel);
 	num_phys_attrs = tupDesc->natts;
@@ -870,6 +871,8 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	/* Initialize all values for row to NULL */
 	MemSet(values, 0, num_phys_attrs * sizeof(Datum));
 	MemSet(nulls, true, num_phys_attrs * sizeof(bool));
+	defaults = (bool *) palloc0(num_phys_attrs * sizeof(bool));
+	MemSet(defaults, false, num_phys_attrs * sizeof(bool));
 
 	if (!cstate->opts.binary)
 	{
@@ -880,7 +883,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		char	   *string;
 
 		/* read raw fields in the next line */
-		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
+		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct, defaults))
 			return false;
 
 		/* check for overflowing fields */
@@ -938,12 +941,27 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
 			cstate->cur_attname = NameStr(att->attname);
 			cstate->cur_attval = string;
-			values[m] = InputFunctionCall(&in_functions[m],
-										  string,
-										  typioparams[m],
-										  att->atttypmod);
+
 			if (string != NULL)
 				nulls[m] = false;
+
+			if (defaults[m])
+			{
+				/*
+				 * The caller must supply econtext and have switched into the
+				 * per-tuple memory context in it.
+				 */
+				Assert(econtext != NULL);
+				Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
+
+				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
+			}
+			else
+				values[m] = InputFunctionCall(&in_functions[m],
+											  string,
+											  typioparams[m],
+											  att->atttypmod);
+
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
 		}
@@ -1019,10 +1037,12 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		Assert(econtext != NULL);
 		Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
 
-		values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
+		values[defmap[i]] = ExecEvalExpr(defexprs[defmap[i]], econtext,
 										 &nulls[defmap[i]]);
 	}
 
+	pfree(defaults);
+
 	return true;
 }
 
@@ -1475,7 +1495,7 @@ GetDecimalFromHex(char hex)
  * The return value is the number of fields actually read.
  */
 static int
-CopyReadAttributesText(CopyFromState cstate)
+CopyReadAttributesText(CopyFromState cstate, bool *defaults)
 {
 	char		delimc = cstate->opts.delim[0];
 	int			fieldno;
@@ -1663,6 +1683,31 @@ CopyReadAttributesText(CopyFromState cstate)
 		if (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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-index and attnum is 1-index */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 		else
 		{
 			/*
@@ -1703,7 +1748,7 @@ CopyReadAttributesText(CopyFromState cstate)
  * "standard" (i.e. common) CSV usage.
  */
 static int
-CopyReadAttributesCSV(CopyFromState cstate)
+CopyReadAttributesCSV(CopyFromState cstate, bool *defaults)
 {
 	char		delimc = cstate->opts.delim[0];
 	char		quotec = cstate->opts.quote[0];
@@ -1852,6 +1897,31 @@ endfield:
 		if (!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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-index and attnum is 1-index */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 
 		fieldno++;
 		/* Done if we hit EOL instead of a delim */
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index cb0096aeb6..207a29e5d5 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -47,6 +47,8 @@ typedef struct CopyFormatOptions
 	char	   *null_print;		/* NULL marker string (server encoding!) */
 	int			null_print_len; /* length of same */
 	char	   *null_print_client;	/* same converted to file encoding */
+	char       *default_print;  /* DEFAULT marker string */
+	int         default_print_len;  /* length of same */
 	char	   *delim;			/* column delimiter (must be 1 byte) */
 	char	   *quote;			/* CSV quote char (must be 1 byte) */
 	char	   *escape;			/* CSV escape char (must be 1 byte) */
@@ -79,7 +81,7 @@ extern void EndCopyFrom(CopyFromState cstate);
 extern bool NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 						 Datum *values, bool *nulls);
 extern bool NextCopyFromRawFields(CopyFromState cstate,
-								  char ***fields, int *nfields);
+								  char ***fields, int *nfields, bool *defaults);
 extern void CopyFromErrorCallback(void *arg);
 
 extern uint64 CopyFrom(CopyFromState cstate);
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index e37c6032ae..61a658bead 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -87,11 +87,11 @@ typedef struct CopyFromStateData
 	 */
 	MemoryContext copycontext;	/* per-copy execution context */
 
-	AttrNumber	num_defaults;
+	AttrNumber	num_defaults;	/* count of att that are missing and have default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
-	int		   *defmap;			/* array of default att numbers */
-	ExprState **defexprs;		/* array of default att expressions */
+	int		   *defmap;			/* array of default att numbers related to missing att */
+	ExprState **defexprs;		/* array of default att expressions for all att */
 	bool		volatile_defexprs;	/* is any of defexprs volatile? */
 	List	   *range_table;
 	ExprState  *qualexpr;
diff --git a/src/test/regress/expected/copydefault.out b/src/test/regress/expected/copydefault.out
new file mode 100644
index 0000000000..30d0526266
--- /dev/null
+++ b/src/test/regress/expected/copydefault.out
@@ -0,0 +1,97 @@
+--
+-- COPY DEFAULT
+-- this file is responsible for testing DEFAULT option of COPY FROM
+--
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | D          | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv);
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | \D         | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+ERROR:  cannot specify DEFAULT in BINARY mode
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+ERROR:  COPY default representation cannot use newline or carriage return
+copy copy_default from stdin with (default E'\r');
+ERROR:  COPY default representation cannot use newline or carriage return
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+ERROR:  COPY delimiter must not appear in the DEFAULT specification
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+ERROR:  CSV quote character must not appear in the DEFAULT specification
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+ERROR:  NULL specification and DEFAULT specification cannot be the same
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D	value	'2022-07-04'"
+copy copy_default from stdin with (format csv, default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D,value,2022-07-04"
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \D         | Mon Jul 04 00:00:00 2022
+  3 | "D"        | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \\D        | Mon Jul 04 00:00:00 2022
+  3 | \D         | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 103e11483d..bfc10d10a0 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -41,7 +41,7 @@ test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comment
 # execute two copy tests in parallel, to check that copy itself
 # is concurrent safe.
 # ----------
-test: copy copyselect copydml insert insert_conflict
+test: copy copyselect copydml insert insert_conflict copydefault
 
 # ----------
 # More groups of parallel tests
diff --git a/src/test/regress/sql/copydefault.sql b/src/test/regress/sql/copydefault.sql
new file mode 100644
index 0000000000..ea3a6c414f
--- /dev/null
+++ b/src/test/regress/sql/copydefault.sql
@@ -0,0 +1,99 @@
+--
+-- COPY DEFAULT
+-- this file is responsible for testing DEFAULT option of COPY FROM
+--
+
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+1	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv);
+1,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+copy copy_default from stdin with (default E'\r');
+
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+\D	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+copy copy_default from stdin with (format csv, default '\D');
+\D,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+1	\D	'2022-07-04'
+2	\\D	'2022-07-04'
+3	"\D"	'2022-07-04'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,\D,2022-07-04
+2,\\D,2022-07-04
+3,"\D",2022-07-04
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+1	value	'2022-07-04'
+2	\D	'2022-07-03'
+3	\D	\D
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
-- 
2.34.1

From 28f61ca4134b12462dfba77e4246b032d35c588b Mon Sep 17 00:00:00 2001
From: Israel Barth <israel.barth@laptop428-ma-us.local>
Date: Mon, 26 Sep 2022 11:56:32 -0300
Subject: [PATCH v3] Added support for DEFAULT in COPY FROM

Previous to this commit, COPY FROM command used to load the column
DEFAULT value only if the column was missing in the command specification.

With this commit we introduce a new feature that works like the NULL
feature from COPY command. The user will be able to specify a marker,
and whenever that marker is found in the input of COPY FROM, it will
be replaced with the DEFAULT value of the corresponding column.

In order to make "the new" COPY from backward compatible, the DEFAULT
feature will only take place if the use specifies some value for that
option.

We are taking advantage of the code that was already implemented in
COPY FROM to find and evaluate the DEFAULT expressions, with the only
difference that now we check both for columns that are missing in the
command specification, and for columns where the DEFAULT marker was
found.

This commit also changes the `file_fdw` to make it possible to use
the new DEFAULT option of COPY FROM. We had to introduce the tuple
context in order to be able to evaluate the DEFAULT value in `file_fdw`.

Signed-off-by: Israel Barth <israel.barth@laptop428-ma-us.local>
---
 contrib/file_fdw/data/copy_default.csv   |   3 +
 contrib/file_fdw/expected/file_fdw.out   |  17 ++++
 contrib/file_fdw/file_fdw.c              |  10 ++-
 contrib/file_fdw/sql/file_fdw.sql        |  11 +++
 doc/src/sgml/ref/copy.sgml               |  14 +++
 src/backend/commands/copy.c              |  51 +++++++++++
 src/backend/commands/copyfrom.c          |  22 +++--
 src/backend/commands/copyfromparse.c     | 107 +++++++++++++++++++----
 src/bin/psql/t/001_basic.pl              |  25 ++++++
 src/bin/psql/t/010_tab_completion.pl     |   8 ++
 src/bin/psql/tab-complete.c              |   2 +-
 src/include/commands/copy.h              |   4 +-
 src/include/commands/copyfrom_internal.h |   6 +-
 src/test/regress/expected/copy2.out      | 100 +++++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 104 ++++++++++++++++++++++
 15 files changed, 450 insertions(+), 34 deletions(-)
 create mode 100644 contrib/file_fdw/data/copy_default.csv

diff --git a/contrib/file_fdw/data/copy_default.csv b/contrib/file_fdw/data/copy_default.csv
new file mode 100644
index 0000000000..5e83a15db4
--- /dev/null
+++ b/contrib/file_fdw/data/copy_default.csv
@@ -0,0 +1,3 @@
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 36d76ba26c..f5ae29732a 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -424,6 +424,23 @@ SELECT a, c FROM gft1;
 (2 rows)
 
 DROP FOREIGN TABLE gft1;
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+DROP FOREIGN TABLE copy_default;
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index de0b9a109c..53c50d10d8 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -72,6 +72,7 @@ static const struct FileFdwOption valid_options[] = {
 	{"quote", ForeignTableRelationId},
 	{"escape", ForeignTableRelationId},
 	{"null", ForeignTableRelationId},
+	{"default", ForeignTableRelationId},
 	{"encoding", ForeignTableRelationId},
 	{"force_not_null", AttributeRelationId},
 	{"force_null", AttributeRelationId},
@@ -712,6 +713,8 @@ static TupleTableSlot *
 fileIterateForeignScan(ForeignScanState *node)
 {
 	FileFdwExecutionState *festate = (FileFdwExecutionState *) node->fdw_state;
+	EState	    *estate = CreateExecutorState();
+	ExprContext *econtext;
 	TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
 	bool		found;
 	ErrorContextCallback errcallback;
@@ -728,11 +731,12 @@ fileIterateForeignScan(ForeignScanState *node)
 	 * ExecStoreVirtualTuple.  If we don't find another row in the file, we
 	 * just skip the last step, leaving the slot empty as required.
 	 *
-	 * We can pass ExprContext = NULL because we read all columns from the
-	 * file, so no need to evaluate default expressions.
+	 * We pass ExprContext because of the possibility of usage of DEFAULT
+	 * option in COPY FROM, so we may need to evaluate default expressions.
 	 */
 	ExecClearTuple(slot);
-	found = NextCopyFrom(festate->cstate, NULL,
+	econtext = GetPerTupleExprContext(estate);
+	found = NextCopyFrom(festate->cstate, econtext,
 						 slot->tts_values, slot->tts_isnull);
 	if (found)
 		ExecStoreVirtualTuple(slot);
diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql
index 46670397ca..f0548e14e1 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -233,6 +233,17 @@ OPTIONS (format 'csv', filename :'filename', delimiter ',');
 SELECT a, c FROM gft1;
 DROP FOREIGN TABLE gft1;
 
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+DROP FOREIGN TABLE copy_default;
+
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c25b52d0cb..165fc11f04 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
+    DEFAULT '<replaceable class="parameter">default_string</replaceable>'
 </synopsis>
  </refsynopsisdiv>
 
@@ -368,6 +369,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>DEFAULT</literal></term>
+    <listitem>
+     <para>
+      Specifies the string that represents a default value. Each time this string
+      is found in the input file, the default value of the corresponding column
+      will be used.
+      This option is allowed only in <command>COPY FROM</command>, and only when
+      not using <literal>binary</literal> format.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>WHERE</literal></term>
     <listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 49924e476a..643217a1c7 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -460,6 +460,12 @@ ProcessCopyOptions(ParseState *pstate,
 				errorConflictingDefElem(defel, pstate);
 			opts_out->null_print = defGetString(defel);
 		}
+		else if (strcmp(defel->defname, "default") == 0)
+		{
+			if (opts_out->default_print)
+				errorConflictingDefElem(defel, pstate);
+			opts_out->default_print = defGetString(defel);
+		}
 		else if (strcmp(defel->defname, "header") == 0)
 		{
 			if (header_specified)
@@ -573,6 +579,11 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify NULL in BINARY mode")));
 
+	if (opts_out->binary && opts_out->default_print)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify DEFAULT in BINARY mode")));
+
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
@@ -608,6 +619,17 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("COPY null representation cannot use newline or carriage return")));
 
+	if (opts_out->default_print)
+	{
+		opts_out->default_print_len = strlen(opts_out->default_print);
+
+		if (strchr(opts_out->default_print, '\r') != NULL ||
+			strchr(opts_out->default_print, '\n') != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("COPY default representation cannot use newline or carriage return")));
+	}
+
 	/*
 	 * Disallow unsafe delimiter characters in non-CSV mode.  We can't allow
 	 * backslash because it would be ambiguous.  We can't allow the other
@@ -701,6 +723,35 @@ ProcessCopyOptions(ParseState *pstate,
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("CSV quote character must not appear in the NULL specification")));
+
+	if (opts_out->default_print)
+	{
+		if (!is_from)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY DEFAULT only available using COPY FROM")));
+
+		/* Don't allow the delimiter to appear in the default string. */
+		if (strchr(opts_out->default_print, opts_out->delim[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY delimiter must not appear in the DEFAULT specification")));
+
+		/* Don't allow the CSV quote char to appear in the default string. */
+		if (opts_out->csv_mode &&
+			strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("CSV quote character must not appear in the DEFAULT specification")));
+
+		/* Don't allow the NULL and DEFAULT string to be the same */
+		if (opts_out->null_print_len == opts_out->default_print_len &&
+			strncmp(opts_out->null_print, opts_out->default_print,
+			opts_out->null_print_len) == 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("NULL specification and DEFAULT specification cannot be the same")));
+	}
 }
 
 /*
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 175aa837f2..901a31ef0d 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1417,12 +1417,12 @@ BeginCopyFrom(ParseState *pstate,
 							 &in_func_oid, &typioparams[attnum - 1]);
 		fmgr_info(in_func_oid, &in_functions[attnum - 1]);
 
-		/* Get default info if needed */
-		if (!list_member_int(cstate->attnumlist, attnum) && !att->attgenerated)
+		/* Get default info if available */
+		defexprs[attnum - 1] = NULL;
+
+		if(!att->attgenerated)
 		{
-			/* attribute is NOT to be copied from input */
-			/* use default value if one exists */
-			Expr	   *defexpr = (Expr *) build_column_default(cstate->rel,
+			Expr       *defexpr = (Expr *) build_column_default(cstate->rel,
 																attnum);
 
 			if (defexpr != NULL)
@@ -1431,9 +1431,15 @@ BeginCopyFrom(ParseState *pstate,
 				defexpr = expression_planner(defexpr);
 
 				/* Initialize executable expression in copycontext */
-				defexprs[num_defaults] = ExecInitExpr(defexpr, NULL);
-				defmap[num_defaults] = attnum - 1;
-				num_defaults++;
+				defexprs[attnum - 1] = ExecInitExpr(defexpr, NULL);
+
+				/* attribute is NOT to be copied from input */
+				/* use default value if one exists */
+				if (!list_member_int(cstate->attnumlist, attnum))
+				{
+					defmap[num_defaults] = attnum - 1;
+					num_defaults++;
+				}
 
 				/*
 				 * If a default expression looks at the table being loaded,
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 097414ef12..6ade60d9b9 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -151,8 +151,8 @@ static const char BinarySignature[11] = "PGCOPY\n\377\r\n\0";
 /* non-export function prototypes */
 static bool CopyReadLine(CopyFromState cstate);
 static bool CopyReadLineText(CopyFromState cstate);
-static int	CopyReadAttributesText(CopyFromState cstate);
-static int	CopyReadAttributesCSV(CopyFromState cstate);
+static int	CopyReadAttributesText(CopyFromState cstate, bool *defaults);
+static int	CopyReadAttributesCSV(CopyFromState cstate, bool *defaults);
 static Datum CopyReadBinaryAttribute(CopyFromState cstate, FmgrInfo *flinfo,
 									 Oid typioparam, int32 typmod,
 									 bool *isnull);
@@ -751,7 +751,7 @@ CopyReadBinaryData(CopyFromState cstate, char *dest, int nbytes)
  * NOTE: force_not_null option are not applied to the returned fields.
  */
 bool
-NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
+NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields, bool *defaults)
 {
 	int			fldct;
 	bool		done;
@@ -775,9 +775,9 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 			int			fldnum;
 
 			if (cstate->opts.csv_mode)
-				fldct = CopyReadAttributesCSV(cstate);
+				fldct = CopyReadAttributesCSV(cstate, defaults);
 			else
-				fldct = CopyReadAttributesText(cstate);
+				fldct = CopyReadAttributesText(cstate, defaults);
 
 			if (fldct != list_length(cstate->attnumlist))
 				ereport(ERROR,
@@ -830,9 +830,9 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 
 	/* Parse the line into de-escaped field values */
 	if (cstate->opts.csv_mode)
-		fldct = CopyReadAttributesCSV(cstate);
+		fldct = CopyReadAttributesCSV(cstate, defaults);
 	else
-		fldct = CopyReadAttributesText(cstate);
+		fldct = CopyReadAttributesText(cstate, defaults);
 
 	*fields = cstate->raw_fields;
 	*nfields = fldct;
@@ -842,9 +842,10 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 /*
  * Read next tuple from file for COPY FROM. Return false if no more tuples.
  *
- * 'econtext' is used to evaluate default expression for each column not
- * read from the file. It can be NULL when no default values are used, i.e.
- * when all columns are read from the file.
+ * 'econtext' is used to evaluate default expression for each column that is
+ * either not read from the file or is using the DEFAULT option of COPY FROM.
+ * It can be NULL when no default values are used, i.e. when all columns are
+ * read from the file, and DEFAULT option is unset.
  *
  * 'values' and 'nulls' arrays must be the same length as columns of the
  * relation passed to BeginCopyFrom. This function fills the arrays.
@@ -862,6 +863,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	int			i;
 	int		   *defmap = cstate->defmap;
 	ExprState **defexprs = cstate->defexprs;
+	bool	   *defaults;
 
 	tupDesc = RelationGetDescr(cstate->rel);
 	num_phys_attrs = tupDesc->natts;
@@ -870,6 +872,8 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	/* Initialize all values for row to NULL */
 	MemSet(values, 0, num_phys_attrs * sizeof(Datum));
 	MemSet(nulls, true, num_phys_attrs * sizeof(bool));
+	defaults = (bool *) palloc0(num_phys_attrs * sizeof(bool));
+	MemSet(defaults, false, num_phys_attrs * sizeof(bool));
 
 	if (!cstate->opts.binary)
 	{
@@ -880,7 +884,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		char	   *string;
 
 		/* read raw fields in the next line */
-		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
+		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct, defaults))
 			return false;
 
 		/* check for overflowing fields */
@@ -938,12 +942,27 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
 			cstate->cur_attname = NameStr(att->attname);
 			cstate->cur_attval = string;
-			values[m] = InputFunctionCall(&in_functions[m],
-										  string,
-										  typioparams[m],
-										  att->atttypmod);
+
 			if (string != NULL)
 				nulls[m] = false;
+
+			if (defaults[m])
+			{
+				/*
+				 * The caller must supply econtext and have switched into the
+				 * per-tuple memory context in it.
+				 */
+				Assert(econtext != NULL);
+				Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
+
+				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
+			}
+			else
+				values[m] = InputFunctionCall(&in_functions[m],
+											  string,
+											  typioparams[m],
+											  att->atttypmod);
+
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
 		}
@@ -1019,10 +1038,12 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		Assert(econtext != NULL);
 		Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
 
-		values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
+		values[defmap[i]] = ExecEvalExpr(defexprs[defmap[i]], econtext,
 										 &nulls[defmap[i]]);
 	}
 
+	pfree(defaults);
+
 	return true;
 }
 
@@ -1475,7 +1496,7 @@ GetDecimalFromHex(char hex)
  * The return value is the number of fields actually read.
  */
 static int
-CopyReadAttributesText(CopyFromState cstate)
+CopyReadAttributesText(CopyFromState cstate, bool *defaults)
 {
 	char		delimc = cstate->opts.delim[0];
 	int			fieldno;
@@ -1663,6 +1684,31 @@ CopyReadAttributesText(CopyFromState cstate)
 		if (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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-index and attnum is 1-index */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 		else
 		{
 			/*
@@ -1703,7 +1749,7 @@ CopyReadAttributesText(CopyFromState cstate)
  * "standard" (i.e. common) CSV usage.
  */
 static int
-CopyReadAttributesCSV(CopyFromState cstate)
+CopyReadAttributesCSV(CopyFromState cstate, bool *defaults)
 {
 	char		delimc = cstate->opts.delim[0];
 	char		quotec = cstate->opts.quote[0];
@@ -1852,6 +1898,31 @@ endfield:
 		if (!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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-index and attnum is 1-index */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 
 		fieldno++;
 		/* Done if we hit EOL instead of a delim */
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index f447845717..698df2fe53 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -325,4 +325,29 @@ is($row_count, '10',
 	'client-side error commits transaction, no ON_ERROR_STOP and multiple -c switches'
 );
 
+# Test \copy from with DEFAULT option
+$node->safe_psql(
+	'postgres',
+	"CREATE TABLE copy_default (
+		id integer PRIMARY KEY,
+		text_value text NOT NULL DEFAULT 'test',
+		ts_value timestamp without time zone NOT NULL DEFAULT '2022-07-05'
+	)"
+);
+
+my $copy_default_sql_file = "$tempdir/copy_default.csv";
+append_to_file($copy_default_sql_file, "1,value,2022-07-04\n");
+append_to_file($copy_default_sql_file, "2,placeholder,2022-07-03\n");
+append_to_file($copy_default_sql_file, "3,placeholder,placeholder\n");
+
+psql_like(
+	$node,
+	"\\copy copy_default from $copy_default_sql_file with (format 'csv', default 'placeholder');
+	SELECT * FROM copy_default",
+	qr/1\|value\|2022-07-04 00:00:00
+2|test|2022-07-03 00:00:00
+3|test|2022-07-05 00:00:00/,
+	'\copy from with DEFAULT'
+);
+
 done_testing();
diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl
index 4aa6dd5fe1..08271713bc 100644
--- a/src/bin/psql/t/010_tab_completion.pl
+++ b/src/bin/psql/t/010_tab_completion.pl
@@ -442,6 +442,14 @@ check_completion("blarg \t\t", qr//, "check completion failure path");
 
 clear_query();
 
+# check COPY FROM with DEFAULT option
+check_completion(
+	"COPY foo FROM stdin WITH ( DEF\t)",
+	qr/DEFAULT /,
+	"COPY FROM with DEFAULT completion");
+
+clear_line();
+
 # send psql an explicit \q to shut it down, else pty won't close properly
 $timer->start($PostgreSQL::Test::Utils::timeout_default);
 $in .= "\\q\n";
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index dbe89d7eb2..e297fece48 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2757,7 +2757,7 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING");
+					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 3f6677b132..49053943b7 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -47,6 +47,8 @@ typedef struct CopyFormatOptions
 	char	   *null_print;		/* NULL marker string (server encoding!) */
 	int			null_print_len; /* length of same */
 	char	   *null_print_client;	/* same converted to file encoding */
+	char       *default_print;  /* DEFAULT marker string */
+	int         default_print_len;  /* length of same */
 	char	   *delim;			/* column delimiter (must be 1 byte) */
 	char	   *quote;			/* CSV quote char (must be 1 byte) */
 	char	   *escape;			/* CSV escape char (must be 1 byte) */
@@ -79,7 +81,7 @@ extern void EndCopyFrom(CopyFromState cstate);
 extern bool NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 						 Datum *values, bool *nulls);
 extern bool NextCopyFromRawFields(CopyFromState cstate,
-								  char ***fields, int *nfields);
+								  char ***fields, int *nfields, bool *defaults);
 extern void CopyFromErrorCallback(void *arg);
 
 extern uint64 CopyFrom(CopyFromState cstate);
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index e37c6032ae..61a658bead 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -87,11 +87,11 @@ typedef struct CopyFromStateData
 	 */
 	MemoryContext copycontext;	/* per-copy execution context */
 
-	AttrNumber	num_defaults;
+	AttrNumber	num_defaults;	/* count of att that are missing and have default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
-	int		   *defmap;			/* array of default att numbers */
-	ExprState **defexprs;		/* array of default att expressions */
+	int		   *defmap;			/* array of default att numbers related to missing att */
+	ExprState **defexprs;		/* array of default att expressions for all att */
 	bool		volatile_defexprs;	/* is any of defexprs volatile? */
 	List	   *range_table;
 	ExprState  *qualexpr;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 5f3685e9ef..aabda29eb9 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -663,3 +663,103 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+--
+-- COPY DEFAULT
+-- the following tests are responsible for testing DEFAULT option of COPY FROM
+--
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | D          | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv);
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | \D         | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+ERROR:  cannot specify DEFAULT in BINARY mode
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+ERROR:  COPY default representation cannot use newline or carriage return
+copy copy_default from stdin with (default E'\r');
+ERROR:  COPY default representation cannot use newline or carriage return
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+ERROR:  COPY delimiter must not appear in the DEFAULT specification
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+ERROR:  CSV quote character must not appear in the DEFAULT specification
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+ERROR:  NULL specification and DEFAULT specification cannot be the same
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D	value	'2022-07-04'"
+copy copy_default from stdin with (format csv, default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D,value,2022-07-04"
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \D         | Mon Jul 04 00:00:00 2022
+  3 | "D"        | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \\D        | Mon Jul 04 00:00:00 2022
+  3 | \D         | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in COPY TO
+copy (select 1 as test) TO stdout with (default '\D');
+ERROR:  COPY DEFAULT only available using COPY FROM
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index b3c16af48e..41b36f5d1d 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -468,3 +468,107 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+
+--
+-- COPY DEFAULT
+-- the following tests are responsible for testing DEFAULT option of COPY FROM
+--
+
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+1	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv);
+1,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+copy copy_default from stdin with (default E'\r');
+
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+\D	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+copy copy_default from stdin with (format csv, default '\D');
+\D,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+1	\D	'2022-07-04'
+2	\\D	'2022-07-04'
+3	"\D"	'2022-07-04'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,\D,2022-07-04
+2,\\D,2022-07-04
+3,"\D",2022-07-04
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+1	value	'2022-07-04'
+2	\D	'2022-07-03'
+3	\D	\D
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
+\.
+
+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

From 2b852feb5658954265c82b0515915cbe4b820412 Mon Sep 17 00:00:00 2001
From: Israel Barth <barthisrael@gmail.com>
Date: Fri, 7 Oct 2022 16:16:01 -0300
Subject: [PATCH v4] Added support for DEFAULT in COPY FROM

Previous to this commit, COPY FROM command used to load the column
DEFAULT value only if the column was missing in the command specification.

With this commit we introduce a new feature that works like the NULL
feature from COPY command. The user will be able to specify a marker,
and whenever that marker is found in the input of COPY FROM, it will
be replaced with the DEFAULT value of the corresponding column.

In order to make "the new" COPY from backward compatible, the DEFAULT
feature will only take place if the use specifies some value for that
option.

We are taking advantage of the code that was already implemented in
COPY FROM to find and evaluate the DEFAULT expressions, with the only
difference that now we check both for columns that are missing in the
command specification, and for columns where the DEFAULT marker was
found.

This commit also changes the `file_fdw` to make it possible to use
the new DEFAULT option of COPY FROM. We had to introduce the tuple
context in order to be able to evaluate the DEFAULT value in `file_fdw`.

Signed-off-by: Israel Barth <israel.barth@laptop428-ma-us.local>
---
 contrib/file_fdw/data/copy_default.csv   |   3 +
 contrib/file_fdw/expected/file_fdw.out   |  17 ++++
 contrib/file_fdw/file_fdw.c              |  19 +++-
 contrib/file_fdw/sql/file_fdw.sql        |  11 +++
 doc/src/sgml/ref/copy.sgml               |  14 +++
 src/backend/commands/copy.c              |  51 +++++++++++
 src/backend/commands/copyfrom.c          |  22 +++--
 src/backend/commands/copyfromparse.c     | 107 +++++++++++++++++++----
 src/bin/psql/t/001_basic.pl              |  25 ++++++
 src/bin/psql/t/010_tab_completion.pl     |   8 ++
 src/bin/psql/tab-complete.c              |   2 +-
 src/include/commands/copy.h              |   4 +-
 src/include/commands/copyfrom_internal.h |   6 +-
 src/test/regress/expected/copy2.out      | 100 +++++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 104 ++++++++++++++++++++++
 15 files changed, 459 insertions(+), 34 deletions(-)
 create mode 100644 contrib/file_fdw/data/copy_default.csv

diff --git a/contrib/file_fdw/data/copy_default.csv b/contrib/file_fdw/data/copy_default.csv
new file mode 100644
index 0000000000..5e83a15db4
--- /dev/null
+++ b/contrib/file_fdw/data/copy_default.csv
@@ -0,0 +1,3 @@
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 36d76ba26c..f5ae29732a 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -424,6 +424,23 @@ SELECT a, c FROM gft1;
 (2 rows)
 
 DROP FOREIGN TABLE gft1;
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+DROP FOREIGN TABLE copy_default;
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 67821cd25b..bbf585250b 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -72,6 +72,7 @@ static const struct FileFdwOption valid_options[] = {
 	{"quote", ForeignTableRelationId},
 	{"escape", ForeignTableRelationId},
 	{"null", ForeignTableRelationId},
+	{"default", ForeignTableRelationId},
 	{"encoding", ForeignTableRelationId},
 	{"force_not_null", AttributeRelationId},
 	{"force_null", AttributeRelationId},
@@ -712,6 +713,9 @@ static TupleTableSlot *
 fileIterateForeignScan(ForeignScanState *node)
 {
 	FileFdwExecutionState *festate = (FileFdwExecutionState *) node->fdw_state;
+	EState	    *estate = CreateExecutorState();
+	ExprContext *econtext;
+	MemoryContext oldcontext;
 	TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
 	bool		found;
 	ErrorContextCallback errcallback;
@@ -728,15 +732,24 @@ fileIterateForeignScan(ForeignScanState *node)
 	 * ExecStoreVirtualTuple.  If we don't find another row in the file, we
 	 * just skip the last step, leaving the slot empty as required.
 	 *
-	 * We can pass ExprContext = NULL because we read all columns from the
-	 * file, so no need to evaluate default expressions.
+	 * We pass ExprContext because of the possibility of usage of DEFAULT
+	 * option in COPY FROM, so we may need to evaluate default expressions.
 	 */
 	ExecClearTuple(slot);
-	found = NextCopyFrom(festate->cstate, NULL,
+	econtext = GetPerTupleExprContext(estate);
+	/*
+	 * Switch to per-tuple context before calling NextCopyFrom, which does
+	 * evaluate default expressions etc. and requires per-tuple context.
+	 */
+	oldcontext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+	found = NextCopyFrom(festate->cstate, econtext,
 						 slot->tts_values, slot->tts_isnull);
 	if (found)
 		ExecStoreVirtualTuple(slot);
 
+	/* Switch back to original memory context */
+	MemoryContextSwitchTo(oldcontext);
+
 	/* Remove error callback. */
 	error_context_stack = errcallback.previous;
 
diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql
index 46670397ca..f0548e14e1 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -233,6 +233,17 @@ OPTIONS (format 'csv', filename :'filename', delimiter ',');
 SELECT a, c FROM gft1;
 DROP FOREIGN TABLE gft1;
 
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+DROP FOREIGN TABLE copy_default;
+
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c25b52d0cb..165fc11f04 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
+    DEFAULT '<replaceable class="parameter">default_string</replaceable>'
 </synopsis>
  </refsynopsisdiv>
 
@@ -368,6 +369,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>DEFAULT</literal></term>
+    <listitem>
+     <para>
+      Specifies the string that represents a default value. Each time this string
+      is found in the input file, the default value of the corresponding column
+      will be used.
+      This option is allowed only in <command>COPY FROM</command>, and only when
+      not using <literal>binary</literal> format.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>WHERE</literal></term>
     <listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 49924e476a..643217a1c7 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -460,6 +460,12 @@ ProcessCopyOptions(ParseState *pstate,
 				errorConflictingDefElem(defel, pstate);
 			opts_out->null_print = defGetString(defel);
 		}
+		else if (strcmp(defel->defname, "default") == 0)
+		{
+			if (opts_out->default_print)
+				errorConflictingDefElem(defel, pstate);
+			opts_out->default_print = defGetString(defel);
+		}
 		else if (strcmp(defel->defname, "header") == 0)
 		{
 			if (header_specified)
@@ -573,6 +579,11 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify NULL in BINARY mode")));
 
+	if (opts_out->binary && opts_out->default_print)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify DEFAULT in BINARY mode")));
+
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
@@ -608,6 +619,17 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("COPY null representation cannot use newline or carriage return")));
 
+	if (opts_out->default_print)
+	{
+		opts_out->default_print_len = strlen(opts_out->default_print);
+
+		if (strchr(opts_out->default_print, '\r') != NULL ||
+			strchr(opts_out->default_print, '\n') != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("COPY default representation cannot use newline or carriage return")));
+	}
+
 	/*
 	 * Disallow unsafe delimiter characters in non-CSV mode.  We can't allow
 	 * backslash because it would be ambiguous.  We can't allow the other
@@ -701,6 +723,35 @@ ProcessCopyOptions(ParseState *pstate,
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("CSV quote character must not appear in the NULL specification")));
+
+	if (opts_out->default_print)
+	{
+		if (!is_from)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY DEFAULT only available using COPY FROM")));
+
+		/* Don't allow the delimiter to appear in the default string. */
+		if (strchr(opts_out->default_print, opts_out->delim[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY delimiter must not appear in the DEFAULT specification")));
+
+		/* Don't allow the CSV quote char to appear in the default string. */
+		if (opts_out->csv_mode &&
+			strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("CSV quote character must not appear in the DEFAULT specification")));
+
+		/* Don't allow the NULL and DEFAULT string to be the same */
+		if (opts_out->null_print_len == opts_out->default_print_len &&
+			strncmp(opts_out->null_print, opts_out->default_print,
+			opts_out->null_print_len) == 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("NULL specification and DEFAULT specification cannot be the same")));
+	}
 }
 
 /*
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 175aa837f2..3ae3ab985e 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1417,12 +1417,12 @@ BeginCopyFrom(ParseState *pstate,
 							 &in_func_oid, &typioparams[attnum - 1]);
 		fmgr_info(in_func_oid, &in_functions[attnum - 1]);
 
-		/* Get default info if needed */
-		if (!list_member_int(cstate->attnumlist, attnum) && !att->attgenerated)
+		/* Get default info if available */
+		defexprs[attnum - 1] = NULL;
+
+		if(!att->attgenerated)
 		{
-			/* attribute is NOT to be copied from input */
-			/* use default value if one exists */
-			Expr	   *defexpr = (Expr *) build_column_default(cstate->rel,
+			Expr       *defexpr = (Expr *) build_column_default(cstate->rel,
 																attnum);
 
 			if (defexpr != NULL)
@@ -1431,9 +1431,15 @@ BeginCopyFrom(ParseState *pstate,
 				defexpr = expression_planner(defexpr);
 
 				/* Initialize executable expression in copycontext */
-				defexprs[num_defaults] = ExecInitExpr(defexpr, NULL);
-				defmap[num_defaults] = attnum - 1;
-				num_defaults++;
+				defexprs[attnum - 1] = ExecInitExpr(defexpr, NULL);
+
+				/* if NOT copied from input */
+				/* use default value if one exists */
+				if (!list_member_int(cstate->attnumlist, attnum))
+				{
+					defmap[num_defaults] = attnum - 1;
+					num_defaults++;
+				}
 
 				/*
 				 * If a default expression looks at the table being loaded,
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 097414ef12..34cf9c16ea 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -151,8 +151,8 @@ static const char BinarySignature[11] = "PGCOPY\n\377\r\n\0";
 /* non-export function prototypes */
 static bool CopyReadLine(CopyFromState cstate);
 static bool CopyReadLineText(CopyFromState cstate);
-static int	CopyReadAttributesText(CopyFromState cstate);
-static int	CopyReadAttributesCSV(CopyFromState cstate);
+static int	CopyReadAttributesText(CopyFromState cstate, bool *defaults);
+static int	CopyReadAttributesCSV(CopyFromState cstate, bool *defaults);
 static Datum CopyReadBinaryAttribute(CopyFromState cstate, FmgrInfo *flinfo,
 									 Oid typioparam, int32 typmod,
 									 bool *isnull);
@@ -751,7 +751,7 @@ CopyReadBinaryData(CopyFromState cstate, char *dest, int nbytes)
  * NOTE: force_not_null option are not applied to the returned fields.
  */
 bool
-NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
+NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields, bool *defaults)
 {
 	int			fldct;
 	bool		done;
@@ -775,9 +775,9 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 			int			fldnum;
 
 			if (cstate->opts.csv_mode)
-				fldct = CopyReadAttributesCSV(cstate);
+				fldct = CopyReadAttributesCSV(cstate, defaults);
 			else
-				fldct = CopyReadAttributesText(cstate);
+				fldct = CopyReadAttributesText(cstate, defaults);
 
 			if (fldct != list_length(cstate->attnumlist))
 				ereport(ERROR,
@@ -830,9 +830,9 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 
 	/* Parse the line into de-escaped field values */
 	if (cstate->opts.csv_mode)
-		fldct = CopyReadAttributesCSV(cstate);
+		fldct = CopyReadAttributesCSV(cstate, defaults);
 	else
-		fldct = CopyReadAttributesText(cstate);
+		fldct = CopyReadAttributesText(cstate, defaults);
 
 	*fields = cstate->raw_fields;
 	*nfields = fldct;
@@ -842,9 +842,10 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 /*
  * Read next tuple from file for COPY FROM. Return false if no more tuples.
  *
- * 'econtext' is used to evaluate default expression for each column not
- * read from the file. It can be NULL when no default values are used, i.e.
- * when all columns are read from the file.
+ * 'econtext' is used to evaluate default expression for each column that is
+ * either not read from the file or is using the DEFAULT option of COPY FROM.
+ * It can be NULL when no default values are used, i.e. when all columns are
+ * read from the file, and DEFAULT option is unset.
  *
  * 'values' and 'nulls' arrays must be the same length as columns of the
  * relation passed to BeginCopyFrom. This function fills the arrays.
@@ -862,6 +863,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	int			i;
 	int		   *defmap = cstate->defmap;
 	ExprState **defexprs = cstate->defexprs;
+	bool	   *defaults;
 
 	tupDesc = RelationGetDescr(cstate->rel);
 	num_phys_attrs = tupDesc->natts;
@@ -870,6 +872,8 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	/* Initialize all values for row to NULL */
 	MemSet(values, 0, num_phys_attrs * sizeof(Datum));
 	MemSet(nulls, true, num_phys_attrs * sizeof(bool));
+	defaults = (bool *) palloc0(num_phys_attrs * sizeof(bool));
+	MemSet(defaults, false, num_phys_attrs * sizeof(bool));
 
 	if (!cstate->opts.binary)
 	{
@@ -880,7 +884,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		char	   *string;
 
 		/* read raw fields in the next line */
-		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
+		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct, defaults))
 			return false;
 
 		/* check for overflowing fields */
@@ -938,12 +942,27 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
 			cstate->cur_attname = NameStr(att->attname);
 			cstate->cur_attval = string;
-			values[m] = InputFunctionCall(&in_functions[m],
-										  string,
-										  typioparams[m],
-										  att->atttypmod);
+
 			if (string != NULL)
 				nulls[m] = false;
+
+			if (defaults[m])
+			{
+				/*
+				 * The caller must supply econtext and have switched into the
+				 * per-tuple memory context in it.
+				 */
+				Assert(econtext != NULL);
+				Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
+
+				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
+			}
+			else
+				values[m] = InputFunctionCall(&in_functions[m],
+											  string,
+											  typioparams[m],
+											  att->atttypmod);
+
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
 		}
@@ -1019,10 +1038,12 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		Assert(econtext != NULL);
 		Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
 
-		values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
+		values[defmap[i]] = ExecEvalExpr(defexprs[defmap[i]], econtext,
 										 &nulls[defmap[i]]);
 	}
 
+	pfree(defaults);
+
 	return true;
 }
 
@@ -1475,7 +1496,7 @@ GetDecimalFromHex(char hex)
  * The return value is the number of fields actually read.
  */
 static int
-CopyReadAttributesText(CopyFromState cstate)
+CopyReadAttributesText(CopyFromState cstate, bool *defaults)
 {
 	char		delimc = cstate->opts.delim[0];
 	int			fieldno;
@@ -1663,6 +1684,31 @@ CopyReadAttributesText(CopyFromState cstate)
 		if (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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-indexed and attnum is 1-indexed */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 		else
 		{
 			/*
@@ -1703,7 +1749,7 @@ CopyReadAttributesText(CopyFromState cstate)
  * "standard" (i.e. common) CSV usage.
  */
 static int
-CopyReadAttributesCSV(CopyFromState cstate)
+CopyReadAttributesCSV(CopyFromState cstate, bool *defaults)
 {
 	char		delimc = cstate->opts.delim[0];
 	char		quotec = cstate->opts.quote[0];
@@ -1852,6 +1898,31 @@ endfield:
 		if (!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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-index and attnum is 1-index */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 
 		fieldno++;
 		/* Done if we hit EOL instead of a delim */
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index f447845717..698df2fe53 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -325,4 +325,29 @@ is($row_count, '10',
 	'client-side error commits transaction, no ON_ERROR_STOP and multiple -c switches'
 );
 
+# Test \copy from with DEFAULT option
+$node->safe_psql(
+	'postgres',
+	"CREATE TABLE copy_default (
+		id integer PRIMARY KEY,
+		text_value text NOT NULL DEFAULT 'test',
+		ts_value timestamp without time zone NOT NULL DEFAULT '2022-07-05'
+	)"
+);
+
+my $copy_default_sql_file = "$tempdir/copy_default.csv";
+append_to_file($copy_default_sql_file, "1,value,2022-07-04\n");
+append_to_file($copy_default_sql_file, "2,placeholder,2022-07-03\n");
+append_to_file($copy_default_sql_file, "3,placeholder,placeholder\n");
+
+psql_like(
+	$node,
+	"\\copy copy_default from $copy_default_sql_file with (format 'csv', default 'placeholder');
+	SELECT * FROM copy_default",
+	qr/1\|value\|2022-07-04 00:00:00
+2|test|2022-07-03 00:00:00
+3|test|2022-07-05 00:00:00/,
+	'\copy from with DEFAULT'
+);
+
 done_testing();
diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl
index 4aa6dd5fe1..08271713bc 100644
--- a/src/bin/psql/t/010_tab_completion.pl
+++ b/src/bin/psql/t/010_tab_completion.pl
@@ -442,6 +442,14 @@ check_completion("blarg \t\t", qr//, "check completion failure path");
 
 clear_query();
 
+# check COPY FROM with DEFAULT option
+check_completion(
+	"COPY foo FROM stdin WITH ( DEF\t)",
+	qr/DEFAULT /,
+	"COPY FROM with DEFAULT completion");
+
+clear_line();
+
 # send psql an explicit \q to shut it down, else pty won't close properly
 $timer->start($PostgreSQL::Test::Utils::timeout_default);
 $in .= "\\q\n";
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 584d9d5ae6..0e933c387a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2757,7 +2757,7 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING");
+					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 3f6677b132..49053943b7 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -47,6 +47,8 @@ typedef struct CopyFormatOptions
 	char	   *null_print;		/* NULL marker string (server encoding!) */
 	int			null_print_len; /* length of same */
 	char	   *null_print_client;	/* same converted to file encoding */
+	char       *default_print;  /* DEFAULT marker string */
+	int         default_print_len;  /* length of same */
 	char	   *delim;			/* column delimiter (must be 1 byte) */
 	char	   *quote;			/* CSV quote char (must be 1 byte) */
 	char	   *escape;			/* CSV escape char (must be 1 byte) */
@@ -79,7 +81,7 @@ extern void EndCopyFrom(CopyFromState cstate);
 extern bool NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 						 Datum *values, bool *nulls);
 extern bool NextCopyFromRawFields(CopyFromState cstate,
-								  char ***fields, int *nfields);
+								  char ***fields, int *nfields, bool *defaults);
 extern void CopyFromErrorCallback(void *arg);
 
 extern uint64 CopyFrom(CopyFromState cstate);
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index e37c6032ae..61a658bead 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -87,11 +87,11 @@ typedef struct CopyFromStateData
 	 */
 	MemoryContext copycontext;	/* per-copy execution context */
 
-	AttrNumber	num_defaults;
+	AttrNumber	num_defaults;	/* count of att that are missing and have default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
-	int		   *defmap;			/* array of default att numbers */
-	ExprState **defexprs;		/* array of default att expressions */
+	int		   *defmap;			/* array of default att numbers related to missing att */
+	ExprState **defexprs;		/* array of default att expressions for all att */
 	bool		volatile_defexprs;	/* is any of defexprs volatile? */
 	List	   *range_table;
 	ExprState  *qualexpr;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 5f3685e9ef..aabda29eb9 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -663,3 +663,103 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+--
+-- COPY DEFAULT
+-- the following tests are responsible for testing DEFAULT option of COPY FROM
+--
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | D          | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv);
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | \D         | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+ERROR:  cannot specify DEFAULT in BINARY mode
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+ERROR:  COPY default representation cannot use newline or carriage return
+copy copy_default from stdin with (default E'\r');
+ERROR:  COPY default representation cannot use newline or carriage return
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+ERROR:  COPY delimiter must not appear in the DEFAULT specification
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+ERROR:  CSV quote character must not appear in the DEFAULT specification
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+ERROR:  NULL specification and DEFAULT specification cannot be the same
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D	value	'2022-07-04'"
+copy copy_default from stdin with (format csv, default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D,value,2022-07-04"
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \D         | Mon Jul 04 00:00:00 2022
+  3 | "D"        | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \\D        | Mon Jul 04 00:00:00 2022
+  3 | \D         | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in COPY TO
+copy (select 1 as test) TO stdout with (default '\D');
+ERROR:  COPY DEFAULT only available using COPY FROM
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index b3c16af48e..41b36f5d1d 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -468,3 +468,107 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+
+--
+-- COPY DEFAULT
+-- the following tests are responsible for testing DEFAULT option of COPY FROM
+--
+
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+1	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv);
+1,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+copy copy_default from stdin with (default E'\r');
+
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+\D	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+copy copy_default from stdin with (format csv, default '\D');
+\D,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+1	\D	'2022-07-04'
+2	\\D	'2022-07-04'
+3	"\D"	'2022-07-04'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,\D,2022-07-04
+2,\\D,2022-07-04
+3,"\D",2022-07-04
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+1	value	'2022-07-04'
+2	\D	'2022-07-03'
+3	\D	\D
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
+\.
+
+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

From 62298645431a8c7452f655edeb5abb30f09ad7dc Mon Sep 17 00:00:00 2001
From: Israel Barth <barthisrael@gmail.com>
Date: Fri, 7 Oct 2022 17:51:43 -0300
Subject: [PATCH v5] Added support for DEFAULT in COPY FROM

Previous to this commit, COPY FROM command used to load the column
DEFAULT value only if the column was missing in the command specification.

With this commit we introduce a new feature that works like the NULL
feature from COPY command. The user will be able to specify a marker,
and whenever that marker is found in the input of COPY FROM, it will
be replaced with the DEFAULT value of the corresponding column.

In order to make "the new" COPY from backward compatible, the DEFAULT
feature will only take place if the use specifies some value for that
option.

We are taking advantage of the code that was already implemented in
COPY FROM to find and evaluate the DEFAULT expressions, with the only
difference that now we check both for columns that are missing in the
command specification, and for columns where the DEFAULT marker was
found.

This commit also changes the `file_fdw` to make it possible to use
the new DEFAULT option of COPY FROM. We had to introduce the tuple
context in order to be able to evaluate the DEFAULT value in `file_fdw`.

Signed-off-by: Israel Barth <barthisrael@gmail.com>
---
 contrib/file_fdw/data/copy_default.csv   |   3 +
 contrib/file_fdw/expected/file_fdw.out   |  17 ++++
 contrib/file_fdw/file_fdw.c              |  19 +++-
 contrib/file_fdw/sql/file_fdw.sql        |  11 +++
 doc/src/sgml/ref/copy.sgml               |  14 +++
 src/backend/commands/copy.c              |  51 +++++++++++
 src/backend/commands/copyfrom.c          |  22 +++--
 src/backend/commands/copyfromparse.c     | 106 +++++++++++++++++++----
 src/bin/psql/t/001_basic.pl              |  25 ++++++
 src/bin/psql/t/010_tab_completion.pl     |   8 ++
 src/bin/psql/tab-complete.c              |   2 +-
 src/include/commands/copy.h              |   4 +-
 src/include/commands/copyfrom_internal.h |   6 +-
 src/test/regress/expected/copy2.out      | 100 +++++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 104 ++++++++++++++++++++++
 15 files changed, 458 insertions(+), 34 deletions(-)
 create mode 100644 contrib/file_fdw/data/copy_default.csv

diff --git a/contrib/file_fdw/data/copy_default.csv b/contrib/file_fdw/data/copy_default.csv
new file mode 100644
index 0000000000..5e83a15db4
--- /dev/null
+++ b/contrib/file_fdw/data/copy_default.csv
@@ -0,0 +1,3 @@
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 36d76ba26c..f5ae29732a 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -424,6 +424,23 @@ SELECT a, c FROM gft1;
 (2 rows)
 
 DROP FOREIGN TABLE gft1;
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+DROP FOREIGN TABLE copy_default;
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 67821cd25b..bbf585250b 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -72,6 +72,7 @@ static const struct FileFdwOption valid_options[] = {
 	{"quote", ForeignTableRelationId},
 	{"escape", ForeignTableRelationId},
 	{"null", ForeignTableRelationId},
+	{"default", ForeignTableRelationId},
 	{"encoding", ForeignTableRelationId},
 	{"force_not_null", AttributeRelationId},
 	{"force_null", AttributeRelationId},
@@ -712,6 +713,9 @@ static TupleTableSlot *
 fileIterateForeignScan(ForeignScanState *node)
 {
 	FileFdwExecutionState *festate = (FileFdwExecutionState *) node->fdw_state;
+	EState	    *estate = CreateExecutorState();
+	ExprContext *econtext;
+	MemoryContext oldcontext;
 	TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
 	bool		found;
 	ErrorContextCallback errcallback;
@@ -728,15 +732,24 @@ fileIterateForeignScan(ForeignScanState *node)
 	 * ExecStoreVirtualTuple.  If we don't find another row in the file, we
 	 * just skip the last step, leaving the slot empty as required.
 	 *
-	 * We can pass ExprContext = NULL because we read all columns from the
-	 * file, so no need to evaluate default expressions.
+	 * We pass ExprContext because of the possibility of usage of DEFAULT
+	 * option in COPY FROM, so we may need to evaluate default expressions.
 	 */
 	ExecClearTuple(slot);
-	found = NextCopyFrom(festate->cstate, NULL,
+	econtext = GetPerTupleExprContext(estate);
+	/*
+	 * Switch to per-tuple context before calling NextCopyFrom, which does
+	 * evaluate default expressions etc. and requires per-tuple context.
+	 */
+	oldcontext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+	found = NextCopyFrom(festate->cstate, econtext,
 						 slot->tts_values, slot->tts_isnull);
 	if (found)
 		ExecStoreVirtualTuple(slot);
 
+	/* Switch back to original memory context */
+	MemoryContextSwitchTo(oldcontext);
+
 	/* Remove error callback. */
 	error_context_stack = errcallback.previous;
 
diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql
index 46670397ca..f0548e14e1 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -233,6 +233,17 @@ OPTIONS (format 'csv', filename :'filename', delimiter ',');
 SELECT a, c FROM gft1;
 DROP FOREIGN TABLE gft1;
 
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+DROP FOREIGN TABLE copy_default;
+
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c25b52d0cb..165fc11f04 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
+    DEFAULT '<replaceable class="parameter">default_string</replaceable>'
 </synopsis>
  </refsynopsisdiv>
 
@@ -368,6 +369,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>DEFAULT</literal></term>
+    <listitem>
+     <para>
+      Specifies the string that represents a default value. Each time this string
+      is found in the input file, the default value of the corresponding column
+      will be used.
+      This option is allowed only in <command>COPY FROM</command>, and only when
+      not using <literal>binary</literal> format.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>WHERE</literal></term>
     <listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 49924e476a..643217a1c7 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -460,6 +460,12 @@ ProcessCopyOptions(ParseState *pstate,
 				errorConflictingDefElem(defel, pstate);
 			opts_out->null_print = defGetString(defel);
 		}
+		else if (strcmp(defel->defname, "default") == 0)
+		{
+			if (opts_out->default_print)
+				errorConflictingDefElem(defel, pstate);
+			opts_out->default_print = defGetString(defel);
+		}
 		else if (strcmp(defel->defname, "header") == 0)
 		{
 			if (header_specified)
@@ -573,6 +579,11 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify NULL in BINARY mode")));
 
+	if (opts_out->binary && opts_out->default_print)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify DEFAULT in BINARY mode")));
+
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
@@ -608,6 +619,17 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("COPY null representation cannot use newline or carriage return")));
 
+	if (opts_out->default_print)
+	{
+		opts_out->default_print_len = strlen(opts_out->default_print);
+
+		if (strchr(opts_out->default_print, '\r') != NULL ||
+			strchr(opts_out->default_print, '\n') != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("COPY default representation cannot use newline or carriage return")));
+	}
+
 	/*
 	 * Disallow unsafe delimiter characters in non-CSV mode.  We can't allow
 	 * backslash because it would be ambiguous.  We can't allow the other
@@ -701,6 +723,35 @@ ProcessCopyOptions(ParseState *pstate,
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("CSV quote character must not appear in the NULL specification")));
+
+	if (opts_out->default_print)
+	{
+		if (!is_from)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY DEFAULT only available using COPY FROM")));
+
+		/* Don't allow the delimiter to appear in the default string. */
+		if (strchr(opts_out->default_print, opts_out->delim[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY delimiter must not appear in the DEFAULT specification")));
+
+		/* Don't allow the CSV quote char to appear in the default string. */
+		if (opts_out->csv_mode &&
+			strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("CSV quote character must not appear in the DEFAULT specification")));
+
+		/* Don't allow the NULL and DEFAULT string to be the same */
+		if (opts_out->null_print_len == opts_out->default_print_len &&
+			strncmp(opts_out->null_print, opts_out->default_print,
+			opts_out->null_print_len) == 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("NULL specification and DEFAULT specification cannot be the same")));
+	}
 }
 
 /*
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 175aa837f2..3ae3ab985e 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1417,12 +1417,12 @@ BeginCopyFrom(ParseState *pstate,
 							 &in_func_oid, &typioparams[attnum - 1]);
 		fmgr_info(in_func_oid, &in_functions[attnum - 1]);
 
-		/* Get default info if needed */
-		if (!list_member_int(cstate->attnumlist, attnum) && !att->attgenerated)
+		/* Get default info if available */
+		defexprs[attnum - 1] = NULL;
+
+		if(!att->attgenerated)
 		{
-			/* attribute is NOT to be copied from input */
-			/* use default value if one exists */
-			Expr	   *defexpr = (Expr *) build_column_default(cstate->rel,
+			Expr       *defexpr = (Expr *) build_column_default(cstate->rel,
 																attnum);
 
 			if (defexpr != NULL)
@@ -1431,9 +1431,15 @@ BeginCopyFrom(ParseState *pstate,
 				defexpr = expression_planner(defexpr);
 
 				/* Initialize executable expression in copycontext */
-				defexprs[num_defaults] = ExecInitExpr(defexpr, NULL);
-				defmap[num_defaults] = attnum - 1;
-				num_defaults++;
+				defexprs[attnum - 1] = ExecInitExpr(defexpr, NULL);
+
+				/* if NOT copied from input */
+				/* use default value if one exists */
+				if (!list_member_int(cstate->attnumlist, attnum))
+				{
+					defmap[num_defaults] = attnum - 1;
+					num_defaults++;
+				}
 
 				/*
 				 * If a default expression looks at the table being loaded,
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 097414ef12..fa9df39fd2 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -151,8 +151,8 @@ static const char BinarySignature[11] = "PGCOPY\n\377\r\n\0";
 /* non-export function prototypes */
 static bool CopyReadLine(CopyFromState cstate);
 static bool CopyReadLineText(CopyFromState cstate);
-static int	CopyReadAttributesText(CopyFromState cstate);
-static int	CopyReadAttributesCSV(CopyFromState cstate);
+static int	CopyReadAttributesText(CopyFromState cstate, bool *defaults);
+static int	CopyReadAttributesCSV(CopyFromState cstate, bool *defaults);
 static Datum CopyReadBinaryAttribute(CopyFromState cstate, FmgrInfo *flinfo,
 									 Oid typioparam, int32 typmod,
 									 bool *isnull);
@@ -751,7 +751,7 @@ CopyReadBinaryData(CopyFromState cstate, char *dest, int nbytes)
  * NOTE: force_not_null option are not applied to the returned fields.
  */
 bool
-NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
+NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields, bool *defaults)
 {
 	int			fldct;
 	bool		done;
@@ -775,9 +775,9 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 			int			fldnum;
 
 			if (cstate->opts.csv_mode)
-				fldct = CopyReadAttributesCSV(cstate);
+				fldct = CopyReadAttributesCSV(cstate, defaults);
 			else
-				fldct = CopyReadAttributesText(cstate);
+				fldct = CopyReadAttributesText(cstate, defaults);
 
 			if (fldct != list_length(cstate->attnumlist))
 				ereport(ERROR,
@@ -830,9 +830,9 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 
 	/* Parse the line into de-escaped field values */
 	if (cstate->opts.csv_mode)
-		fldct = CopyReadAttributesCSV(cstate);
+		fldct = CopyReadAttributesCSV(cstate, defaults);
 	else
-		fldct = CopyReadAttributesText(cstate);
+		fldct = CopyReadAttributesText(cstate, defaults);
 
 	*fields = cstate->raw_fields;
 	*nfields = fldct;
@@ -842,9 +842,10 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 /*
  * Read next tuple from file for COPY FROM. Return false if no more tuples.
  *
- * 'econtext' is used to evaluate default expression for each column not
- * read from the file. It can be NULL when no default values are used, i.e.
- * when all columns are read from the file.
+ * 'econtext' is used to evaluate default expression for each column that is
+ * either not read from the file or is using the DEFAULT option of COPY FROM.
+ * It can be NULL when no default values are used, i.e. when all columns are
+ * read from the file, and DEFAULT option is unset.
  *
  * 'values' and 'nulls' arrays must be the same length as columns of the
  * relation passed to BeginCopyFrom. This function fills the arrays.
@@ -862,6 +863,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	int			i;
 	int		   *defmap = cstate->defmap;
 	ExprState **defexprs = cstate->defexprs;
+	bool	   *defaults;
 
 	tupDesc = RelationGetDescr(cstate->rel);
 	num_phys_attrs = tupDesc->natts;
@@ -870,6 +872,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	/* Initialize all values for row to NULL */
 	MemSet(values, 0, num_phys_attrs * sizeof(Datum));
 	MemSet(nulls, true, num_phys_attrs * sizeof(bool));
+	defaults = (bool *) palloc0(num_phys_attrs * sizeof(bool));
 
 	if (!cstate->opts.binary)
 	{
@@ -880,7 +883,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		char	   *string;
 
 		/* read raw fields in the next line */
-		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct))
+		if (!NextCopyFromRawFields(cstate, &field_strings, &fldct, defaults))
 			return false;
 
 		/* check for overflowing fields */
@@ -938,12 +941,27 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
 			cstate->cur_attname = NameStr(att->attname);
 			cstate->cur_attval = string;
-			values[m] = InputFunctionCall(&in_functions[m],
-										  string,
-										  typioparams[m],
-										  att->atttypmod);
+
 			if (string != NULL)
 				nulls[m] = false;
+
+			if (defaults[m])
+			{
+				/*
+				 * The caller must supply econtext and have switched into the
+				 * per-tuple memory context in it.
+				 */
+				Assert(econtext != NULL);
+				Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
+
+				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
+			}
+			else
+				values[m] = InputFunctionCall(&in_functions[m],
+											  string,
+											  typioparams[m],
+											  att->atttypmod);
+
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
 		}
@@ -1019,10 +1037,12 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		Assert(econtext != NULL);
 		Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
 
-		values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
+		values[defmap[i]] = ExecEvalExpr(defexprs[defmap[i]], econtext,
 										 &nulls[defmap[i]]);
 	}
 
+	pfree(defaults);
+
 	return true;
 }
 
@@ -1475,7 +1495,7 @@ GetDecimalFromHex(char hex)
  * The return value is the number of fields actually read.
  */
 static int
-CopyReadAttributesText(CopyFromState cstate)
+CopyReadAttributesText(CopyFromState cstate, bool *defaults)
 {
 	char		delimc = cstate->opts.delim[0];
 	int			fieldno;
@@ -1663,6 +1683,31 @@ CopyReadAttributesText(CopyFromState cstate)
 		if (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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-indexed and attnum is 1-indexed */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 		else
 		{
 			/*
@@ -1703,7 +1748,7 @@ CopyReadAttributesText(CopyFromState cstate)
  * "standard" (i.e. common) CSV usage.
  */
 static int
-CopyReadAttributesCSV(CopyFromState cstate)
+CopyReadAttributesCSV(CopyFromState cstate, bool *defaults)
 {
 	char		delimc = cstate->opts.delim[0];
 	char		quotec = cstate->opts.quote[0];
@@ -1852,6 +1897,31 @@ endfield:
 		if (!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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-index and attnum is 1-index */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 
 		fieldno++;
 		/* Done if we hit EOL instead of a delim */
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index f447845717..698df2fe53 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -325,4 +325,29 @@ is($row_count, '10',
 	'client-side error commits transaction, no ON_ERROR_STOP and multiple -c switches'
 );
 
+# Test \copy from with DEFAULT option
+$node->safe_psql(
+	'postgres',
+	"CREATE TABLE copy_default (
+		id integer PRIMARY KEY,
+		text_value text NOT NULL DEFAULT 'test',
+		ts_value timestamp without time zone NOT NULL DEFAULT '2022-07-05'
+	)"
+);
+
+my $copy_default_sql_file = "$tempdir/copy_default.csv";
+append_to_file($copy_default_sql_file, "1,value,2022-07-04\n");
+append_to_file($copy_default_sql_file, "2,placeholder,2022-07-03\n");
+append_to_file($copy_default_sql_file, "3,placeholder,placeholder\n");
+
+psql_like(
+	$node,
+	"\\copy copy_default from $copy_default_sql_file with (format 'csv', default 'placeholder');
+	SELECT * FROM copy_default",
+	qr/1\|value\|2022-07-04 00:00:00
+2|test|2022-07-03 00:00:00
+3|test|2022-07-05 00:00:00/,
+	'\copy from with DEFAULT'
+);
+
 done_testing();
diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl
index 4aa6dd5fe1..08271713bc 100644
--- a/src/bin/psql/t/010_tab_completion.pl
+++ b/src/bin/psql/t/010_tab_completion.pl
@@ -442,6 +442,14 @@ check_completion("blarg \t\t", qr//, "check completion failure path");
 
 clear_query();
 
+# check COPY FROM with DEFAULT option
+check_completion(
+	"COPY foo FROM stdin WITH ( DEF\t)",
+	qr/DEFAULT /,
+	"COPY FROM with DEFAULT completion");
+
+clear_line();
+
 # send psql an explicit \q to shut it down, else pty won't close properly
 $timer->start($PostgreSQL::Test::Utils::timeout_default);
 $in .= "\\q\n";
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 584d9d5ae6..0e933c387a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2757,7 +2757,7 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING");
+					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index 3f6677b132..49053943b7 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -47,6 +47,8 @@ typedef struct CopyFormatOptions
 	char	   *null_print;		/* NULL marker string (server encoding!) */
 	int			null_print_len; /* length of same */
 	char	   *null_print_client;	/* same converted to file encoding */
+	char       *default_print;  /* DEFAULT marker string */
+	int         default_print_len;  /* length of same */
 	char	   *delim;			/* column delimiter (must be 1 byte) */
 	char	   *quote;			/* CSV quote char (must be 1 byte) */
 	char	   *escape;			/* CSV escape char (must be 1 byte) */
@@ -79,7 +81,7 @@ extern void EndCopyFrom(CopyFromState cstate);
 extern bool NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 						 Datum *values, bool *nulls);
 extern bool NextCopyFromRawFields(CopyFromState cstate,
-								  char ***fields, int *nfields);
+								  char ***fields, int *nfields, bool *defaults);
 extern void CopyFromErrorCallback(void *arg);
 
 extern uint64 CopyFrom(CopyFromState cstate);
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index e37c6032ae..61a658bead 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -87,11 +87,11 @@ typedef struct CopyFromStateData
 	 */
 	MemoryContext copycontext;	/* per-copy execution context */
 
-	AttrNumber	num_defaults;
+	AttrNumber	num_defaults;	/* count of att that are missing and have default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
-	int		   *defmap;			/* array of default att numbers */
-	ExprState **defexprs;		/* array of default att expressions */
+	int		   *defmap;			/* array of default att numbers related to missing att */
+	ExprState **defexprs;		/* array of default att expressions for all att */
 	bool		volatile_defexprs;	/* is any of defexprs volatile? */
 	List	   *range_table;
 	ExprState  *qualexpr;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 5f3685e9ef..aabda29eb9 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -663,3 +663,103 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+--
+-- COPY DEFAULT
+-- the following tests are responsible for testing DEFAULT option of COPY FROM
+--
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | D          | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv);
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | \D         | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+ERROR:  cannot specify DEFAULT in BINARY mode
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+ERROR:  COPY default representation cannot use newline or carriage return
+copy copy_default from stdin with (default E'\r');
+ERROR:  COPY default representation cannot use newline or carriage return
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+ERROR:  COPY delimiter must not appear in the DEFAULT specification
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+ERROR:  CSV quote character must not appear in the DEFAULT specification
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+ERROR:  NULL specification and DEFAULT specification cannot be the same
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D	value	'2022-07-04'"
+copy copy_default from stdin with (format csv, default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D,value,2022-07-04"
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \D         | Mon Jul 04 00:00:00 2022
+  3 | "D"        | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \\D        | Mon Jul 04 00:00:00 2022
+  3 | \D         | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in COPY TO
+copy (select 1 as test) TO stdout with (default '\D');
+ERROR:  COPY DEFAULT only available using COPY FROM
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index b3c16af48e..41b36f5d1d 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -468,3 +468,107 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+
+--
+-- COPY DEFAULT
+-- the following tests are responsible for testing DEFAULT option of COPY FROM
+--
+
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+1	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv);
+1,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+copy copy_default from stdin with (default E'\r');
+
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+\D	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+copy copy_default from stdin with (format csv, default '\D');
+\D,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+1	\D	'2022-07-04'
+2	\\D	'2022-07-04'
+3	"\D"	'2022-07-04'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,\D,2022-07-04
+2,\\D,2022-07-04
+3,"\D",2022-07-04
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+1	value	'2022-07-04'
+2	\D	'2022-07-03'
+3	\D	\D
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
+\.
+
+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

From 3a08629861e57aa888fc0c47847cc809ea5ee862 Mon Sep 17 00:00:00 2001
From: Israel Barth <barthisrael@gmail.com>
Date: Fri, 2 Dec 2022 10:13:08 -0300
Subject: [PATCH v6] Added support for DEFAULT in COPY FROM

Previous to this commit, COPY FROM command used to load the column
DEFAULT value only if the column was missing in the command specification.

With this commit we introduce a new feature that works like the NULL
feature from COPY command. The user will be able to specify a marker,
and whenever that marker is found in the input of COPY FROM, it will
be replaced with the DEFAULT value of the corresponding column.

In order to make "the new" COPY from backward compatible, the DEFAULT
feature will only take place if the use specifies some value for that
option.

We are taking advantage of the code that was already implemented in
COPY FROM to find and evaluate the DEFAULT expressions, with the only
difference that now we check both for columns that are missing in the
command specification, and for columns where the DEFAULT marker was
found.

This commit also changes the `file_fdw` to make it possible to use
the new DEFAULT option of COPY FROM. We had to introduce the tuple
context in order to be able to evaluate the DEFAULT value in `file_fdw`.

Signed-off-by: Israel Barth <barthisrael@gmail.com>
---
 contrib/file_fdw/data/copy_default.csv   |   3 +
 contrib/file_fdw/expected/file_fdw.out   |  17 ++++
 contrib/file_fdw/file_fdw.c              |  19 ++++-
 contrib/file_fdw/sql/file_fdw.sql        |  11 +++
 doc/src/sgml/ref/copy.sgml               |  14 +++
 src/backend/commands/copy.c              |  51 +++++++++++
 src/backend/commands/copyfrom.c          |  22 +++--
 src/backend/commands/copyfromparse.c     |  85 ++++++++++++++++--
 src/bin/psql/t/001_basic.pl              |  25 ++++++
 src/bin/psql/t/010_tab_completion.pl     |   8 ++
 src/bin/psql/tab-complete.c              |   2 +-
 src/include/commands/copy.h              |   2 +
 src/include/commands/copyfrom_internal.h |   7 +-
 src/test/regress/expected/copy2.out      | 100 ++++++++++++++++++++++
 src/test/regress/sql/copy2.sql           | 104 +++++++++++++++++++++++
 15 files changed, 447 insertions(+), 23 deletions(-)
 create mode 100644 contrib/file_fdw/data/copy_default.csv

diff --git a/contrib/file_fdw/data/copy_default.csv b/contrib/file_fdw/data/copy_default.csv
new file mode 100644
index 0000000000..5e83a15db4
--- /dev/null
+++ b/contrib/file_fdw/data/copy_default.csv
@@ -0,0 +1,3 @@
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
diff --git a/contrib/file_fdw/expected/file_fdw.out b/contrib/file_fdw/expected/file_fdw.out
index 36d76ba26c..f5ae29732a 100644
--- a/contrib/file_fdw/expected/file_fdw.out
+++ b/contrib/file_fdw/expected/file_fdw.out
@@ -424,6 +424,23 @@ SELECT a, c FROM gft1;
 (2 rows)
 
 DROP FOREIGN TABLE gft1;
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+DROP FOREIGN TABLE copy_default;
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/contrib/file_fdw/file_fdw.c b/contrib/file_fdw/file_fdw.c
index 67821cd25b..bbf585250b 100644
--- a/contrib/file_fdw/file_fdw.c
+++ b/contrib/file_fdw/file_fdw.c
@@ -72,6 +72,7 @@ static const struct FileFdwOption valid_options[] = {
 	{"quote", ForeignTableRelationId},
 	{"escape", ForeignTableRelationId},
 	{"null", ForeignTableRelationId},
+	{"default", ForeignTableRelationId},
 	{"encoding", ForeignTableRelationId},
 	{"force_not_null", AttributeRelationId},
 	{"force_null", AttributeRelationId},
@@ -712,6 +713,9 @@ static TupleTableSlot *
 fileIterateForeignScan(ForeignScanState *node)
 {
 	FileFdwExecutionState *festate = (FileFdwExecutionState *) node->fdw_state;
+	EState	    *estate = CreateExecutorState();
+	ExprContext *econtext;
+	MemoryContext oldcontext;
 	TupleTableSlot *slot = node->ss.ss_ScanTupleSlot;
 	bool		found;
 	ErrorContextCallback errcallback;
@@ -728,15 +732,24 @@ fileIterateForeignScan(ForeignScanState *node)
 	 * ExecStoreVirtualTuple.  If we don't find another row in the file, we
 	 * just skip the last step, leaving the slot empty as required.
 	 *
-	 * We can pass ExprContext = NULL because we read all columns from the
-	 * file, so no need to evaluate default expressions.
+	 * We pass ExprContext because of the possibility of usage of DEFAULT
+	 * option in COPY FROM, so we may need to evaluate default expressions.
 	 */
 	ExecClearTuple(slot);
-	found = NextCopyFrom(festate->cstate, NULL,
+	econtext = GetPerTupleExprContext(estate);
+	/*
+	 * Switch to per-tuple context before calling NextCopyFrom, which does
+	 * evaluate default expressions etc. and requires per-tuple context.
+	 */
+	oldcontext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+	found = NextCopyFrom(festate->cstate, econtext,
 						 slot->tts_values, slot->tts_isnull);
 	if (found)
 		ExecStoreVirtualTuple(slot);
 
+	/* Switch back to original memory context */
+	MemoryContextSwitchTo(oldcontext);
+
 	/* Remove error callback. */
 	error_context_stack = errcallback.previous;
 
diff --git a/contrib/file_fdw/sql/file_fdw.sql b/contrib/file_fdw/sql/file_fdw.sql
index 46670397ca..f0548e14e1 100644
--- a/contrib/file_fdw/sql/file_fdw.sql
+++ b/contrib/file_fdw/sql/file_fdw.sql
@@ -233,6 +233,17 @@ OPTIONS (format 'csv', filename :'filename', delimiter ',');
 SELECT a, c FROM gft1;
 DROP FOREIGN TABLE gft1;
 
+-- copy default tests
+\set filename :abs_srcdir '/data/copy_default.csv'
+CREATE FOREIGN TABLE copy_default (
+	id integer,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+) SERVER file_server
+OPTIONS (format 'csv', filename :'filename', default '\D');
+SELECT id, text_value, ts_value FROM copy_default;
+DROP FOREIGN TABLE copy_default;
+
 -- privilege tests
 SET ROLE regress_file_fdw_superuser;
 SELECT * FROM agg_text ORDER BY a;
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index c25b52d0cb..165fc11f04 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -43,6 +43,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
+    DEFAULT '<replaceable class="parameter">default_string</replaceable>'
 </synopsis>
  </refsynopsisdiv>
 
@@ -368,6 +369,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>DEFAULT</literal></term>
+    <listitem>
+     <para>
+      Specifies the string that represents a default value. Each time this string
+      is found in the input file, the default value of the corresponding column
+      will be used.
+      This option is allowed only in <command>COPY FROM</command>, and only when
+      not using <literal>binary</literal> format.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>WHERE</literal></term>
     <listitem>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index db4c9dbc23..1676762031 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -460,6 +460,12 @@ ProcessCopyOptions(ParseState *pstate,
 				errorConflictingDefElem(defel, pstate);
 			opts_out->null_print = defGetString(defel);
 		}
+		else if (strcmp(defel->defname, "default") == 0)
+		{
+			if (opts_out->default_print)
+				errorConflictingDefElem(defel, pstate);
+			opts_out->default_print = defGetString(defel);
+		}
 		else if (strcmp(defel->defname, "header") == 0)
 		{
 			if (header_specified)
@@ -573,6 +579,11 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_SYNTAX_ERROR),
 				 errmsg("cannot specify NULL in BINARY mode")));
 
+	if (opts_out->binary && opts_out->default_print)
+		ereport(ERROR,
+				(errcode(ERRCODE_SYNTAX_ERROR),
+				 errmsg("cannot specify DEFAULT in BINARY mode")));
+
 	/* Set defaults for omitted options */
 	if (!opts_out->delim)
 		opts_out->delim = opts_out->csv_mode ? "," : "\t";
@@ -608,6 +619,17 @@ ProcessCopyOptions(ParseState *pstate,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("COPY null representation cannot use newline or carriage return")));
 
+	if (opts_out->default_print)
+	{
+		opts_out->default_print_len = strlen(opts_out->default_print);
+
+		if (strchr(opts_out->default_print, '\r') != NULL ||
+			strchr(opts_out->default_print, '\n') != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("COPY default representation cannot use newline or carriage return")));
+	}
+
 	/*
 	 * Disallow unsafe delimiter characters in non-CSV mode.  We can't allow
 	 * backslash because it would be ambiguous.  We can't allow the other
@@ -701,6 +723,35 @@ ProcessCopyOptions(ParseState *pstate,
 		ereport(ERROR,
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("CSV quote character must not appear in the NULL specification")));
+
+	if (opts_out->default_print)
+	{
+		if (!is_from)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY DEFAULT only available using COPY FROM")));
+
+		/* Don't allow the delimiter to appear in the default string. */
+		if (strchr(opts_out->default_print, opts_out->delim[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("COPY delimiter must not appear in the DEFAULT specification")));
+
+		/* Don't allow the CSV quote char to appear in the default string. */
+		if (opts_out->csv_mode &&
+			strchr(opts_out->default_print, opts_out->quote[0]) != NULL)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("CSV quote character must not appear in the DEFAULT specification")));
+
+		/* Don't allow the NULL and DEFAULT string to be the same */
+		if (opts_out->null_print_len == opts_out->default_print_len &&
+			strncmp(opts_out->null_print, opts_out->default_print,
+			opts_out->null_print_len) == 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("NULL specification and DEFAULT specification cannot be the same")));
+	}
 }
 
 /*
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 504afcb811..c3cf660786 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -1562,12 +1562,12 @@ BeginCopyFrom(ParseState *pstate,
 							 &in_func_oid, &typioparams[attnum - 1]);
 		fmgr_info(in_func_oid, &in_functions[attnum - 1]);
 
-		/* Get default info if needed */
-		if (!list_member_int(cstate->attnumlist, attnum) && !att->attgenerated)
+		/* Get default info if available */
+		defexprs[attnum - 1] = NULL;
+
+		if(!att->attgenerated)
 		{
-			/* attribute is NOT to be copied from input */
-			/* use default value if one exists */
-			Expr	   *defexpr = (Expr *) build_column_default(cstate->rel,
+			Expr       *defexpr = (Expr *) build_column_default(cstate->rel,
 																attnum);
 
 			if (defexpr != NULL)
@@ -1576,9 +1576,15 @@ BeginCopyFrom(ParseState *pstate,
 				defexpr = expression_planner(defexpr);
 
 				/* Initialize executable expression in copycontext */
-				defexprs[num_defaults] = ExecInitExpr(defexpr, NULL);
-				defmap[num_defaults] = attnum - 1;
-				num_defaults++;
+				defexprs[attnum - 1] = ExecInitExpr(defexpr, NULL);
+
+				/* if NOT copied from input */
+				/* use default value if one exists */
+				if (!list_member_int(cstate->attnumlist, attnum))
+				{
+					defmap[num_defaults] = attnum - 1;
+					num_defaults++;
+				}
 
 				/*
 				 * If a default expression looks at the table being loaded,
diff --git a/src/backend/commands/copyfromparse.c b/src/backend/commands/copyfromparse.c
index 097414ef12..400daa04bc 100644
--- a/src/backend/commands/copyfromparse.c
+++ b/src/backend/commands/copyfromparse.c
@@ -842,9 +842,10 @@ NextCopyFromRawFields(CopyFromState cstate, char ***fields, int *nfields)
 /*
  * Read next tuple from file for COPY FROM. Return false if no more tuples.
  *
- * 'econtext' is used to evaluate default expression for each column not
- * read from the file. It can be NULL when no default values are used, i.e.
- * when all columns are read from the file.
+ * 'econtext' is used to evaluate default expression for each column that is
+ * either not read from the file or is using the DEFAULT option of COPY FROM.
+ * It can be NULL when no default values are used, i.e. when all columns are
+ * read from the file, and DEFAULT option is unset.
  *
  * 'values' and 'nulls' arrays must be the same length as columns of the
  * relation passed to BeginCopyFrom. This function fills the arrays.
@@ -870,6 +871,7 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 	/* Initialize all values for row to NULL */
 	MemSet(values, 0, num_phys_attrs * sizeof(Datum));
 	MemSet(nulls, true, num_phys_attrs * sizeof(bool));
+	cstate->defaults = (bool *) palloc0(num_phys_attrs * sizeof(bool));
 
 	if (!cstate->opts.binary)
 	{
@@ -938,12 +940,27 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 
 			cstate->cur_attname = NameStr(att->attname);
 			cstate->cur_attval = string;
-			values[m] = InputFunctionCall(&in_functions[m],
-										  string,
-										  typioparams[m],
-										  att->atttypmod);
+
 			if (string != NULL)
 				nulls[m] = false;
+
+			if (cstate->defaults[m])
+			{
+				/*
+				 * The caller must supply econtext and have switched into the
+				 * per-tuple memory context in it.
+				 */
+				Assert(econtext != NULL);
+				Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
+
+				values[m] = ExecEvalExpr(defexprs[m], econtext, &nulls[m]);
+			}
+			else
+				values[m] = InputFunctionCall(&in_functions[m],
+											  string,
+											  typioparams[m],
+											  att->atttypmod);
+
 			cstate->cur_attname = NULL;
 			cstate->cur_attval = NULL;
 		}
@@ -1019,10 +1036,12 @@ NextCopyFrom(CopyFromState cstate, ExprContext *econtext,
 		Assert(econtext != NULL);
 		Assert(CurrentMemoryContext == econtext->ecxt_per_tuple_memory);
 
-		values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
+		values[defmap[i]] = ExecEvalExpr(defexprs[defmap[i]], econtext,
 										 &nulls[defmap[i]]);
 	}
 
+	pfree(cstate->defaults);
+
 	return true;
 }
 
@@ -1663,6 +1682,31 @@ CopyReadAttributesText(CopyFromState cstate)
 		if (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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-indexed and attnum is 1-indexed */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				cstate->defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 		else
 		{
 			/*
@@ -1852,6 +1896,31 @@ endfield:
 		if (!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 (cstate->opts.default_print &&
+				 input_len == cstate->opts.default_print_len &&
+				 strncmp(start_ptr, cstate->opts.default_print, input_len) == 0)
+		{
+			/* fieldno is 0-index and attnum is 1-index */
+			int m = list_nth_int(cstate->attnumlist, fieldno) - 1;
+
+			if (cstate->defexprs[m] != NULL)
+			{
+				/* defaults contain entries for all physical attributes */
+				cstate->defaults[m] = true;
+			}
+			else
+			{
+				TupleDesc         tupDesc = RelationGetDescr(cstate->rel);
+				Form_pg_attribute att = TupleDescAttr(tupDesc, m);
+
+				ereport(ERROR,
+						(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
+						 errmsg("unexpected DEFAULT in COPY data"),
+						 errdetail("Column \"%s\" has no DEFAULT value.",
+						 NameStr(att->attname))));
+			}
+		}
 
 		fieldno++;
 		/* Done if we hit EOL instead of a delim */
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index f447845717..698df2fe53 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -325,4 +325,29 @@ is($row_count, '10',
 	'client-side error commits transaction, no ON_ERROR_STOP and multiple -c switches'
 );
 
+# Test \copy from with DEFAULT option
+$node->safe_psql(
+	'postgres',
+	"CREATE TABLE copy_default (
+		id integer PRIMARY KEY,
+		text_value text NOT NULL DEFAULT 'test',
+		ts_value timestamp without time zone NOT NULL DEFAULT '2022-07-05'
+	)"
+);
+
+my $copy_default_sql_file = "$tempdir/copy_default.csv";
+append_to_file($copy_default_sql_file, "1,value,2022-07-04\n");
+append_to_file($copy_default_sql_file, "2,placeholder,2022-07-03\n");
+append_to_file($copy_default_sql_file, "3,placeholder,placeholder\n");
+
+psql_like(
+	$node,
+	"\\copy copy_default from $copy_default_sql_file with (format 'csv', default 'placeholder');
+	SELECT * FROM copy_default",
+	qr/1\|value\|2022-07-04 00:00:00
+2|test|2022-07-03 00:00:00
+3|test|2022-07-05 00:00:00/,
+	'\copy from with DEFAULT'
+);
+
 done_testing();
diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl
index 4aa6dd5fe1..08271713bc 100644
--- a/src/bin/psql/t/010_tab_completion.pl
+++ b/src/bin/psql/t/010_tab_completion.pl
@@ -442,6 +442,14 @@ check_completion("blarg \t\t", qr//, "check completion failure path");
 
 clear_query();
 
+# check COPY FROM with DEFAULT option
+check_completion(
+	"COPY foo FROM stdin WITH ( DEF\t)",
+	qr/DEFAULT /,
+	"COPY FROM with DEFAULT completion");
+
+clear_line();
+
 # send psql an explicit \q to shut it down, else pty won't close properly
 $timer->start($PostgreSQL::Test::Utils::timeout_default);
 $in .= "\\q\n";
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 89e7317c23..e45812bf2f 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2791,7 +2791,7 @@ psql_completion(const char *text, int start, int end)
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "("))
 		COMPLETE_WITH("FORMAT", "FREEZE", "DELIMITER", "NULL",
 					  "HEADER", "QUOTE", "ESCAPE", "FORCE_QUOTE",
-					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING");
+					  "FORCE_NOT_NULL", "FORCE_NULL", "ENCODING", "DEFAULT");
 
 	/* Complete COPY <sth> FROM|TO filename WITH (FORMAT */
 	else if (Matches("COPY|\\copy", MatchAny, "FROM|TO", MatchAny, "WITH", "(", "FORMAT"))
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index b77b935005..cec0d8b9d9 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -47,6 +47,8 @@ typedef struct CopyFormatOptions
 	char	   *null_print;		/* NULL marker string (server encoding!) */
 	int			null_print_len; /* length of same */
 	char	   *null_print_client;	/* same converted to file encoding */
+	char       *default_print;  /* DEFAULT marker string */
+	int         default_print_len;  /* length of same */
 	char	   *delim;			/* column delimiter (must be 1 byte) */
 	char	   *quote;			/* CSV quote char (must be 1 byte) */
 	char	   *escape;			/* CSV escape char (must be 1 byte) */
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 8d9cc5accd..65cb416722 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -91,11 +91,12 @@ typedef struct CopyFromStateData
 	 */
 	MemoryContext copycontext;	/* per-copy execution context */
 
-	AttrNumber	num_defaults;
+	AttrNumber	num_defaults;	/* count of att that are missing and have default value */
 	FmgrInfo   *in_functions;	/* array of input functions for each attrs */
 	Oid		   *typioparams;	/* array of element types for in_functions */
-	int		   *defmap;			/* array of default att numbers */
-	ExprState **defexprs;		/* array of default att expressions */
+	int		   *defmap;			/* array of default att numbers related to missing att */
+	ExprState **defexprs;		/* array of default att expressions for all att */
+	bool	   *defaults;		/* if DEFAULT marker was found for corresponding att */
 	bool		volatile_defexprs;	/* is any of defexprs volatile? */
 	List	   *range_table;
 	ExprState  *qualexpr;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 090ef6c7a8..f9b7547c10 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -680,3 +680,103 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+--
+-- COPY DEFAULT
+-- the following tests are responsible for testing DEFAULT option of COPY FROM
+--
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | D          | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv);
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | \D         | Tue Jul 05 00:00:00 2022
+(2 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+ERROR:  cannot specify DEFAULT in BINARY mode
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+ERROR:  COPY default representation cannot use newline or carriage return
+copy copy_default from stdin with (default E'\r');
+ERROR:  COPY default representation cannot use newline or carriage return
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+ERROR:  COPY delimiter must not appear in the DEFAULT specification
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+ERROR:  CSV quote character must not appear in the DEFAULT specification
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+ERROR:  NULL specification and DEFAULT specification cannot be the same
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D	value	'2022-07-04'"
+copy copy_default from stdin with (format csv, default '\D');
+ERROR:  unexpected DEFAULT in COPY data
+DETAIL:  Column "id" has no DEFAULT value.
+CONTEXT:  COPY copy_default, line 1: "\D,value,2022-07-04"
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \D         | Mon Jul 04 00:00:00 2022
+  3 | "D"        | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | test       | Mon Jul 04 00:00:00 2022
+  2 | \\D        | Mon Jul 04 00:00:00 2022
+  3 | \D         | Mon Jul 04 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+copy copy_default from stdin with (format csv, default '\D');
+select id, text_value, ts_value from copy_default;
+ id | text_value |         ts_value         
+----+------------+--------------------------
+  1 | value      | Mon Jul 04 00:00:00 2022
+  2 | test       | Sun Jul 03 00:00:00 2022
+  3 | test       | Tue Jul 05 00:00:00 2022
+(3 rows)
+
+truncate copy_default;
+-- DEFAULT cannot be used in COPY TO
+copy (select 1 as test) TO stdout with (default '\D');
+ERROR:  COPY DEFAULT only available using COPY FROM
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index b0de82c3aa..cf555701cb 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -478,3 +478,107 @@ DROP TABLE instead_of_insert_tbl;
 DROP VIEW instead_of_insert_tbl_view;
 DROP VIEW instead_of_insert_tbl_view_2;
 DROP FUNCTION fun_instead_of_insert_tbl();
+
+--
+-- COPY DEFAULT
+-- the following tests are responsible for testing DEFAULT option of COPY FROM
+--
+
+create temp table copy_default (
+	id integer primary key,
+	text_value text not null default 'test',
+	ts_value timestamp without time zone not null default '2022-07-05'
+);
+
+-- if DEFAULT is not specified, then it will behave as a regular COPY FROM
+-- to maintain backward compatibility
+copy copy_default from stdin;
+1	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv);
+1,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- DEFAULT cannot be used in binary mode
+copy copy_default from stdin with (format binary, default '\D');
+
+-- DEFAULT cannot be new line nor carriage return
+copy copy_default from stdin with (default E'\n');
+copy copy_default from stdin with (default E'\r');
+
+-- DELIMITER cannot appear in DEFAULT spec
+copy copy_default from stdin with (delimiter ';', default 'test;test');
+
+-- CSV quote cannot appear in DEFAULT spec
+copy copy_default from stdin with (format csv, quote '"', default 'test"test');
+
+-- NULL and DEFAULT spec must be different
+copy copy_default from stdin with (default '\N');
+
+-- cannot use DEFAULT marker in column that has no DEFAULT value
+copy copy_default from stdin with (default '\D');
+\D	value	'2022-07-04'
+2	\D	'2022-07-05'
+\.
+
+copy copy_default from stdin with (format csv, default '\D');
+\D,value,2022-07-04
+2,\D,2022-07-05
+\.
+
+-- how it handles escaping and quoting
+copy copy_default from stdin with (default '\D');
+1	\D	'2022-07-04'
+2	\\D	'2022-07-04'
+3	"\D"	'2022-07-04'
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,\D,2022-07-04
+2,\\D,2022-07-04
+3,"\D",2022-07-04
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+-- successful usage of DEFAULT option in COPY
+copy copy_default from stdin with (default '\D');
+1	value	'2022-07-04'
+2	\D	'2022-07-03'
+3	\D	\D
+\.
+
+select id, text_value, ts_value from copy_default;
+
+truncate copy_default;
+
+copy copy_default from stdin with (format csv, default '\D');
+1,value,2022-07-04
+2,\D,2022-07-03
+3,\D,\D
+\.
+
+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