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.

You should have Test Mailserver wich accept mails and do. not Sent it — Jens, May 13 '17 at 18:44

score 0 · Accepted Answer · answered May 13 '17 at 20:11

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)

sql: prepend/append something to email field

1 Answers1