Re: BUG #16714: INSERT ON CONFLICT DO UPDATE fails to infer constraint if it's not at top-level partition - Mailing list pgsql-bugs

Please don't top-post.  The convention here is to inline or bottom-post.

I agree that the overview statement quoted should be qualified.  The existing behavior is that the table listed in the insert is the table upon which the ON CONFLICT arbiter constraint must exist [1].  This effectively negates the benefit of leveraging the partitioning scheme as a form of uniqueness enforcement in lieu of indexed columns.  IIUC, the additional partition constraint is still useful for select queries so its creation still has merit and leverages that particular benefit of partitioning.  But today, you cannot fully benefit from that dynamic if you wish to leverage ON CONFLICT during data insertion.  An improvement there isn't something that could be back-patched.

David J.

[1] From the INSERT documentation: "All table_name unique indexes that, without regard to order, contain exactly the conflict_target-specified columns/expressions are inferred (chosen) as arbiter indexes."