Re: COPY ON_CONFLICT TABLE; save duplicated record to another table.

Hi Jian

On 11/05/2026 05:13, jian he wrote:
> The attached patch should address most, if not all, of the issues you
> both raised.

Thanks for the update. All my points were addressed.

> As explained in [1], we can export ExecInsert to let it perform the
> main insertion work.
> To allow ExecInsert to handle the remaining tasks, we need to carefuly manage
> the lifecycle of constructed CopyFromStateData->ModifyTableContext (including
> ModifyTableContext->EState): populate it, use it, and then release it.
> 
> Since ExecInsert already contains the necessary infrastructure for INSERT ON
> CONFLICT DO NOTHING/SELECT, exporting it avoids duplicating that logic in
> src/backend/commands/copyfrom.c (which is what v1 of the patch did).
> 
> [1]: https://postgr.es/m/CACJufxH_NbPuA+O5YR7xP4xDZ+iHkO2VFkddhrhBz+4-
> EUTp7w@mail.gmail.com
> 
> The exclusion unique constraint issue is still not resolved.... but,
> overall v2 is better than v1, IMHO.


One other thing I just noticed in BINARY mode. I believe we're missing a
check in ProcessCopyOptions() with ON_CONFLICT TABLE to show a proper
error message, e.g.

/* Check on_conflict */
if (opts_out->format == COPY_FORMAT_BINARY && opts_out->on_conflict !=
ONCONFLICT_NONE)
  ereport(ERROR,
    (errcode(ERRCODE_SYNTAX_ERROR),
     errmsg("cannot specify %s in BINARY mode", "ON_CONFLICT TABLE")));

postgres=# COPY t FROM STDIN (FORMAT binary, ON_CONFLICT TABLE,
CONFLICT_TABLE ctbl);
ERROR:  cannot specify ON_CONFLICT TABLE in BINARY mode

Right now the error is rather vague:

ERROR:  COPY file signature not recognized

What do you think?

Thanks!

Best, Jim