Attach a prepared table as a partition with a short lock (ATTACH PARTITION)

Command

-- 1) Prepare the data in a separate table without touching the live table
CREATE TABLE sales_2027_01 (LIKE sales INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-- ... bulk load (COPY / INSERT) ...
ALTER TABLE sales_2027_01 ADD PRIMARY KEY (id, created_at);
CREATE INDEX CONCURRENTLY sales_2027_01_customer_id_idx ON sales_2027_01 (customer_id);

-- 2) A CHECK that exactly matches the bounds: avoids a full scan during ATTACH
ALTER TABLE sales_2027_01 ADD CONSTRAINT sales_2027_01_bound_chk
  CHECK (created_at IS NOT NULL AND created_at >= DATE '2027-01-01' AND created_at < DATE '2027-02-01') NOT VALID;
ALTER TABLE sales_2027_01 VALIDATE CONSTRAINT sales_2027_01_bound_chk;

-- 3) Attach (SHARE UPDATE EXCLUSIVE on the parent; reads/writes continue)
SET lock_timeout = '5s';
ALTER TABLE sales ATTACH PARTITION sales_2027_01
  FOR VALUES FROM ('2027-01-01') TO ('2027-02-01');
RESET lock_timeout;

-- 4) Drop the now redundant CHECK
ALTER TABLE sales_2027_01 DROP CONSTRAINT sales_2027_01_bound_chk;

What it does

Turns data loaded into a separate table into a partition, without a full scan or long locks, by using a pre-validated CHECK constraint that matches the bounds and pre-built indexes.

When to use it

For bulk loads (ETL) or restores from archive: prepare the data without touching the live table, then bring it online in one step.

Caution

ATTACH takes SHARE UPDATE EXCLUSIVE on the parent and ACCESS EXCLUSIVE on the table being attached. Without a matching valid CHECK, the table is scanned in full under that lock to validate the bounds; if indexes matching the parent's are missing, ATTACH builds them under the lock, so prepare both in advance. If a default partition exists, it is also scanned to prove it holds no rows in the new range (see the default partition recipe). Column names and types must match the parent exactly; creating the table with LIKE ... INCLUDING is the safest way.