Question 317211 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres adding unique index and then PK to partitioned table that already has PK on partition

when adding a unique index and then a PK to a partitioned table that already has that PK on a table partition, the PK creation fails with multiple primary keys for table X are not allowed

Why this limitation and the strange error message? Shouldn’t that just work?

CREATE TABLE tst_t (
    c1 int4 NOT NULL,
    c2 int8 NOT NULL,
    c3 int8 NOT NULL
)
PARTITION BY LIST (c1);

CREATE TABLE tst_t_988 PARTITION OF tst_t (
    CONSTRAINT pk_tst_t_988 PRIMARY KEY (c1)
) FOR VALUES IN (988);

create unique index pk_tst_t on tst_t using btree (c1);

alter table tst_t add primary key (c1);

-- SQL Error [42P16]: ERROR: multiple primary keys for table "tst_t_988" are not allowed

drop table tst_t;

CREATE TABLE tst_t (
    c1 int4 NOT NULL,
    c2 int8 NOT NULL,
    c3 int8 NOT NULL
)
PARTITION BY LIST (c1);

CREATE TABLE tst_t_988 PARTITION OF tst_t (
    CONSTRAINT pk_tst_t_988 PRIMARY KEY (c1)
) FOR VALUES IN (988);

-- this time not creating the index
--create unique index pk_tst_t on tst_t using btree (c1);

alter table tst_t add primary key (c1);

-- works
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The weird error message seems to be an area of improvement for postgres. A more clear error message could be displayed instead.

    But in general the feature of creating a primary key using an existing index is just not there yet in PG for partitioned tables.

    See https://www.postgresql.org/docs/14/sql-altertable.html, chapters

    https://www.postgresql.org/docs/14/sql-altertable.html and

    https://www.postgresql.org/docs/16/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT-USING-INDEX.

    They clearly state that such operations are not yet possible for partitioned tables.


  2. 0

    If you already have a primary key on the partition, you can add a primary key on the partitioned table by first adding an invalid primary key on only the partitioned table, then attaching the primary key index on the partitions as partitions to that index. Once the index has a partition on all table partitions, the primary key becomes valid automatically:

    CREATE TABLE tst_t (
    c1 int4 NOT NULL,
    c2 int8 NOT NULL,
    c3 int8 NOT NULL
)
PARTITION BY LIST (c1);

CREATE TABLE tst_t_988 PARTITION OF tst_t (
    CONSTRAINT pk_tst_t_988 PRIMARY KEY (c1)
) FOR VALUES IN (988);

ALTER TABLE ONLY tst_t ADD CONSTRAINT tst_t_pkey PRIMARY KEY (c1);

ALTER INDEX tst_t_pkey ATTACH PARTITION pk_tst_t_988;

d tst_t
         Partitioned table "laurenz.tst_t"
 Column │  Type   │ Collation │ Nullable │ Default 
════════╪═════════╪═══════════╪══════════╪═════════
 c1     │ integer │           │ not null │ 
 c2     │ bigint  │           │ not null │ 
 c3     │ bigint  │           │ not null │ 
Partition key: LIST (c1)
Indexes:
    "tst_t_pkey" PRIMARY KEY, btree (c1)
Number of partitions: 1 (Use d+ to list them.)

d+ tst_t_pkey
            Partitioned index "laurenz.tst_t_pkey"
 Column │  Type   │ Key? │ Definition │ Storage │ Stats target 
════════╪═════════╪══════╪════════════╪═════════╪══════════════
 c1     │ integer │ yes  │ c1         │ plain   │ 
primary key, btree, for table "laurenz.tst_t"
Partitions: pk_tst_t_988
Access method: btree
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search