v1-0001-COPY-ON_CONFLICT-TABLE.patch

From 590c839d9d3f73f6bdaea7746e4bb4730d756590 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Sat, 25 Apr 2026 12:11:20 +0800
Subject: [PATCH v1 1/1] COPY ON_CONFLICT TABLE

not sure how to deal with excludsion constraint
reference: https://web.archive.org/web/20240328094030/https://riggs.business/blog/f/postgresql-todo-2023

discussion: https://postgr.es/m/
commitfest entry: https://commitfest.postgresql.org/patch/
---
 doc/src/sgml/monitoring.sgml             |   6 +-
 doc/src/sgml/ref/copy.sgml               |  90 ++++
 src/backend/commands/copy.c              |  50 +++
 src/backend/commands/copyfrom.c          | 520 ++++++++++++++++++++++-
 src/backend/commands/explain.c           |   3 +-
 src/backend/executor/nodeModifyTable.c   |   2 +-
 src/backend/parser/gram.y                |   1 +
 src/include/commands/copy.h              |   5 +
 src/include/commands/copyfrom_internal.h |   4 +
 src/include/executor/nodeModifyTable.h   |   3 +
 src/include/nodes/nodes.h                |   1 +
 src/test/regress/expected/copy.out       |   8 +
 src/test/regress/expected/copy2.out      |  88 ++++
 src/test/regress/sql/copy.sql            |  11 +
 src/test/regress/sql/copy2.sql           |  86 ++++
 15 files changed, 863 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..0860da3d23b 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6745,9 +6745,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
       </para>
       <para>
        Number of tuples skipped because they contain malformed data.
-       This counter only advances when
-       <literal>ignore</literal> is specified to the <literal>ON_ERROR</literal>
-       option.
+       This counter advances when
+       <literal>ignore</literal> is specified to the <literal>ON_ERROR</literal> option
+       or <literal>table</literal> is specified to the <literal>ON_CONFLICT</literal> option.
       </para></entry>
      </row>
     </tbody>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 4706c9a4410..7410248c0b4 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -44,6 +44,8 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
     FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
     FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
+    ON_CONFLICT <replaceable class="parameter">conflict_action</replaceable>
+    CONFLICT_TABLE <replaceable class="parameter">conflict_table</replaceable>
     ON_ERROR <replaceable class="parameter">error_action</replaceable>
     REJECT_LIMIT <replaceable class="parameter">maxerror</replaceable>
     ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
@@ -440,6 +442,92 @@ COPY (SELECT j FROM (VALUES ('null'::json), (NULL::json)) v(j))
     </listitem>
    </varlistentry>
 
+   <varlistentry id="sql-copy-params-on-conflict">
+    <term><literal>ON_CONFLICT</literal></term>
+    <listitem>
+     <para>
+      Specifies the behavior when a row violates a unique constraint.
+      An <replaceable class="parameter">conflict_action</replaceable> value of
+      <literal>stop</literal> means fail the command, while
+      <literal>table</literal> means save the conflicting input row to table
+      <replaceable class="parameter">conflict_table</replaceable>
+      specified by <literal>CONFLICT_TABLE</literal> and continue with the next one.
+      The default is <literal>stop</literal>.
+     </para>
+     <para>
+      The <literal>table</literal>
+      options are applicable only for <command>COPY FROM</command>
+      when the <literal>FORMAT</literal> is <literal>text</literal> or <literal>csv</literal>.
+     </para>
+     <para>
+      If <literal>ON_CONFLICT</literal> is set to <literal>table</literal>, a
+      <literal>NOTICE</literal> message is emitted at the end of the command
+      reporting the number of rows that were inserted to table <replaceable class="parameter">conflict_table</replaceable>
+      due to unique constraint violation, provided that at least one row was affected.
+     </para>
+     <para>
+      When the <literal>LOG_VERBOSITY</literal> option is set to
+      <literal>verbose</literal>, a <literal>NOTICE</literal> message is emitted
+      for each row insert by <literal>ON_CONFLICT</literal>, containing the
+      input line that violated the unique constraint. When set to
+      <literal>silent</literal>, no messages are emitted regarding discarded rows.
+     </para>
+     <para>
+      This uses the same mechanism as <link linkend="sql-on-conflict"><command>INSERT ... ON CONFLICT</command></link>.
+      However, exclusion constraints are not supported; only <literal>NOT DEFERRABLE</literal>
+      unique constraints are checked for violations.
+     </para>
+    </listitem>
+   </varlistentry>
+
+    <varlistentry id="sql-copy-params-conflict-table">
+    <term><literal>CONFLICT_TABLE</literal></term>
+    <listitem>
+      <para>
+      Specifies a destination table (<replaceable class="parameter">conflict_table</replaceable>)
+      to store details regarding unique constraint violations encountered during
+      the <command>COPY FROM</command> operation.  The target table must define
+      exactly four columns, though the specific column names are not restricted.
+      The required column order and data types are:
+
+      <informaltable>
+      <tgroup cols="2">
+        <thead>
+        <row>
+          <entry>Data Type</entry>
+          <entry>Description</entry>
+        </row>
+        </thead>
+        <tbody>
+        <row>
+          <entry><type>oid</type></entry>
+          <entry>
+          The OID of the destination table for the <command>COPY FROM</command> command.
+          This corresponds to <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>.
+          Note that no formal dependency is maintained; if the referenced table is dropped, this value will persist as a stale reference.
+          </entry>
+        </row>
+        <row>
+          <entry><type>text</type></entry>
+          <entry>The file path of the <command>COPY FROM</command> input.</entry>
+        </row>
+        <row>
+          <entry><type>bigint</type></entry>
+          <entry>The line number within the input source where the unique constraint violation occurred (starting at 1).</entry>
+        </row>
+        <row>
+          <entry><type>text</type></entry>
+          <entry>The raw line text content of the record that caused the violation.</entry>
+        </row>
+        </tbody>
+      </tgroup>
+      </informaltable>
+
+      </para>
+    </listitem>
+    </varlistentry>
+
+
    <varlistentry id="sql-copy-params-on-error">
     <term><literal>ON_ERROR</literal></term>
     <listitem>
