| From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
|---|---|
| To: | veem v <veema0000(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: Partitioning and unique key |
| Date: | 2024-09-02 13:43:48 |
| Message-ID: | a2473f06464fde533ed499b53e2d87b79567fd21.camel@cybertec.at |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Sun, 2024-09-01 at 01:32 +0530, veem v wrote:
> due to postgres limitations we are unable to have this unique constraint or primary key
> only on the transaction_id column, we have to include transaction_timestamp with it as
> a composite key. So I want to understand from experts if there is any possible way to
> satisfy both partitioning on transaction_timestamp column and unique key or pk just on
> transaction_id only?
No, you cannot have both.
Usually the solution is to *not* create a primary key on the partitioned table
and instead create a primary key on each partition.
That won't guarantee global uniqueness (and there is no way to do that), but it
goes a long way by ensuring that the column is unique within each partition.
Yours,
Laurenz Albe
| From | Date | Subject | |
|---|---|---|---|
| Next Message | veem v | 2024-09-02 16:09:06 | Re: Partitioning and unique key |
| Previous Message | Shaheed Haque | 2024-09-02 13:27:24 | Re: Postgres Logical Replication - how to see what subscriber is doing with received data? |