ERROR: insufficient columns in PRIMARY KEY constraint definition

Question

I recently upgraded the database from PostgreSQL v9.6 to v11.7. we have some partitioned table with inherence and planning to migrate them to the declaration.

Table DDL:

CREATE TABLE c_account_p
(
    billing_account_guid character varying(40)  NOT NULL,
    ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
    load_dttm timestamp(6) without time zone NOT NULL,
    ban integer NOT NULL,
    CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
) PARTITION by RANGE(load_dttm);

When I try the create table, it's throwing below error:

ERROR:  insufficient columns in the PRIMARY KEY constraint definition
DETAIL:  PRIMARY KEY constraint on table "l_billing_account_p" lacks column "load_dttm" which is part of the partition key.
SQL state: 0A000

Is it mandatory/necessary that the partition column should be a primary key? cause if I include load_dttm as PK then its working fine.db<>fiddle

If the partition column should be supposed to be a PK, it's challenging to create a partition by range with the date column, cause the load_dttm column chances to have duplicate if data loaded COPY or INSERT.

INSERT INTO c_account_p SELECT * from c_account_p_bkp ON CONFLICT (billing_account_guid,ban,load_dttm) DO UPDATE SET 'some stuff..'

If I receive billing_account_guid, ban combination with different load_dttm then it will end up with duplicate keys.

Could some please help me to understand this scenario?

Thanks.

score 7 · Answer 1 · edited Oct 05 '20 at 19:28

Yes, this is required. There is no concept of an index over all partitions in PostgreSQL. The requirement of having the partition key a subset of the primary key allows us to implement primary keys by just having individual unique indexes on each partition.

There's mention in section 5.10.2.3. "Unique constraints on partitioned tables must include all the partition key columns. This limitation exists because PostgreSQL can only enforce uniqueness in each partition individually.

CREATE TABLE c_account_p
    (
        billing_account_guid character varying(40)  NOT NULL,
        ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
        load_dttm timestamp(6) without time zone NOT NULL,
        ban integer NOT NULL,
        CONSTRAINT billing_account_pkey  PRIMARY KEY (billing_account_guid,ban,load_dttm)
    ) PARTITION by RANGE(load_dttm);

ERROR: insufficient columns in PRIMARY KEY constraint definition

1 Answers1