sql: prepend/append something to email field

Question

I routinely import a copy of the production database, replacing my development database.

However, during development, I don't want t accidently email any production users. (their emails are stored in the email column of the users table)

How can I update all the email addresses so that if I accidentally email them, it goes to nowhere

if would also be nice if the original email is somehow embedded in the faked email.

Answer 1

Given: the email column holds one email (no commas, no monkey hacks, hidden commands - just an email). Then:

update users set email=email||'.fail';

Will update all email, adding .fail to the end where email is not null, if it is null, it will remain such. To revert back, run:

update users set email=substring(email,1,length(email)-5) 
  where substring(email from '.....$') = '.fail';

example of dataset:

t=# with users(email) as (values('pop@pop.pop'||'.fail'),(null))
select substring(email,1,length(email)-5) from users where substring(email from '.....$') = '.fail';
  substring
-------------
 pop@pop.pop
(1 row)