@@ -493,6 +581,8 @@ COPY (SELECT j FROM (VALUES ('null'::json), (NULL::json)) v(j))
       If not specified, <literal>ON_ERROR</literal>=<literal>ignore</literal>
       allows an unlimited number of errors, meaning <command>COPY</command> will
       skip all erroneous data.
+      Note: Rows ignored due to unique constraint violations via the
+      <literal>ON_CONFLICT</literal> option do not count toward this limit.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 003b70852bb..6101ebd500f 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -561,6 +561,29 @@ defGetCopyLogVerbosityChoice(DefElem *def, ParseState *pstate)
 	return COPY_LOG_VERBOSITY_DEFAULT;	/* keep compiler quiet */
 }
 
+/*
+ * Extract a OnConflictAction value from a DefElem.
+ */
+static OnConflictAction
+defGetdefGetCopyOnConflictChoice(DefElem *def, ParseState *pstate)
+{
+	char	   *sval;
+
+	sval = defGetString(def);
+	if (pg_strcasecmp(sval, "stop") == 0)
+		return ONCONFLICT_NONE;
+	else if (pg_strcasecmp(sval, "table") == 0)
+		return ONCONFLICT_TABLE;
+
+	ereport(ERROR,
+			errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+	/*- translator: first %s is the name of a COPY option, e.g. ON_ERROR */
+			errmsg("COPY %s \"%s\" not recognized", "ON_CONFLICT", sval),
+			parser_errposition(pstate, def->location));
+
+	return ONCONFLICT_NONE;		/* keep compiler quiet */
+}
+
 /*
  * Process the statement option list for COPY.
  *
@@ -587,9 +610,11 @@ ProcessCopyOptions(ParseState *pstate,
 	bool		freeze_specified = false;
 	bool		header_specified = false;
 	bool		on_error_specified = false;
+	bool		conflict_tbl_specified = false;
 	bool		log_verbosity_specified = false;
 	bool		reject_limit_specified = false;
 	bool		force_array_specified = false;
+	bool		on_conflict_specified = false;
 	ListCell   *option;
 
 	/* Support external use for option sanity checking */
@@ -774,6 +799,21 @@ ProcessCopyOptions(ParseState *pstate,
 			reject_limit_specified = true;
 			opts_out->reject_limit = defGetCopyRejectLimitOption(defel);
 		}
+		else if (strcmp(defel->defname, "on_conflict") == 0)
+		{
+			if (on_conflict_specified)
+				errorConflictingDefElem(defel, pstate);
+			on_conflict_specified = true;
+			opts_out->on_conflict = defGetdefGetCopyOnConflictChoice(defel, pstate);
+		}
+		else if (strcmp(defel->defname, "conflict_table") == 0)
+		{
+			if (conflict_tbl_specified)
+				errorConflictingDefElem(defel, pstate);
+			conflict_tbl_specified = true;
+
+			opts_out->on_conflict_tbl = defGetString(defel);
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -782,6 +822,16 @@ ProcessCopyOptions(ParseState *pstate,
 					 parser_errposition(pstate, defel->location)));
 	}
 
+	if (!(opts_out->on_conflict == ONCONFLICT_TABLE) && conflict_tbl_specified)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("COPY %s requires %s option", "CONFLICT_TABLE", "ON_CONFLICT"));
+
+	if ((opts_out->on_conflict == ONCONFLICT_TABLE) && !conflict_tbl_specified)
+		ereport(ERROR,
+				errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("COPY %s requires %s option", "CONFLICT_TABLE", "ON_CONFLICT"));
+
 	/*
 	 * Check for incompatible options (must do these three before inserting
 	 * defaults)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 64ac3063c61..8017f0b236e 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -42,16 +42,23 @@
 #include "miscadmin.h"
 #include "nodes/miscnodes.h"
 #include "optimizer/optimizer.h"
+#include "parser/parse_relation.h"
 #include "pgstat.h"
 #include "rewrite/rewriteHandler.h"
 #include "storage/fd.h"
+#include "storage/lmgr.h"
 #include "tcop/tcopprot.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/injection_point.h"
 #include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/portal.h"
+#include "utils/regproc.h"
 #include "utils/rel.h"
 #include "utils/snapmgr.h"
 #include "utils/typcache.h"
+#include "utils/syscache.h"
 
 /*
  * No more than this many tuples per CopyMultiInsertBuffer
@@ -120,6 +127,11 @@ static void CopyFromBinaryInFunc(CopyFromState cstate, Oid atttypid,
 								 FmgrInfo *finfo, Oid *typioparam);
 static void CopyFromBinaryStart(CopyFromState cstate, TupleDesc tupDesc);
 static void CopyFromBinaryEnd(CopyFromState cstate);
+static void CopyFromConflictTableCheck(Relation relation);
+static void RangeVarCallbackForCopyConflictTable(const RangeVar *rv, Oid relid, Oid oldrelid,
+												 void *arg);
+static void CopyFromConflictTableInit(CopyFromState cstate);
+static void CopyConflictTablePermissionCheck(ParseState *pstate, Relation rel);
 
 
 /*
@@ -801,6 +813,7 @@ CopyFrom(CopyFromState cstate)
 	bool		has_before_insert_row_trig;
 	bool		has_instead_insert_row_trig;
 	bool		leafpart_use_multi_insert = false;
+	TupleTableSlot *conflictslot = NULL;
 
 	Assert(cstate->rel);
 	Assert(list_length(cstate->range_table) == 1);
@@ -808,6 +821,13 @@ CopyFrom(CopyFromState cstate)
 	if (cstate->opts.on_error != COPY_ON_ERROR_STOP)
 		Assert(cstate->escontext);
 
+	if (cstate->opts.on_conflict == ONCONFLICT_TABLE)
+	{
+		conflictslot = ExecInitExtraTupleSlot(estate,
+											  RelationGetDescr(cstate->conflictRel),
+											  &TTSOpsVirtual);
+	}
+
 	/*
 	 * The target must be a plain, foreign, or partitioned relation, or have
 	 * an INSTEAD OF INSERT row trigger.  (Currently, such triggers are only
@@ -923,7 +943,7 @@ CopyFrom(CopyFromState cstate)
 	/* Verify the named relation is a valid target for INSERT */
 	CheckValidResultRel(resultRelInfo, CMD_INSERT, ONCONFLICT_NONE, NIL);
 
