public inbox for [email protected]
help / color / mirror / Atom feedFrom: Geri Wright <[email protected]>
To: David Rowley <[email protected]>
Cc: Inzamam Shafiq <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Steve Midgley <[email protected]>
Cc: [email protected]
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
Date: Thu, 22 Sep 2022 06:39:27 -0400
Message-ID: <CAKSgRY7WQtA2wyFkX-rE6oLMLm=bfr9EphQy2_H6x0Affz528A@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvoVCXU8_S46VBX6j+ff6n-dHLb6jNnfUF094db9ccBjKg@mail.gmail.com>
References: <AM9P251MB033090199F1A3C2B1742A50A98499@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
<CAJexoS+F37aQStLD4chj51EhtLyUWFNTtC4Vduq4XqR_bFwYCQ@mail.gmail.com>
<AM9P251MB0330200D3E9C4875862B10E898489@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
<[email protected]>
<AM9P251MB03309E0FC9FFEC4552F81ED298489@AM9P251MB0330.EURP251.PROD.OUTLOOK.COM>
<CAApHDvoVCXU8_S46VBX6j+ff6n-dHLb6jNnfUF094db9ccBjKg@mail.gmail.com>
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 <[email protected]> wrote:
> On Fri, 16 Sept 2022 at 19:52, Inzamam Shafiq
> <[email protected]> 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
>
>
>
view thread (8+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: ON CONFLICT clause NOT working on Partition Table PostgreSQL 12
In-Reply-To: <CAKSgRY7WQtA2wyFkX-rE6oLMLm=bfr9EphQy2_H6x0Affz528A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox