Postgresql partition table unique index problem

Question

postgres 14

I have some table:

CREATE TABLE sometable (
    id integer NOT NULL PRIMARY KEY UNIQUE ,
    a integer NOT NULL  DEFAULT 1,
    b varchar(32) UNIQUE)
PARTITION BY RANGE (id);

But when i try to execute it, i get

ERROR: unique constraint on partitioned table must include all partitioning columns

If i execute same table definition without PARTITION BY RANGE (id) and check indexes, i get:

 tablename    indexname                                   indexdef

 sometable, sometable_b_key, CREATE UNIQUE INDEX sometable_b_key ON public.sometable USING btree (b)
 sometable, sometable_pkey, CREATE UNIQUE INDEX sometable_pkey ON public.sometable USING btree (id)

So... unique constraints exist

whats the problem? how can i fix it?

Answer 1

On partitioned tables, all primary keys, unique constraints and unique indexes must contain the partition expression. That is because indexes on partitioned tables are implemented by individual indexes on each partition, and there is no way to enforce uniqueness across different indexes.

If you want to use partitioning, you have to sacrifice some consistency guarantees. There is no way around that. What you can do is create unique constraints on the partitions. That will guarantee uniqueness within each partition, but not global uniqueness.