Subject: Regarding use of single column as primary key on partitioned table

From: Durgamahesh Manne <[email-masked]>
Date: Sat, 28 Sep 2024 09:55:57 +0530

Hi Team
test=>  CREATE TABLE public.bet (
        betid int4 NOT NULL,
        externalbetid text NULL,
        externalsystem text NULL,
        placedon timestamptz NULL,
        createdon timestamptz NULL
) partition by list (placedon) ;
CREATE TABLE
test=> alter table public.bet add primary key (betid);
ERROR:  unique constraint on partitioned table must include all
partitioning columns
DETAIL:  PRIMARY KEY constraint on table "bet" lacks column "placedon"
which is part of the partition key.
test=>

Can't we use primary key on singal column(betid) on partitioned table
rather than using composite key (placedon,betid)?

Regards,
Durga Mahesh

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 27 Sep 2024 21:39:21 -0700

> rather than using composite key (placedon,betid)?

No.  It would be misleading to allow such a thing because a unique index
can only span a single partition.

David J.