7 PostgreSQL data migration hacks you should be using (but aren't)

batch_at_will_commander.sql

CREATE FUNCTION batch_at_will() RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE batched_count INTEGER = 1;
BEGIN
    WITH selected_users AS (
        SELECT id
        FROM users
        WHERE role = 'moderator'
        AND registration_date < CURRENT_DATE - INTERVAL '4' YEAR
        LIMIT 1000
        FOR UPDATE NOWAIT
    ),
    updated_users AS (
        UPDATE users
        SET role = 'admin'
        FROM selected_users
        WHERE selected_users.id = users.id
        RETURNING users.id
    ) SELECT COUNT(1) INTO batched_count FROM updated_users;
    RETURN batched_count;
END$$;

DO LANGUAGE plpgsql $$
DECLARE counter INTEGER = 1;
BEGIN
    WHILE counter > 0 LOOP
        SELECT INTO counter batch_at_will();
    END LOOP;
END$$;

delete_fu_query.sql

WITH deleted_orders AS (
    DELETE FROM orders o
    WHERE o.deleted = 1
    RETURNING guid AS deleted_guid
)
SELECT deleted_guid
FROM deleted_orders;

in_n_out_query.sql

WITH inserted_managers AS (
    INSERT INTO managers(guid, type, name, deleted)
    SELECT
        REPLACE(UPPER(UUID_GENERATE_v4()::text), '-', ''),
        e.type,
        e.name,
        e.deleted
    FROM employees e
    WHERE e.type = 'manager'
    RETURNING *
)
SELECT *
FROM inserted_managers;

in_through_the_out_query.sql

INSERT INTO related_rows(id, type, value)
SELECT
    v.related_id,
    'backfilled',
    v.value
FROM values v;

keep_calm_and_archive_on.sql

WITH latest_orders AS (
    SELECT o.id, o.order_date,
        ROW_NUMBER() OVER(
            PARTITION BY o.customer_id
            ORDER BY o.order_date DESC
        ) AS rank
    FROM orders o
),
deleted_customers AS (
   DELETE FROM customers c
   USING latest_orders
   WHERE c.id = latest_orders.customer_id
     AND latest_orders.rank = 1
     AND latest_orders.order_date < CURRENT_DATE - INTERVAL '10' YEAR
   RETURNING *
),
archived_customers AS (
   INSERT INTO archived_customers
   SELECT *
   FROM deleted_customers
   RETURNING *
)
SELECT *
FROM archived_customers;

rank_and_file.sql

WITH summary AS (
    SELECT id, ROW_NUMBER() OVER(
        PARTITION BY u.external_id
        ORDER BY u.date_modified DESC
    ) AS rank
    FROM users u
)
SELECT id
FROM summary
WHERE rank = 1;