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.
