// advent-of-postgres/day-20
The Risky Migration
A cleanup migration that looks safe fails halfway. Test it on a Neon branch, fix it, and count what it really deletes.
The gift tracker lets children log in with their email, and some of them signed up two or three times. Bushy wrote a migration to clean that up and add a unique constraint. It runs tonight, on the real database.
You do not run unknown migrations on the real database. You run them on a copy first. On Neon, a branch is a full copy of your database that takes a second to create and costs nothing to throw away.
The children are in children:
| Column | Type | Notes |
|---|---|---|
id |
int |
Primary key |
name |
text |
The child's name |
email |
text |
The login; the same child can appear more than once |
Bushy's migration:
begin;
-- keep the first sign-up of every email, delete the rest
delete from day20.children c using day20.children d
where lower(c.email) = lower(d.email) and c.id > d.id;
-- store every email the same way
update day20.children set email = lower(trim(email));
alter table day20.children add constraint children_email_key unique (email);
commit;
Branches need your database in your Neon account. The steps:
- Run the starter query on this page, so today's data loads.
- If you have not claimed your database yet, press "Keep it for the month" and claim it into a free Neon account. Neon resets the password during the move, so paste the new connection string when the page asks.
- In the Neon Console, create a branch of the project.
- Open the branch in the SQL Editor (or connect to it with psql) and run the migration there. The page runs one statement at a time, so it is not the place for a migration with
beginandcommit.
Your task
The migration fails on your branch. Change only the delete so it compares lower(trim(email)) on both sides, and run it again on your branch (the failed run rolled back, so the branch is unchanged). How many rows does the fixed migration delete?
Submit the number.
Stuck? Show a hint
Look at the emails that differ only by case or by a trailing space. The DELETE and the UPDATE do not agree on what counts as the same address.
Download today's data as SQL to solve it on your own Postgres (14 or later).
Solved this puzzle? Share it.
Tag @thedevopsdaily and share it with the community.
We earn commissions when you shop through the links below.
Bitrise
Managed runners for GitHub Actions
Bitrise Build Hub runs your GitHub Actions jobs on M4 Macs and AMD Linux machines. Change runs-on and keep your workflows, secrets and logs on GitHub.
Atomsized
AWS platform engineering and GitOps
Design and automation for reliable AWS and Kubernetes platforms, safer delivery workflows, and preview and UAT environments your engineers can understand and own.
DigitalOcean
Cloud infrastructure for developers
Simple, reliable cloud computing designed for developers
DevDojo
Developer community & tools
Join a community of developers sharing knowledge and tools
SMTPfast
Developer-first email API
Send transactional and marketing email through a clean REST API. Detailed logs, webhooks, and embeddable signup forms in one dashboard.
QuizAPI
Developer-first quiz platform
Build, generate, and embed quizzes with a powerful REST API. AI-powered question generation and live multiplayer.
Want to support DevOps Daily and reach thousands of developers?
Become a Sponsor