-	ExecOpenIndices(resultRelInfo, false);
+	ExecOpenIndices(resultRelInfo, true);
 
 	/*
 	 * Set up a ModifyTableState so we can let FDW(s) init themselves for
@@ -1052,6 +1072,11 @@ CopyFrom(CopyFromState cstate)
 		 */
 		insertMethod = CIM_SINGLE;
 	}
+	else if (cstate->opts.on_conflict != ONCONFLICT_NONE &&
+			 resultRelInfo->ri_NumIndices > 0)
+	{
+		insertMethod = CIM_SINGLE;
+	}
 	else
 	{
 		/*
@@ -1164,7 +1189,7 @@ CopyFrom(CopyFromState cstate)
 
 			/* Report that this tuple was skipped by the ON_ERROR clause */
 			pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
-										 cstate->num_errors);
+										 (cstate->num_conflicts + cstate->num_errors));
 
 			if (cstate->opts.reject_limit > 0 &&
 				cstate->num_errors > cstate->opts.reject_limit)
@@ -1425,15 +1450,218 @@ CopyFrom(CopyFromState cstate)
 					}
 					else
 					{
-						/* OK, store the tuple and create index entries for it */
-						table_tuple_insert(resultRelInfo->ri_RelationDesc,
-										   myslot, mycid, ti_options, bistate);
+						if (cstate->opts.on_conflict == ONCONFLICT_NONE)
+						{
+							/*
+							 * OK, store the tuple and create index entries
+							 * for it
+							 */
+							table_tuple_insert(resultRelInfo->ri_RelationDesc,
+											   myslot, mycid, ti_options, bistate);
 
-						if (resultRelInfo->ri_NumIndices > 0)
+							if (resultRelInfo->ri_NumIndices > 0)
+								recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
+																	   estate, 0,
+																	   myslot, NIL,
+																	   NULL);
+						}
+						else if (resultRelInfo->ri_NumIndices > 0 &&
+								 cstate->opts.on_conflict != ONCONFLICT_NONE)
+						{
+							/* Perform a speculative insertion. */
+							uint32		specToken;
+							ItemPointerData conflictTid;
+							ItemPointerData invalidItemPtr;
+							bool		specConflict;
+							List	   *arbiterIndexes;
+
+							ItemPointerSetInvalid(&invalidItemPtr);
+							arbiterIndexes = resultRelInfo->ri_onConflictArbiterIndexes;
+
+							/*
+							 * Do a non-conclusive check for conflicts first.
+							 *
+							 * We're not holding any locks yet, so this
+							 * doesn't guarantee that the later insert won't
+							 * conflict.  But it avoids leaving behind a lot
+							 * of canceled speculative insertions, if you run
+							 * a lot of INSERT ON CONFLICT statements that do
+							 * conflict.
+							 *
+							 * We loop back here if we find a conflict below,
+							 * either during the pre-check, or when we
+							 * re-check after inserting the tuple
+							 * speculatively.  Better allow interrupts in case
+							 * some bug makes this an infinite loop.
+							 */
+					vlock:
+							CHECK_FOR_INTERRUPTS();
+							specConflict = false;
+							if (!ExecCheckIndexConstraints(resultRelInfo, myslot, estate,
+														   &conflictTid, &invalidItemPtr,
+														   arbiterIndexes))
+							{
+								/*
+								 * This is equivalent to ON CONFLICT DO
+								 * SELECT. We need verify that the tuple is
+								 * visible to the executor's MVCC snapshot at
+								 * higher isolation levels. See comments in
+								 * ExecInsert->ExecCheckIndexConstraints also.
+								 */
+								if (cstate->opts.on_conflict == ONCONFLICT_TABLE)
+								{
+									int			j = 0;
+									Datum	   *newvalues;
+									bool	   *nulls;
+									ModifyTableState *mstate = cstate->conflict_mstate;
+									EState	   *conflict_estate = mstate->ps.state;
+									TupleDesc	tupdesc = RelationGetDescr(cstate->conflictRel);
+
+									if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc,
+																	   &conflictTid,
+																	   SnapshotAny,
+																	   ExecGetReturningSlot(conflict_estate, resultRelInfo)))
+										elog(ERROR, "failed to fetch conflicting tuple for ON CONFLICT");
+
+									ExecCheckTupleVisible(conflict_estate,
+														  resultRelInfo->ri_RelationDesc,
+														  ExecGetReturningSlot(conflict_estate, resultRelInfo));
+
+									ExecClearTuple(conflictslot);
+
+									newvalues = conflictslot->tts_values;
+									nulls = conflictslot->tts_isnull;
+
+									/* Prepare to build the result tuple */
+									for (int i = 0; i < tupdesc->natts; i++)
+									{
+										Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+
+										if (att->attisdropped)
+										{
+											newvalues[i] = (Datum) 0;
+											nulls[i] = true;
+											continue;
+										}
+
+										j++;
+										nulls[i] = false;
+										switch (j)
+										{
+											case 1:
+												newvalues[i] = ObjectIdGetDatum(RelationGetRelid(cstate->rel));
+												break;
+
+											case 2:
+												newvalues[i] = CStringGetTextDatum(cstate->filename ? cstate->filename : "STDIN");
+												break;
+
+											case 3:
+												newvalues[i] = Int64GetDatum((int64) cstate->cur_lineno);
+												break;
+
+											case 4:
+												newvalues[i] = CStringGetTextDatum(pnstrdup(cstate->line_buf.data,
+																							cstate->line_buf.len));
+												break;
+
+											default:
+												elog(ERROR, "COPY ON CONFLICT table must have 4 attributes");
+												break;
+										}
+									}
+
+									/* Build the virtual tuple. */
+									ExecStoreVirtualTuple(conflictslot);
+
+									/*
+									 * Check constraint and not-null
+									 * constraint vertification
+									 */
+									if (tupdesc->constr)
+										ExecConstraints(mstate->resultRelInfo, conflictslot, conflict_estate);
+
+									/* insert the tuple normally */
+									table_tuple_insert(cstate->conflictRel, conflictslot,
+													   conflict_estate->es_output_cid,
+													   0, NULL);
+
+									/* insert index entries for tuple */
+									if (mstate->resultRelInfo->ri_NumIndices > 0)
+										recheckIndexes = ExecInsertIndexTuples(mstate->resultRelInfo,
+																			   conflict_estate,
+																			   0, conflictslot, NIL,
+																			   NULL);
+									list_free(recheckIndexes);
+
+									cstate->num_conflicts++;
+
+									/*
+									 * Report that this tuple was skipped by
+									 * the ON_ERROR or ON_CONFLICT clause
+									 */
+									pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
+																 (cstate->num_conflicts + cstate->num_errors));
+
+									continue;
+								}
+							}
+
+							/*
+							 * Before we start insertion proper, acquire our
+							 * "speculative insertion lock".  Others can use
+							 * that to wait for us to decide if we're going to
+							 * go ahead with the insertion, instead of waiting
+							 * for the whole transaction to complete.
+							 */
+							INJECTION_POINT("exec-copy-insert-before-insert-speculative", NULL);
+							specToken = SpeculativeInsertionLockAcquire(GetCurrentTransactionId());
+
+							/* insert the tuple, with the speculative token */
+							table_tuple_insert_speculative(resultRelInfo->ri_RelationDesc, myslot,
+														   estate->es_output_cid,
+														   0,
+														   NULL,
+														   specToken);
+
+							/* insert index entries for tuple */
 							recheckIndexes = ExecInsertIndexTuples(resultRelInfo,
-																   estate, 0,
-																   myslot, NIL,
-																   NULL);
+																   estate, EIIT_NO_DUPE_ERROR,
+																   myslot, arbiterIndexes,
+																   &specConflict);
+
+							/* adjust the tuple's state accordingly */
+							table_tuple_complete_speculative(resultRelInfo->ri_RelationDesc, myslot,
+															 specToken, !specConflict);
+
+							/*
+							 * Wake up anyone waiting for our decision.  They
+							 * will re-check the tuple, see that it's no
+							 * longer speculative, and wait on our XID as if
+							 * this was a regularly inserted tuple all along.
+							 * Or if we killed the tuple, they will see it's
+							 * dead, and proceed as if the tuple never
+							 * existed.
+							 */
+							SpeculativeInsertionLockRelease(GetCurrentTransactionId());
+
+							/*
+							 * If there was a conflict, start from the
+							 * beginning.  We'll do the pre-check again, which
+							 * will now find the conflicting tuple (unless it
+							 * aborts before we get there).
+							 */
+							if (specConflict)
+							{
+								list_free(recheckIndexes);
+								goto vlock;
+							}
+
+							/*
+							 * Since there was no insertion conflict, we're
+							 * done
+							 */
+						}
 					}
 
 					/* AFTER ROW INSERT Triggers */
