Re: Proposal: Add a UNIQUE NOT ENFORCED constraint

On Wed, Jan 7, 2026 at 2:12 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
> Some other SQL implementations have optional implementation-specific
> modes on top of NOT ENFORCED that mean, the constraint is not enforced,
> but you can assume it holds for query planning purposes. This is
> probably what the thinking here was, but that is not how PostgreSQL
> currently works, so additional work would need to be done to add this
> additional mode.
>
> There are probably also multiple levels to this. It's one thing to use
> an unenforced-but-probably-true constraint for things like selectivity
> estimation, where you just get a worse plan if it's not quite true, but
> it's another to assume something is unique when it might not be. So
> there is some research to be done here.

Yeah, after reading through Matthias's and your comments, I am
increasingly convinced that the best option is to add a separate
trusted/untrusted flag that would decide whether Postgres allows any
query plans that could change results if the data isn't actually
unique. It seems feasible, but I haven't worked on implementing it
yet.