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...
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
create temp sequence tmp; Works fine :-).
INSERT, notUPDATE