@@ -1482,6 +1710,18 @@ CopyFrom(CopyFromState cstate)
 								  cstate->num_errors));
 	}
 
+	if (cstate->num_conflicts > 0 &&
+		cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT)
+	{
+		if (cstate->opts.on_conflict == ONCONFLICT_TABLE)
+			ereport(NOTICE,
+					errmsg_plural("%" PRIu64 " row was saved to conflict table \"%s\" due to unique constraint violation",
+								  "%" PRIu64 " rows were saved to conflict table \"%s\" due to unique constraint violation",
+								  cstate->num_conflicts,
+								  cstate->num_conflicts,
+								  RelationGetRelationName(cstate->conflictRel)));
+	}
+
 	if (bistate != NULL)
 		FreeBulkInsertState(bistate);
 
@@ -1515,6 +1755,17 @@ CopyFrom(CopyFromState cstate)
 
 	FreeExecutorState(estate);
 
+	/* Close/release resouces associated with copy error saving */
+	if (cstate->conflictRel)
+	{
+		ExecResetTupleTable(cstate->conflict_mstate->ps.state->es_tupleTable, false);
+
+		ExecCloseResultRelations(cstate->conflict_mstate->ps.state);
+		ExecCloseRangeTableRelations(cstate->conflict_mstate->ps.state);
+
+		FreeExecutorState(cstate->conflict_mstate->ps.state);
+	}
+
 	return processed;
 }
 
@@ -1634,6 +1885,44 @@ BeginCopyFrom(ParseState *pstate,
 	else
 		cstate->escontext = NULL;
 
+	cstate->conflict_mstate = NULL;
+	if (cstate->opts.on_conflict == ONCONFLICT_TABLE)
+	{
+		Oid			conflictRelid;
+		RangeVar   *relvar;
+		List	   *relname_list;
+
+		Assert(cstate->opts.on_conflict_tbl != NULL);
+
+		relname_list = stringToQualifiedNameList(cstate->opts.on_conflict_tbl, NULL);
+		relvar = makeRangeVarFromNameList(relname_list);
+
+		/*
+		 * We might insert tuples into the conflict error-saving table later,
+		 * so we first need to check its lock status. If it is already heavily
+		 * locked, our subsequent COPY FROM may stuck. Instead of letting COPY
+		 * FROM hang, report an error indicating that the conflict
+		 * error-saving table is under heavy lock.
+		 */
+		conflictRelid = RangeVarGetRelidExtended(relvar,
+												 RowExclusiveLock,
+												 RVR_NOWAIT,
+												 RangeVarCallbackForCopyConflictTable,
+												 NULL);
+
+		if (RelationGetRelid(cstate->rel) == conflictRelid)
+			ereport(ERROR,
+					errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					errmsg("cannot use relation \"%s\" for COPY on_conflict error saving while copying data to it",
+						   cstate->opts.on_conflict_tbl));
+
+		cstate->conflictRel = table_open(conflictRelid, NoLock);
+
+		CopyFromConflictTableInit(cstate);
+
+		table_close(cstate->conflictRel, NoLock);
+	}
+
 	if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
 	{
 		int			attr_count = list_length(cstate->attnumlist);
@@ -1998,3 +2287,216 @@ ClosePipeFromProgram(CopyFromState cstate)
 				 errdetail_internal("%s", wait_result_to_str(pclose_rc))));
 	}
 }
