Revision history [back]

One way to add a primary key to a partitioned table in PostgreSQL 12 without causing extended periods of locking is to use the following steps:

1. Create a new table with the desired primary key columns, as well as any other necessary columns and constraints (e.g. foreign keys).
2. Use the ALTER TABLE ... ATTACH PARTITION command to attach each partition of the original table to the new table.
3. Drop the original table.

During this process, the original table will still be accessible for reads and writes, but any writes to it will be redirected to the new table. This means that there will be some temporary overhead during the process, but it should not result in extended periods of locking.

Here is an example of the steps using standard SQL syntax:

-- Step 1: Create new table with primary key

CREATE TABLE my_table_new (
  pk_column INT PRIMARY KEY,
  other_column VARCHAR,
  FOREIGN KEY (other_column) REFERENCES some_other_table (column)
);

-- Step 2: Attach partitions to new table

ALTER TABLE my_table_part1 ATTACH PARTITION my_table_new PARTITION my_table_part1;
ALTER TABLE my_table_part2 ATTACH PARTITION my_table_new PARTITION my_table_part2;
-- Repeat for as many partitions as necessary

-- Step 3: Drop old table

DROP TABLE my_table;

Note that this approach assumes that the original table is already partitioned and that the partitions are defined as individual tables. If the original table is not partitioned, a different approach may be necessary.