8

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.

4
  • It says the partition key columns must be columns of a PK. A column of a PK doesn't have to be unique unless it is the only column. Why do you think otherwise?
    – philipxy
    Sep 29, 2020 at 22:38
  • Unrelated, but: if billing_account_guid is a "UUID", then the column should be declared as uuid. Unfortunately Postgres' partitioning implementation has this restriction that the primary key has to be part of the partitioning key. There is no way you can work around that.
    – user1822
    Sep 30, 2020 at 6:07
  • we have some partitioned table with inherence - So you have exactly the same limitation. You didn't have a primary key for all inheritance partitions (globally unique), you still don't have one primary key for all declarative partitions. How have you dealt with this previously?
    – Melkij
    Jun 25, 2021 at 7:43
  • If you want to work around this limitation, one way would be to add a UNIQUE constraint on each partition (instead of the partitioned table). Jun 25, 2021 at 10:43

1 Answer 1

7

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);
4
  • Yes, I can add load_dttm to PK, but partitioning is to use a date field, which is totally unrelated to your PK. how to handle if we get a duplicate combination?
    – Rj_N
    Sep 29, 2020 at 22:10
  • you have an unique guid and you combine it with a number(ban), so that you can have multiple billing_account_guid ban is an integer which is quite small so you don't exspect not that many rows. further partioning that is not indexed takes quite long to search or select. Maybe you should reconsider your design or live with it and check the uniqueness without waiting for an error
    – nbk
    Sep 29, 2020 at 22:47
  • How to handle INSERT INTO c_account_p SELECT * from c_account_p_bkp ON CONFLICT (billing_account_guid,ban) 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.
    – Rj_N
    Jun 25, 2021 at 2:58
  • if you want to be unique so make a UNIQUE constraint with these two, and you will trigger the On update, but please make a new question.
    – nbk
    Jun 25, 2021 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.