+
+/*
+ * The conflict_table must be a plain table and is subject to the following
+ * restrictions: it cannot have foreign key constraints; nor can it have column
+ * DEFAULT values, triggers, rules, or row-level security policies.
+ *
+ * These restrictions are necessary to allow the use of table_tuple_insert();
+ * otherwise, the executor would need to perform extensive additional checks and
+ * setup for each inserted error row.
+ */
+static void
+CopyFromConflictTableCheck(Relation relation)
+{
+	int			valid_col_count = 0;
+	TupleDesc	tupDesc = RelationGetDescr(relation);
+	char	   *errdetail_msg = NULL;
+
+	if (tupDesc->constr)
+	{
+		if (tupDesc->constr->has_generated_stored || tupDesc->constr->has_generated_virtual)
+			errdetail_msg = _("The conflict_table cannot have generated columns.");
+	}
+
+	if (!errdetail_msg)
+	{
+		if (list_length(RelationGetFKeyList(relation)) > 0)
+			errdetail_msg = _("The conflict_table cannot have foreign keys.");
+		else if (relation->rd_rules)
+			errdetail_msg = _("The conflict_table cannot have rules.");
+		else if (relation->trigdesc)
+			errdetail_msg = _("The conflict_table cannot have triggers.");
+		else if (relation->rd_rel->relrowsecurity)
+			errdetail_msg = _("The conflict_table cannot have row-level security policies.");
+	}
+
+	if (errdetail_msg)
+		ereport(ERROR,
+				errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("cannot use relation \"%s\" for COPY on_conflict error saving",
+					   RelationGetRelationName(relation)),
+				errdetail_internal("%s", errdetail_msg));
+
+	for (int i = 0; i < tupDesc->natts; i++)
+	{
+		Form_pg_attribute attr = TupleDescAttr(tupDesc, i);
+
+		/* Skip columns marked as dropped */
+		if (attr->attisdropped)
+			continue;
+
+		valid_col_count++;
+
+		/* Check types based on the effective column position */
+		switch (valid_col_count)
+		{
+			case 1:
+				if (attr->atttypid != OIDOID)
+					errdetail_msg = _("The first column of the conflict_table must be type OID.");
+				break;
+			case 2:
+				if (attr->atttypid != TEXTOID)
+					errdetail_msg = _("The second column of the conflict_table must be type TEXT.");
+				break;
+			case 3:
+				if (attr->atttypid != INT8OID)
+					errdetail_msg = _("The third column of the conflict_table must be type BIGINT.");
+				break;
+			case 4:
+				if (attr->atttypid != TEXTOID)
+					errdetail_msg = _("The fourth column of the conflict_table must be type TEXT.");
+				break;
+			default:
+				errdetail_msg = _("The conflict_table must have exactly four columns.");
+				break;
+		}
+	}
+
+	if (valid_col_count != 4)
+		errdetail_msg = _("The conflict_table is incomplete; exactly four columns are required.");
+
+	if (errdetail_msg)
+		ereport(ERROR,
+				errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				errmsg("cannot use relation \"%s\" for COPY on_conflict error saving",
+					   RelationGetRelationName(relation)),
+				errdetail_internal("%s", errdetail_msg));
+}
+
+static void
+CopyFromConflictTableInit(CopyFromState cstate)
+{
+	ParseState *pstate;
+	ResultRelInfo *resultRelInfo;
+	EState	   *estate = CreateExecutorState();
+	ModifyTableState *mtstate;
+	Relation	relation;
+
+	relation = cstate->conflictRel;
+	pstate = make_parsestate(NULL);
+
+	CopyConflictTablePermissionCheck(pstate, relation);
+
+	CopyFromConflictTableCheck(relation);
+
+	/*
+	 * We need a ResultRelInfo so we can use the regular executor's
+	 * index-entry-making machinery.
+	 */
+	ExecInitRangeTable(estate, pstate->p_rtable, pstate->p_rteperminfos,
+					   bms_make_singleton(1));
+	resultRelInfo = makeNode(ResultRelInfo);
+	ExecInitResultRelation(estate, resultRelInfo, 1);
+
+	/* Verify the named relation is a valid target for INSERT */
+	CheckValidResultRel(resultRelInfo, CMD_INSERT, ONCONFLICT_NONE, NIL);
+
+	ExecOpenIndices(resultRelInfo, false);
+
+	/* Set up a ModifyTableState for inserting record to CONFLICT_TABLE */
+	mtstate = makeNode(ModifyTableState);
+	mtstate->ps.plan = NULL;
+	mtstate->ps.state = estate;
+	mtstate->operation = CMD_INSERT;
+	mtstate->mt_nrels = 1;
+	mtstate->resultRelInfo = resultRelInfo;
+	mtstate->rootResultRelInfo = resultRelInfo;
+
+	cstate->conflict_mstate = mtstate;
+}
+
+static void
+CopyConflictTablePermissionCheck(ParseState *pstate, Relation rel)
+{
+	LOCKMODE	lockmode = RowExclusiveLock;
+	ParseNamespaceItem *nsitem;
+	RTEPermissionInfo *perminfo;
+	TupleDesc	tupDesc;
+
+	nsitem = addRangeTableEntryForRelation(pstate, rel, lockmode,
+										   NULL, false, false);
+	perminfo = nsitem->p_perminfo;
+	perminfo->requiredPerms = ACL_INSERT;
+
+	tupDesc = RelationGetDescr(rel);
+
+	for (int i = 0; i < tupDesc->natts; i++)
+	{
+		Bitmapset **bms;
+		int			attno;
+
+		CompactAttribute *attr = TupleDescCompactAttr(tupDesc, i);
+
+		if (attr->attisdropped)
+			continue;
+
+		attno = i + 1 - FirstLowInvalidHeapAttributeNumber;
+		bms = &perminfo->insertedCols;
+
+		*bms = bms_add_member(*bms, attno);
+
+	}
+	ExecCheckPermissions(pstate->p_rtable, list_make1(perminfo), true);
+}
+
+/*
+ * Callback to RangeVarGetRelidExtended().
+ *
+ * Checks the following:
+ *	- the relation specified is a table.
+ *	- current user must have INSERT priviledge on the table.
+ *	- the table is not a system table.
+ *
+ * If any of these checks fails then an error is raised.
+ */
+static void
+RangeVarCallbackForCopyConflictTable(const RangeVar *rv, Oid relid, Oid oldrelid,
+									 void *arg)
+{
+	HeapTuple	tuple;
+	Form_pg_class classform;
+	char		relkind;
+	AclResult	aclresult;
+
+	tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+	if (!HeapTupleIsValid(tuple))
+		return;
+
+	classform = (Form_pg_class) GETSTRUCT(tuple);
+	relkind = classform->relkind;
+
+	/* Must have INSERT privilege */
+	aclresult = pg_class_aclcheck(relid, GetUserId(), ACL_INSERT);
+	if (aclresult != ACLCHECK_OK)
+		aclcheck_error(aclresult, get_relkind_objtype(get_rel_relkind(relid)),
+					   rv->relname);
+
+	/* No system table modifications unless explicitly allowed. */
+	if (!allowSystemTableMods && IsSystemClass(relid, classform))
+		ereport(ERROR,
+				errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				errmsg("permission denied: \"%s\" is a system catalog",
+					   rv->relname));
+
+	/* The conflict error saving table must be a regular realtion */
+	if (relkind != RELKIND_RELATION)
+		ereport(ERROR,
+				errcode(ERRCODE_WRONG_OBJECT_TYPE),
+				errmsg("cannot use relation \"%s\" for COPY on_conflict error saving",
+					   rv->relname),
+				errdetail_relkind_not_supported(relkind));
+
+	ReleaseSysCache(tuple);
+}
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 112c17b0d64..acefcb20498 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -4857,9 +4857,8 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
 			resolution = "NOTHING";
 		else if (node->onConflictAction == ONCONFLICT_UPDATE)
 			resolution = "UPDATE";
