Re: Partitioning and unique key

On Saturday, August 31, 2024, veem v <veema0000(at)gmail(dot)com> wrote:

>
> 1) if it's technically possible to have a unique key on only the
> transaction_id column having the partition key on the
> transaction_timestamp, because the table is going to be queried/purged
> based on the transaction_timestamp?
>

There is presently no such thing as a cross-partition unique constraint.
If you define the constraint on the [partitioned] table the documentation
is perfectly clear, as are I believe the error messages, that it will
require all partitioning columns to be included - since that is what
happens in reality. If you target the partitions directly with the unique
index or constraint no such limitation should exist.

>
> iii)And then alter the datatype of the partition key transaction_date to
> DATE in one shot at the table level(which should be fast as its having more
> granularity as compare to existing timestamptype, so should be catalog or
> dictionary change only), and that will remain the part of composite PK
> (transaction_id,transaction_date).
>

While this might seem logical, in reality date and timestamptz are
different fixed-width data types and thus any attempt to change from one to
the other will involve a table rewrite. Best you could do is leave the
timestamptz in place and just truncate to day so the time is always
midnight UTC.

> iv) Repeat this step for all child partition tables and then for the
> parent partition tables.
>

I’d suggest trying to just build a new partitioned table that is correctly
defined. Then populate it. Add a trigger to the existing one to keep the
new one in sync. Then change your application code to point to the new
partitioned table. At which point the old partitioned table can be dropped.

David J.