Unique Index on partition table isse

How we can eliminate duplicate insertion after postgres partitions.

As Partition key on unique constraint causing non key attribute duplicate.

EX: ID date

 1 1-01-2022

 1 02-01-2022

To make ID unique we have before insert trigger is the only option, Any other ways?

Solution 1:

You cannot have a unique constraint on a partitioned table that does not contain the partitioning key. A trigger won't be a reliable solution either, because it would be subject to race conditions (concurrent inserts) unless you are running with the SERIALIZABLE isolation level.

The best you can have is unique constraints on each individual partitions.

The best you can do is fill the values from a sequence, so that the values are automatically unique, for example with an identity column.