PlanMissingNumberReassignment.sql

/*
Purpose:
- Produce a reviewable mapping from surplus high-numbered rows to gaps in a numeric key.

Safety:
- Read-only. Updating primary or externally referenced keys is inherently risky; this script
  deliberately returns a plan and does not execute an UPDATE.

Customization:
- Replace app.sample_records, record_id, and @expected_max semantics with your own source.
*/

WITH settings AS
(
    SELECT 100000::bigint AS expected_max
),
missing AS
(
    SELECT
        value AS target_record_id,
        ROW_NUMBER() OVER (ORDER BY value) AS position
    FROM settings
    CROSS JOIN LATERAL generate_series(1, settings.expected_max) AS series(value)
    LEFT JOIN app.sample_records AS existing
        ON existing.record_id = series.value
    WHERE existing.record_id IS NULL
),
surplus AS
(
    SELECT
        records.record_id AS source_record_id,
        ROW_NUMBER() OVER (ORDER BY records.record_id) AS position
    FROM app.sample_records AS records
    CROSS JOIN settings
    WHERE records.record_id > settings.expected_max
)
SELECT
    surplus.source_record_id,
    missing.target_record_id
FROM surplus
JOIN missing
    ON missing.position = surplus.position
ORDER BY missing.target_record_id;