Message-ID: <a2473f06464fde533ed499b53e2d87b79567fd21.camel@cybertec.at>
Subject: Re: Partitioning and unique key
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: veem v <veema0000@gmail.com>, pgsql-general
	 <pgsql-general@lists.postgresql.org>
Date: Mon, 02 Sep 2024 15:43:48 +0200
In-Reply-To: <CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
References: 
	<CAB+=1TViAodXqYbgB+xiXL=6AShPacH5G9PB2hbcd+gHR_Nb9w@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/a2473f06464fde533ed499b53e2d87b79567fd21.camel%40cybertec.at>
Precedence: bulk

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_timesta=
mp with it as
> a composite key. So I want to understand from experts if there is any pos=
sible way to
> satisfy both partitioning on transaction_timestamp column and unique key =
or pk just on
> transaction_id only?=C2=A0

No, you cannot have both.

Usually the solution is to *not* create a primary key on the partitioned ta=
ble
and instead create a primary key on each partition.

That won't guarantee global uniqueness (and there is no way to do that), bu=
t it
goes a long way by ensuring that the column is unique within each partition=
.

Yours,
Laurenz Albe