Replace all email addresses after ‘@’ in PostgresSQL

I have a list of emails. I want to change all of them to test emails for my test system e.g. [e-mail] to [e-mail]. I don’t want to use actual emails as those emails are valid and it will be bad for users to receive such email. Is it possible to change all emails at once in a single query? If so, can anyone share? Thanks.

Answer

You can do it with the REGEXP_REPLACE function.

If you need to select only, you can use the following query:

SELECT REGEXP_REPLACE(email, '@.*', '@test.com')
FROM tab

If instead you want to update the field value, you can use:

UPDATE tab
SET email = REGEXP_REPLACE(email, '@.*', '@test.com');

Try it here.