-		else
+		else if (node->onConflictAction == ONCONFLICT_SELECT)
 		{
-			Assert(node->onConflictAction == ONCONFLICT_SELECT);
 			switch (node->onConflictLockStrength)
 			{
 				case LCS_NONE:
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 4cb057ca4f9..4f7a3451bc2 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -380,7 +380,7 @@ ExecProcessReturning(ModifyTableContext *context,
  * path) on the basis of another tuple that is not visible to MVCC snapshot.
  * Check for the need to raise a serialization failure, and do so as necessary.
  */
-static void
+extern void
 ExecCheckTupleVisible(EState *estate,
 					  Relation rel,
 					  TupleTableSlot *slot)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..2854f2a884f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3755,6 +3755,7 @@ copy_generic_opt_arg:
 			| NumericOnly					{ $$ = (Node *) $1; }
 			| '*'							{ $$ = (Node *) makeNode(A_Star); }
 			| DEFAULT                       { $$ = (Node *) makeString("default"); }
+			| TABLE                         { $$ = (Node *) makeString("table"); }
 			| '(' copy_generic_opt_arg_list ')'		{ $$ = (Node *) $2; }
 			| /* EMPTY */					{ $$ = NULL; }
 		;
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index abecfe51098..3aeebf24b67 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -36,6 +36,7 @@ typedef enum CopyOnErrorChoice
 	COPY_ON_ERROR_STOP = 0,		/* immediately throw errors, default */
 	COPY_ON_ERROR_IGNORE,		/* ignore errors */
 	COPY_ON_ERROR_SET_NULL,		/* set error field to null */
+	COPY_ON_ERROR_TABLE,		/* saving errors info to table */
 } CopyOnErrorChoice;
 
 /*
@@ -94,9 +95,13 @@ typedef struct CopyFormatOptions
 	bool	   *force_null_flags;	/* per-column CSV FN flags */
 	bool		convert_selectively;	/* do selective binary conversion? */
 	CopyOnErrorChoice on_error; /* what to do when error happened */
+	OnConflictAction on_conflict;	/* what to do when unique conflict
+									 * happened */
 	CopyLogVerbosityChoice log_verbosity;	/* verbosity of logged messages */
 	int64		reject_limit;	/* maximum tolerable number of errors */
 	List	   *convert_select; /* list of column names (can be NIL) */
+	char	   *on_conflict_tbl;	/* on error, save error info to the table,
+									 * table name */
 } CopyFormatOptions;
 
 /* These are private in commands/copy[from|to].c */
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 9d3e244ee55..ec8f6981fae 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -73,6 +73,7 @@ typedef struct CopyFromStateData
 
 	/* parameters from the COPY command */
 	Relation	rel;			/* relation to copy from */
+	Relation	conflictRel;	/* relation for copy from conflict saving */
 	List	   *attnumlist;		/* integer list of attnums to copy */
 	char	   *filename;		/* filename, or NULL for STDIN */
 	bool		is_program;		/* is 'filename' a program to popen? */
@@ -102,6 +103,8 @@ typedef struct CopyFromStateData
 									 * execution */
 	uint64		num_errors;		/* total number of rows which contained soft
 								 * errors */
+	uint64		num_conflicts;	/* total number of rows skipped due to unique
+								 * constraint conflict */
 	int		   *defmap;			/* array of default att numbers related to
 								 * missing att */
 	ExprState **defexprs;		/* array of default att expressions for all
@@ -189,6 +192,7 @@ typedef struct CopyFromStateData
 #define RAW_BUF_BYTES(cstate) ((cstate)->raw_buf_len - (cstate)->raw_buf_index)
 
 	uint64		bytes_processed;	/* number of bytes processed so far */
+	ModifyTableState *conflict_mstate;
 } CopyFromStateData;
 
 extern void ReceiveCopyBegin(CopyFromState cstate);
diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h
index f6070e1cdf3..6d6eba159a5 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -29,5 +29,8 @@ extern void ExecReScanModifyTable(ModifyTableState *node);
 
 extern void ExecInitMergeTupleSlots(ModifyTableState *mtstate,
 									ResultRelInfo *resultRelInfo);
+extern void ExecCheckTupleVisible(EState *estate,
+								  Relation rel,
+								  TupleTableSlot *slot);
 
 #endif							/* NODEMODIFYTABLE_H */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index a2925ae4946..22a329cb810 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -429,6 +429,7 @@ typedef enum OnConflictAction
 	ONCONFLICT_NOTHING,			/* ON CONFLICT ... DO NOTHING */
 	ONCONFLICT_UPDATE,			/* ON CONFLICT ... DO UPDATE */
 	ONCONFLICT_SELECT,			/* ON CONFLICT ... DO SELECT */
