How to validate Email Addresses in an SQL Database
Check if Email Addresses are really correct in an SQL without sending any messages.
If you have a table full of Email Addresses that are potentially invalid and you want to clean them up. Maybe the data was collected without input validation or they were collected a long time ago and some email accounts maybe have been closed.
In this example we will assume the table is defined like this.
create table people (id serial primary key, email text);
You could test the emails with a like query or a regex but its not recommended as it can give incorrect answers.
-- using a like query is not recommended
SELECT email, email LIKE '%_@_%.__%' AS isVaild FROM people;
This approach will tell which email addresses look right, but they could still be wrong and bounce. See what is email validation.
To validate this table using Real Email you will need to export the table as a CSV file, validate it with Real Email, then import the results.
Export the table as a CSV file with headers using the Postgresql copy command. Make sure you include the id primary key, so you can import the results. In MySQL you would use a select into outfile command.
COPY people TO '/tmp/people.csv' WITH (FORMAT CSV, HEADER);
Login to Real Email and upload your CSV file. Depending on the size this may take a little while. For more details see Bulk Email Address validation with CSV File.
Import the results into a new temporary table with a status column.
CREATE TABLE people_import (id SERIAL, email text, status text);
COPY people_import FROM '/tmp/people-validated.csv' WITH (FORMAT CSV, HEADER);
If you wanted to create an
isValid column to set the status.
ALTER TABLE people add COLUMN isValid boolean;
UPDATE people AS p
SET isValid = (i.status = 'valid' OR i.status = 'unknown')
FROM people_import AS i
WHERE p.id = i.id
Keep in mind the status can be valid, invalid or unknown. Depending on your use case you may want to handle unknown differently. For more details see the Real Email API reference.
Or if you wanted to delete the invalid ones you could do it like.
DELETE FROM people p
USING people_import i
WHERE p.id = i.id AND i.status = 'invalid';