How to increment a variable in PostgreSQL UPDATE statement

Question

I want to add e-mails numbered from 1 to 100. So effectively I want to do something like:

tmp := 0
update user SET tmp = tmp + 1, email = 'test' || TO_CHAR(tmp,'fm00000') || '@example.com'
    where removed = false
    ORDER BY id
    limit 100;

And preferably would like it to work in 8.3. Note - I cannot use ids as they are not from 1 to 100 in the selected rows.

Edit: Forgot I'm on 8.3...

Answer 1

You don't need a hack with variables in Postgres. Just use a window function. Unfortunately window functions cannot be used directly in an UPDATE statement, but this can easily be combined with a CTE:

with numbered_users as (
   select id, 
          row_number() over (order by email) as rn
   from users
   where removed = false
) 
update users 
  set email = 'test' || TO_CHAR(nu.rn,'fm00000') || '@example.com'
from numbered_users nu
where rmoved = false
  and users.id = nu.id;

This assumes the id column is unique