In PostgreSQL, why does ALTER TABLE clicks ADD PRIMARY KEY (click_id) fail on a table partitioned by RANGE (clicked_at), and what are your legal options?
answer
- one B-tree per partition
- no index spans partitions
- partition key inside every unique key
- expression keys forbid unique keys
- SQLSTATE 0A000
basics
~20 sPostgreSQL has no global indexes: a unique key on a partitioned table is one unique index per partition, so it must include every partition key column. Use PRIMARY KEY (click_id, clicked_at), or partition by the column that must be unique.
solid answer
~40 sIt fails with `unique constraint on partitioned table must include all partitioning columns` (SQLSTATE `0A000`) and a DETAIL naming `clicked_at`. An index declared on a partitioned table is a **partitioned index**: one physical B-tree per partition, each seeing only its own rows, and PostgreSQL has no index that spans partitions. Only if the key contains the partition key do equal keys always land in the same partition, so the per-partition checks add up to a table-wide guarantee. Options: `PRIMARY KEY (click_id, clicked_at)` with a generated `click_id`; `PARTITION BY HASH (click_id)` if `click_id` must be unique on its own, giving up monthly partitions; or a per-partition unique index, which guarantees nothing across months. The partition key must be plain columns: with an expression key, no unique or primary key is possible at all.
code
sql · 18 linesCREATE TABLE clicks (
click_id bigint NOT NULL,
clicked_at timestamptz NOT NULL,
campaign_id bigint NOT NULL,
ad_id bigint NOT NULL
) PARTITION BY RANGE (clicked_at);
ALTER TABLE clicks ADD PRIMARY KEY (click_id);
-- ERROR: unique constraint on partitioned table must include all partitioning columns
ALTER TABLE clicks ADD PRIMARY KEY (click_id, clicked_at);
CREATE TABLE conversions (
conversion_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
click_id bigint NOT NULL,
clicked_at timestamptz NOT NULL,
FOREIGN KEY (click_id, clicked_at) REFERENCES clicks (click_id, clicked_at)
);go deeper
Remember the rule itself: a primary or unique key on a PostgreSQL partitioned table must include every partition key column, and the partition key must be plain columns.
Explain why: a partitioned index is one B-tree per partition and PostgreSQL has no global index, so only the partition key can rule out cross-partition duplicates. Name the error and its SQLSTATE.
Walk through the consequences you will meet in production: composite foreign keys in referencing tables, upsert arbiters containing the partition key, and detach failures when referenced rows remain.
Weigh the partitioning scheme against the keys the business must enforce: monthly retention versus a table-wide unique identifier, and whether the generator can be trusted where the database cannot check.
## What PostgreSQL reports On a clickstream table declared `PARTITION BY RANGE (clicked_at)`, the statement `ALTER TABLE clicks ADD PRIMARY KEY (click_id)` is refused with: - `ERROR: unique constraint on partitioned table must include all partitioning columns` - `DETAIL: PRIMARY KEY constraint on table "clicks" lacks column "clicked_at" which is part of the partition key.` The SQLSTATE is `0A000` (`feature_not_supported`). Nothing is wrong with the data; the key has the wrong shape. The same check runs for `UNIQUE` constraints and for `CREATE UNIQUE INDEX` on the parent. ## Why the rule exists - A **partitioned table** has no storage of its own. Its rows live in **partitions**, which are ordinary tables (here, one per month). - An index declared on the parent is a **partitioned index**: a catalog entry that owns one physical index per partition and is cloned onto every partition created or attached later. - PostgreSQL has **no global index** spanning partitions. Each per-partition B-tree can check uniqueness only among its own partition's rows. - When the key contains every partition key column, two rows with equal keys must route to the same partition, so they meet in the same B-tree. That is the only way per-partition indexes add up to a table-wide guarantee. So `(click_id, clicked_at)` is accepted and `(click_id)` is not. The local-versus-global index argument is shared by many databases; the PostgreSQL-specific fact is that there is no global index to fall back on. ## The stricter rule for expression keys The partition key must consist of **plain columns** for any unique or primary key to exist. If a table were declared `PARTITION BY LIST (lower(country_code))`, every unique or primary key on it would fail with `unsupported PRIMARY KEY constraint with partition key definition`, because these constraints cannot be used when the partition key includes an expression, whichever columns the key lists. Partition on a real column (here the `timestamptz` column `clicked_at`) rather than on an expression of it if you will ever need a key. ## Your options | Option | What the database enforces | What it costs | |---|---|---| | `PRIMARY KEY (click_id, clicked_at)` | the pair is unique | `click_id` alone could repeat in two months; referencing tables must store `clicked_at` | | `PARTITION BY HASH (click_id)` instead | `click_id` table-wide | no monthly partitions, so no month-at-a-time retention or loading | | a unique index on each partition only | `click_id` within one month | no cross-month guarantee, and each new partition needs its own | | composite key plus a generator that never reuses values | the pair; the identity, sequence or UUID default covers the rest | relies on the generator, not on a constraint | For an ad-analytics clickstream the usual choice is the composite key with a generated `click_id`, because monthly partitions are what make retention and bulk loading cheap. ## Knock-on effects to mention 1. **Foreign keys into `clicks`.** A referencing table such as `conversions` needs a unique key to point at, so it must store both columns: `FOREIGN KEY (click_id, clicked_at) REFERENCES clicks`. 2. **Removing a referenced partition.** Detaching a `clicks` partition that `conversions` rows still point at fails with `removing partition ... violates foreign key constraint ...` (SQLSTATE `23503`). 3. **Upserts.** `INSERT ... ON CONFLICT` into `clicks` needs a unique index as its arbiter, and every legal one contains `clicked_at` (arbiter rules belong to the upsert topic). 4. **Exclusion constraints** follow the same rule: they must include every partition key column and compare those columns with equality, not with an operator such as `&&`. 5. **pg_partman's template table** can put a unique index on a non-key column of each child, but it is still enforced per child; the extension ships a `check_unique_constraint.py` script to find duplicates across the set. ## How the parent's unique index is built Adding the key on the parent builds one unique index per partition in a single command, and `CREATE INDEX CONCURRENTLY` is not available on a partitioned table. The documented alternative is to create the parent's constraint with `ALTER TABLE ONLY clicks ADD ...` (which leaves the parent index invalid), add a matching constraint on each partition, and link each partition's index with `ALTER INDEX ... ATTACH PARTITION`; the parent index becomes valid once every partition has one. Running that safely on a large live system is covered with partitioned indexes in general.
- With PRIMARY KEY (click_id, clicked_at) in place, how does a lookup by click_id alone perform?A plain index on `click_id` (or the primary key's leading column) is still one B-tree per partition, and a predicate without `clicked_at` cannot tell PostgreSQL which month to read, so the query probes every monthly index. Passing `clicked_at`, or a narrow range of it, alongside `click_id` lets the planner skip the other months.
- What stops you from detaching an old clicks partition while conversions still references it?PostgreSQL checks referencing rows when a partition of a referenced table is removed: if any `conversions` row still points into that month, the detach fails with `removing partition ... violates foreign key constraint ...` (SQLSTATE `23503`). Delete or archive those conversions first. pg_partman 5.5 added `detach_before_drop` so its retention detaches such children before dropping them.
- PostgreSQL builds a global unique index across partitions when you declare a primary key on the parent.
- A unique index on each partition guarantees click_id is unique across the whole table.
- Partitioning by an expression still allows a primary key as long as it lists the expression's column.
- CREATE UNIQUE INDEX CONCURRENTLY on the parent gets around the partition-key rule.
- A foreign key can reference click_id alone once the composite primary key exists.