+	ONCONFLICT_TABLE,			/* COPY (ON_CONFLICT TABLE) */
 } OnConflictAction;
 
 /*
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 1714faab39c..36fe3c00765 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -430,6 +430,14 @@ copy tab_progress_reporting from :'filename'
 	where (salary < 2000);
 INFO:  progress: {"type": "FILE", "command": "COPY FROM", "relname": "tab_progress_reporting", "tuples_skipped": 0, "has_bytes_total": true, "tuples_excluded": 1, "tuples_processed": 2, "has_bytes_processed": true}
 -- Generate COPY FROM report with PIPE, with some skipped tuples.
+create unique index tab_progress_reporting_idx1 on tab_progress_reporting(name);
+create temp table conflict_tbl(copy_tbl oid, filename text, lineno bigint, line text);
+copy tab_progress_reporting from stdin(on_conflict table, conflict_table 'conflict_tbl');
+NOTICE:  3 rows were saved to conflict table "conflict_tbl" due to unique constraint violation
+INFO:  progress: {"type": "PIPE", "command": "COPY FROM", "relname": "tab_progress_reporting", "tuples_skipped": 3, "has_bytes_total": false, "tuples_excluded": 0, "tuples_processed": 0, "has_bytes_processed": true}
+drop index tab_progress_reporting_idx1;
+drop table conflict_tbl;
+-- Generate COPY FROM report with PIPE, with some skipped tuples.
 copy tab_progress_reporting from stdin(on_error ignore);
 NOTICE:  2 rows were skipped due to data type incompatibility
 INFO:  progress: {"type": "PIPE", "command": "COPY FROM", "relname": "tab_progress_reporting", "tuples_skipped": 2, "has_bytes_total": false, "tuples_excluded": 0, "tuples_processed": 1, "has_bytes_processed": true}
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 7600e5239d2..7c45400c8dd 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -884,7 +884,95 @@ ERROR:  skipped more than REJECT_LIMIT (3) rows due to data type incompatibility
 CONTEXT:  COPY check_ign_err, line 5, column n: ""
 COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 4);
 NOTICE:  4 rows were skipped due to data type incompatibility
+CREATE DOMAIN d_text as TEXT;
+CREATE TABLE t_copy_tbl(a int, b int, c text);
+CREATE TABLE err_tbl0(copy_tbl oid primary key);
+CREATE TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text generated always as ('hh') stored);
+ALTER TABLE err_tbl1 ADD CONSTRAINT con1 FOREIGN KEY (copy_tbl) REFERENCES err_tbl0(copy_tbl);
+CREATE TRIGGER trg_x_after AFTER INSERT ON err_tbl1 FOR EACH ROW EXECUTE PROCEDURE fn_x_after();
+CREATE POLICY p1 ON err_tbl1 FOR SELECT USING (true);
+ALTER TABLE err_tbl1 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE err_tbl1 FORCE ROW LEVEL SECURITY;
+-- The conflict error saving table must be a plain table and is subject to the
+-- following restrictions: it cannot contain foreign key constraints; it must
+-- not have triggers, rules, or row-level security policies.
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+ERROR:  cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL:  The conflict_table cannot have generated columns.
+ALTER TABLE err_tbl1 ALTER COLUMN line DROP EXPRESSION;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+ERROR:  cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL:  The conflict_table cannot have foreign keys.
+ALTER TABLE err_tbl1 DROP CONSTRAINT con1;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+ERROR:  cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL:  The conflict_table cannot have triggers.
+DROP TRIGGER IF EXISTS trg_x_after ON err_tbl1;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+ERROR:  cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL:  The conflict_table cannot have row-level security policies.
+DROP POLICY IF EXISTS p1 ON err_tbl1;
+ALTER TABLE err_tbl1 DISABLE ROW LEVEL SECURITY;
+ALTER TABLE err_tbl1 ALTER COLUMN line SET DATA TYPE d_text;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error
+ERROR:  cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL:  The fourth column of the conflict_table must be type TEXT.
+ALTER TABLE err_tbl1 DROP COLUMN line;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error
+ERROR:  cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL:  The conflict_table is incomplete; exactly four columns are required.
+ALTER TABLE err_tbl1 ADD COLUMN line text, ADD column extra int;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error
+ERROR:  cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL:  The conflict_table is incomplete; exactly four columns are required.
+ALTER TABLE err_tbl1 DROP COLUMN extra;
+CREATE VIEW err_tbl4 AS SELECT * FROM err_tbl1;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl4); -- error
+ERROR:  cannot use relation "err_tbl4" for COPY on_conflict error saving
+DETAIL:  This operation is not supported for views.
+COPY t_copy_tbl(c, b) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose); -- ok
+-- COPY ON_CONFLICT TABLE cannot apply to deferred unqiue constraint
+ALTER TABLE t_copy_tbl ADD CONSTRAINT t_copy_tbl_unq1 UNIQUE (a) DEFERRABLE INITIALLY DEFERRED;
+BEGIN;
+COPY t_copy_tbl FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+ERROR:  ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters
+CONTEXT:  COPY t_copy_tbl, line 1: "1,2,3"
+ROLLBACK;
+ALTER TABLE t_copy_tbl DROP CONSTRAINT t_copy_tbl_unq1;
+ALTER TABLE err_tbl1 ADD CONSTRAINT cc CHECK (lineno > 0);
+ALTER TABLE err_tbl1 ADD CONSTRAINT nn NOT NULL copy_tbl;
+CREATE UNIQUE INDEX ON t_copy_tbl (b) WHERE a = 1;
+CREATE UNIQUE INDEX ON t_copy_tbl ((b+1));
+CREATE UNIQUE INDEX ON t_copy_tbl (c);
+COPY t_copy_tbl(b,a,c) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE,CONFLICT_TABLE err_tbl1, log_verbosity verbose); -- ok
+NOTICE:  2 rows were saved to conflict table "err_tbl1" due to unique constraint violation
+COPY t_copy_tbl(b,a, c) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose);
+NOTICE:  3 rows were saved to conflict table "err_tbl1" due to unique constraint violation
+CREATE TABLE err_tbl6 (
+  id1 int4range,
+  valid_at int4range,
+  CONSTRAINT err_tbl6_uq UNIQUE (id1, valid_at WITHOUT OVERLAPS)
+);
+COPY err_tbl6 FROM STDIN (ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error
+ERROR:  empty WITHOUT OVERLAPS value found in column "valid_at" in relation "err_tbl6"
+CONTEXT:  COPY err_tbl6, line 1: "[11,12)	empty"
+COPY err_tbl6 FROM STDIN (ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+NOTICE:  1 row was saved to conflict table "err_tbl1" due to unique constraint violation
+SELECT copy_tbl::regclass, filename, lineno, line FROM err_tbl1;
+  copy_tbl  | filename | lineno |                                       line                                       
+------------+----------+--------+----------------------------------------------------------------------------------
+ t_copy_tbl | STDIN    |      1 | 2,1,aaa
+ t_copy_tbl | STDIN    |      2 | 2,1,XXX
+ t_copy_tbl | STDIN    |      2 | 6,11,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+ t_copy_tbl | STDIN    |      4 | 12,1,xxxxxxxx
+ t_copy_tbl | STDIN    |      5 | 13,1,xxxxxxxx
+ err_tbl6   | STDIN    |      2 | [1,10)  [1,12)
+(6 rows)
+
 -- clean up
+DROP TABLE err_tbl0, err_tbl1 CASCADE;
+NOTICE:  drop cascades to view err_tbl4
+DROP DOMAIN d_text;
 DROP TABLE forcetest;
 DROP TABLE vistest;
 DROP FUNCTION truncate_in_subxact();
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index eaad290b257..045cb17666f 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -369,6 +369,17 @@ truncate tab_progress_reporting;
 copy tab_progress_reporting from :'filename'
 	where (salary < 2000);
 
+-- Generate COPY FROM report with PIPE, with some skipped tuples.
+create unique index tab_progress_reporting_idx1 on tab_progress_reporting(name);
+create temp table conflict_tbl(copy_tbl oid, filename text, lineno bigint, line text);
+copy tab_progress_reporting from stdin(on_conflict table, conflict_table 'conflict_tbl');
+sharon	25	(115,12)	1000	sam
+bill	20	(111,10)	1000	sharon
+bill	20	(111,10)	1000	sharon
+\.
+drop index tab_progress_reporting_idx1;
+drop table conflict_tbl;
+
 -- Generate COPY FROM report with PIPE, with some skipped tuples.
 copy tab_progress_reporting from stdin(on_error ignore);
 sharon	x	(15,12)	x	sam
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index e0810109473..c939c8602c2 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -636,7 +636,93 @@ a	{7}	7
 10	{10}	10
 \.
 
+CREATE DOMAIN d_text as TEXT;
+CREATE TABLE t_copy_tbl(a int, b int, c text);
+CREATE TABLE err_tbl0(copy_tbl oid primary key);
+
+CREATE TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text generated always as ('hh') stored);
+ALTER TABLE err_tbl1 ADD CONSTRAINT con1 FOREIGN KEY (copy_tbl) REFERENCES err_tbl0(copy_tbl);
+CREATE TRIGGER trg_x_after AFTER INSERT ON err_tbl1 FOR EACH ROW EXECUTE PROCEDURE fn_x_after();
+CREATE POLICY p1 ON err_tbl1 FOR SELECT USING (true);
+ALTER TABLE err_tbl1 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE err_tbl1 FORCE ROW LEVEL SECURITY;
+
+-- The conflict error saving table must be a plain table and is subject to the
+-- following restrictions: it cannot contain foreign key constraints; it must
+-- not have triggers, rules, or row-level security policies.
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+ALTER TABLE err_tbl1 ALTER COLUMN line DROP EXPRESSION;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+ALTER TABLE err_tbl1 DROP CONSTRAINT con1;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+DROP TRIGGER IF EXISTS trg_x_after ON err_tbl1;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+DROP POLICY IF EXISTS p1 ON err_tbl1;
+ALTER TABLE err_tbl1 DISABLE ROW LEVEL SECURITY;
+
+ALTER TABLE err_tbl1 ALTER COLUMN line SET DATA TYPE d_text;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error
+ALTER TABLE err_tbl1 DROP COLUMN line;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error
+
+ALTER TABLE err_tbl1 ADD COLUMN line text, ADD column extra int;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error
+ALTER TABLE err_tbl1 DROP COLUMN extra;
+
+CREATE VIEW err_tbl4 AS SELECT * FROM err_tbl1;
+COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl4); -- error
+COPY t_copy_tbl(c, b) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose); -- ok
+3,2
+\.
+
+-- COPY ON_CONFLICT TABLE cannot apply to deferred unqiue constraint
+ALTER TABLE t_copy_tbl ADD CONSTRAINT t_copy_tbl_unq1 UNIQUE (a) DEFERRABLE INITIALLY DEFERRED;
+BEGIN;
+COPY t_copy_tbl FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+1,2,3
+\.
+ROLLBACK;
+ALTER TABLE t_copy_tbl DROP CONSTRAINT t_copy_tbl_unq1;
+
+ALTER TABLE err_tbl1 ADD CONSTRAINT cc CHECK (lineno > 0);
+ALTER TABLE err_tbl1 ADD CONSTRAINT nn NOT NULL copy_tbl;
+CREATE UNIQUE INDEX ON t_copy_tbl (b) WHERE a = 1;
+CREATE UNIQUE INDEX ON t_copy_tbl ((b+1));
+CREATE UNIQUE INDEX ON t_copy_tbl (c);
+
+COPY t_copy_tbl(b,a,c) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE,CONFLICT_TABLE err_tbl1, log_verbosity verbose); -- ok
+2,1,aaa
+2,1,XXX
+\.
+
+COPY t_copy_tbl(b,a, c) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose);
+4,17,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+6,11,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+11,1,xxxxxxxx
+12,1,xxxxxxxx
+13,1,xxxxxxxx
+\.
+
+CREATE TABLE err_tbl6 (
+  id1 int4range,
+  valid_at int4range,
+  CONSTRAINT err_tbl6_uq UNIQUE (id1, valid_at WITHOUT OVERLAPS)
+);
+
+COPY err_tbl6 FROM STDIN (ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error
+[11,12)	empty
+\.
+
+COPY err_tbl6 FROM STDIN (ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1);
+[1,10)	[1,2)
+[1,10)	[1,12)
+\.
+
+SELECT copy_tbl::regclass, filename, lineno, line FROM err_tbl1;
+
 -- clean up
+DROP TABLE err_tbl0, err_tbl1 CASCADE;
+DROP DOMAIN d_text;
 DROP TABLE forcetest;
 DROP TABLE vistest;
 DROP FUNCTION truncate_in_subxact();
-- 
2.34.1