Skip to content

Instantly share code, notes, and snippets.

@aanari
Last active August 8, 2020 15:31
Show Gist options
  • Save aanari/349c7d97ed50c6f69930 to your computer and use it in GitHub Desktop.
Save aanari/349c7d97ed50c6f69930 to your computer and use it in GitHub Desktop.
7 PostgreSQL data migration hacks you should be using (but aren't)
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$$;
WITH deleted_orders AS (
DELETE FROM orders o
WHERE o.deleted = 1
RETURNING guid AS deleted_guid
)
SELECT deleted_guid
FROM deleted_orders;
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;
INSERT INTO related_rows(id, type, value)
SELECT
v.related_id,
'backfilled',
v.value
FROM values v;
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;
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment