Thread

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

jian he <jian.universality@gmail.com> — 2026-05-27T14:06:27Z

On Fri, May 15, 2026 at 7:56 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
>
> I also noticed the early relation close mentioned by Jim, which can
> crash the patch.
>
fixed.

> +      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.
>
> EXCLUDE USING gist (... WITH =, ... WITH &&) seems to work fine?
> Except that the message mentions unique constraint violation.
>

I double-checked ExecCheckIndexConstraints, ExecInsertIndexTuples and
added some dummy regression tests to confirm
that INSERT ON CONFLICT DO NOTHING works fine with exclusion constraints.

> I also checked the same trigger behaviors as in the other thread[1],
> especially before triggers on the conflict table, and this patch
> behaves similarly, it silently drops rows.
> I think this could also use some more visibility/documentation about that.
>
> 1: https://www.postgresql.org/message-id/CAN4CZFPoohFvQTSE0wC%2BwcrfYiZOxFmUdOq0%2B9TCVR6Hk8n6iw%40mail.gmail.com
>
With the attached v4, row-level and statement-level triggers are now
fired for every insertion to conflict_table

In v3, there was a performance regression when a table don't have any unique or
exclusion constraint, but ON_CONFLICT was still specified as 'TABLE'.
I have attached an SQL test script demonstrating this.
With v4, this regression is now very very minimal for COPY operations where
ON_CONFLICT is set to 'TABLE' on a target table without any unique or exclusion
constraints.

I also polished the documentation.

Comments from Jim Jones also addressed.



--
jian
https://www.enterprisedb.com/