Use any column of a partitioned table as the primary key - PolarDB

In PostgreSQL, the primary key of a partitioned table must include the partition keys and cannot be referenced by a foreign key. PolarDB for PostgreSQL removes this limit. You can use any column of a partitioned table as the primary key and reference it with a foreign key.

Applicability

This feature is supported on PolarDB for PostgreSQL clusters that run PostgreSQL 14 with minor engine version 2.0.14.10.17.0 or later.

Note

You can view the minor engine version in the console or run the SHOW polardb_version; statement. If your cluster's minor engine version does not meet the requirement, upgrade the minor engine version.

Parameters

polar_pk_in_non_partition_column_mode: Specifies the index type used for a primary key that is on a non-partition column. Valid values:

none/local_pk: A primary key cannot be created on a non-partition column. If you attempt to do so, an error is reported.
global_index: When you create a primary key on a non-partition column, a global index is used as the constraint.

Limits

If a primary key includes all partition keys, a local index is used as the primary key by default. Otherwise, a global index is used as the primary key.
If a UNIQUE constraint includes all partition keys, a local index is used as the unique constraint by default. Otherwise, a global index is used as the unique constraint.
You must use a global index when you add a primary key to an existing partitioned table because a local index may not satisfy the constraint. The same rule applies when you add a UNIQUE constraint.
You cannot update a row across partitions if a foreign key references that row. A cross-partition update is equivalent to a delete operation followed by an insert operation. When the system deletes the row, it performs a foreign key check.

Examples

Set polar_pk_in_non_partition_column_mode to none and create a primary key on a non-partition column.

SET polar_pk_in_non_partition_column_mode = none;
CREATE TABLE pt1 (a int, b int primary key, c varchar) PARTITION BY RANGE(a);
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "pt1" lacks column "a" which is part of the partition key.

Set polar_pk_in_non_partition_column_mode to global_index and create a primary key on a non-partition column.

SET polar_pk_in_non_partition_column_mode = global_index;
CREATE TABLE pt1 (a int, b int primary key, c varchar) PARTITION BY RANGE(a);
CREATE TABLE pt1_p1 PARTITION OF pt1 FOR VALUES FROM (0) TO (1);
\d pt1
               Partitioned table "public.pt1"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           |          |
 b      | integer           |           | not null |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt1_pkey" PRIMARY KEY, btree (b) GLOBAL
Number of partitions: 1 (Use \d+ to list them.)

If the primary key of a partitioned table includes all partition keys, a local index is used as the primary key regardless of the polar_pk_in_non_partition_column_mode value. This is the default behavior of PostgreSQL.

CREATE TABLE pt2 (a int primary key, b int, c varchar) PARTITION BY RANGE(a);
CREATE TABLE pt2_p1 PARTITION OF pt2 FOR VALUES FROM (0) TO (1);
\d pt2
               Partitioned table "public.pt2"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 b      | integer           |           |          |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt2_pkey" PRIMARY KEY, btree (a)
Number of partitions: 1 (Use \d+ to list them.)

If you must use a global index as the primary key, use the syntax to modify the primary key of the table.

ALTER TABLE pt2 DROP CONSTRAINT pt2_pkey;
CREATE UNIQUE INDEX pt2_pkey ON pt2 (a) GLOBAL;
ALTER TABLE pt2 ADD PRIMARY KEY USING INDEX pt2_pkey;
\d pt2
               Partitioned table "public.pt2"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 b      | integer           |           |          |
 c      | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "pt2_pkey" PRIMARY KEY, btree (a) GLOBAL
Number of partitions: 1 (Use \d+ to list them.)