Moving data from the default partition into a new partition table

When a default partition table is defined, new partitions cannot be created or attached if there exists data in the default partition (available since PostgreSQL 11) that would be in the new partition. Attempting to create such a partition will result in an error like:

ERROR: updated partition constraint for default partition "child" would be violated by some row

Here are some steps to handle that situation.

To illustrate the issue, create a partitioned table with a default partition:

CREATE TABLE parent(id BIGINT PRIMARY KEY) PARTITION BY RANGE (id);
CREATE TABLE child PARTITION OF parent DEFAULT;

Populate the table with some data and show what happens when a new partition is created for some of the data that already exists in the default partition:

INSERT INTO parent SELECT generate_series(0, 12);
CREATE TABLE child1 PARTITION OF parent FOR VALUES FROM (1) TO (10);

This is the error that PostgreSQL throws:

ERROR: updated partition constraint for default partition "child" would be violated by some row

The problem can be fixed by starting a new transaction to:

1. Prevent new data from being inserted or changed in the default partition
2. Create a new table to be attached later in the transaction.
3. Move the relevant data from the default partition into the new table.
4. Attach the new table as a partition.

BEGIN;

LOCK child IN ACCESS EXCLUSIVE MODE;

CREATE TABLE child1 (LIKE parent INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

WITH moving AS (
DELETE FROM child WHERE id >=1 AND id < 11 RETURNING *
)
INSERT INTO child1
SELECT * FROM moving;

ALTER TABLE parent ATTACH PARTITION child1 FOR VALUES FROM (1) TO (11);

COMMIT;