How to fix cannot turn regular table into partitioned table in PostgreSQL

CREATE TABLE events_partitioned (
    id SERIAL,
    event_name VARCHAR(255),
    created_at TIMESTAMP NOT NULL,
    -- other columns...
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024 PARTITION OF events_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE events_2025 PARTITION OF events_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Rename the original table
ALTER TABLE events RENAME TO events_old;

-- Create the partitioned parent table
CREATE TABLE events (
    id SERIAL,
    event_name VARCHAR(255),
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Add a CHECK constraint to the old table matching partition bounds
ALTER TABLE events_old
    ADD CONSTRAINT events_old_check
    CHECK (created_at >= '2024-01-01' AND created_at < '2025-01-01');

-- Attach the old table as a partition
ALTER TABLE events ATTACH PARTITION events_old
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Create additional partitions for other date ranges
CREATE TABLE events_2025 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Rename original table
ALTER TABLE events RENAME TO events_old;

-- Create partitioned table (from step 1)
CREATE TABLE events (
    id SERIAL,
    event_name VARCHAR(255),
    created_at TIMESTAMP NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE events_2025 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Copy data in batches to avoid locks
INSERT INTO events
SELECT * FROM events_old
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

INSERT INTO events
SELECT * FROM events_old
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

-- Verify data integrity
SELECT COUNT(*) FROM events;
SELECT COUNT(*) FROM events_old;

-- After verification, drop old table
DROP TABLE events_old;

-- Create partitioned table alongside existing table
CREATE TABLE events_new (
    id SERIAL,
    event_name VARCHAR(255),
    created_at TIMESTAMP NOT NULL,
    migrated BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024 PARTITION OF events_new
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Create trigger to redirect new writes
CREATE OR REPLACE FUNCTION redirect_to_partitioned()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO events_new VALUES (NEW.*);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER redirect_inserts
    AFTER INSERT ON events
    FOR EACH ROW
    EXECUTE FUNCTION redirect_to_partitioned();

-- Migrate existing data in batches
DO $$
DECLARE
    batch_size INT := 10000;
    last_id INT := 0;
BEGIN
    LOOP
        INSERT INTO events_new
        SELECT * FROM events
        WHERE id > last_id
        ORDER BY id
        LIMIT batch_size;

        IF NOT FOUND THEN EXIT; END IF;

        SELECT MAX(id) INTO last_id FROM events_new;
        COMMIT;
    END LOOP;
END $$;

-- After migration: swap tables
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;

-- Check partition structure
SELECT
    parent.relname AS parent_table,
    child.relname AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bounds
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'events'
ORDER BY partition_name;

-- Verify data distribution
SELECT
    tableoid::regclass AS partition_name,
    COUNT(*) AS row_count
FROM events
GROUP BY tableoid
ORDER BY partition_name;

-- Test partition pruning (should only scan relevant partition)
EXPLAIN SELECT * FROM events
WHERE created_at >= '2024-06-01'
  AND created_at < '2024-07-01';

-- Create indexes on partitions (automatically inherited)
CREATE INDEX idx_events_created_at ON events (created_at);
CREATE INDEX idx_events_event_name ON events (event_name);

-- Update sequences if using SERIAL
SELECT setval('events_id_seq',
    (SELECT MAX(id) FROM events)
);

-- Grant appropriate permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON events TO app_user;

-- Good: Uses partition key
SELECT * FROM events
WHERE created_at >= '2024-01-01' AND event_name = 'login';

-- Less optimal: Missing partition key
SELECT * FROM events WHERE event_name = 'login';