Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12

You can create a Constraint trigger to check the new values of the desired
columns when updating or insering them. This trigger will fire for each
partition but checks the data in the entire table. The trigger should
obtain an advisory lock for the unique columns . Here is a blog for more
information.
https://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html

On Tue, Sep 20, 2022, 5:46 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Fri, 16 Sept 2022 at 19:52, Inzamam Shafiq
> <inzamam(dot)shafiq(at)hotmail(dot)com> wrote:
> > Is there anyway we can create constraint on column only which are not
> part of partition key?
>
> Unfortunately, there is no way to create a unique or primary key
> constraint unless the constraint contains all columns from the
> partition key. This is explained in the limitations section in [1].
>
> If you need such a constraint, then you might want to consider
> changing your partition key.
>
> David
>
> [1] https://www.postgresql.org/docs/12/ddl-partitioning.html
>
>
>