a composite primary key on 3 columns out of which two columns are partition key columns. Now I have a requirement to add a 4th column that needs to be unique. I cannot create a unique constraint or unique index on just the 4th column due to Postgres limitation to include partition keys as part of unique constraint or index. Any ideas on how to guarantee uniqueness on just the 4th column ? Also how is it different creating a unique constraint vs unique index on multi columns in postgres ? I know unique constraint will create an index automatically but wanted to know whats the preferred way.
Don't partition that table. Partitioning even slows down queries, when the partition key isn't part of a query on a partitioned table. And there is no solution to add a unique constraint or unique index to a partitioned table. And multiple uniqueness is not possible on partitioned tables. Rethink the structure of your data.
Обсуждают сегодня