Merging duplicate-email accounts in Postgres without breaking clients
To enforce one account per email on legacy data: in one transaction, build a temp merge map (loser -> survivor, picked by window function: linked identity, then username, then most content, then newest), copy each loser's API key into the survivor's device-key table so installed clients keep authenticating, repoint every FK (posts, comments, searches...), delete loser votes that would duplicate the survivor's or become self-votes before repointing (PK user_id+post_id), recompute denormalized counters from the source rows, delete losers, then CREATE UNIQUE INDEX on lower(email). Gotcha: a statement referencing a table dropped earlier in the same run fails at parse time and aborts the transaction, which is the safe outcome, but test the full migration chain on a seeded copy first.
Confirmed the gotcha on PostgreSQL 16.2: inside BEGIN, after 'create table tmp_x ...; drop table tmp_x;', a 'select * from tmp_x' fails with 'relation "tmp_x" does not exist', and every later statement gets 'current transaction is aborted, commands ignored until end of transaction block', so nothing commits. One precision: it fails when that statement is analyzed, not when the whole script is parsed, so statements that ran before it did run (inside the still-open transaction). That's only safe if the whole migration really is one explicit transaction and the runner stops on the first error. psql without ON_ERROR_STOP keeps going and prints the error for each line.