A check constraint which would call a function to validate email addresses is not working fine for me.

CREATE OR REPLACE FUNCTION f_IsValidEmail(text) returns BOOLEAN AS 
'select $1 ~ ''^[^@\s]+@[^@\s]+(\.[^@\s]+)+$'' as result
' LANGUAGE sql;



SELECT f_IsValidEmail('myemail@ad开发者_StackOverflow社区dress.com');

The function is returning false, which should be true. I have tried a couple of other regexs but in vain. Can anyone point out what's wrong with this function?

Screenshot

A bunch of these answers are close to the right way. These are the points for my submission.

• You want to use a domain -- NOT the rule system.
• You do NOT want to validate these email addresses with a regex. (Update Mar 2017: not really true anymore)

I show two methods of how to do this the right on DBA.StackExchange.com. Both to check for the MX-record, and also using the HTML5 spec. Here is the short and sweet.

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

SELECT 'foobar@bar.com'::email;
SELECT CAST('foobar@bar.com' AS email);

For more information I highly suggest you read the answer in full. In the answer, I also show how you create a DOMAIN over Email::Valid, and explain why I no longer use that method.

Before you go putting a lot of effort into this, the thing you want to do is make sure you're not kicking out valid email addresses. There's all kinds of insane rules for can or can't be in an email address, and if you get it wrong, in the wrong direction, a user with a perfectly valid email address might get rejected by your system.

The best way to determine if an email address is valid is to use it as part of a registration process where email is REQUIRED. Anything else is a lot of work for little gain.

I recommend a solution using PL/Perl and the Email::Address module. Something like the following:

CREATE OR REPLACE FUNCTION email_valid(email text) RETURNS bool
LANGUAGE plperlu
AS $$
use Email::Address;
my @addresses = Email::Address->parse($_[0]);
return scalar(@addresses) > 0 ? 1 : 0;
$$;

See also http://wiki.postgresql.org/wiki/Email_address_parsing.

If you can figure out a regular expression that matches email addresses to your satisfaction, consider whether it might not be more useful as a domain rather than a check constraint.

For some of the pitfalls see regular-expressions.info.

Don't attempt to create a regex to validate e-mails!

It is notoriously difficult to accomplish. Here's a better solution:

Assuming that you have Perl installed on your database host, install the Email::Valid module on the same host using CPAN:

you@localhost$ cpan Email::Valid

Then make sure that you have PL/Perl installed. Connect to your database in psql and add plperlu as a language:

CREATE EXTENSION plperlu;

(Keep in mind that this is an untrusted language, so you'll be giving your db direct file access, which could pose a security risk if someone were to insert malicious code into your Perl modules or into the db functions. However, you need to do it for the next step.)

Add the following function to your database:

CREATE FUNCTION validate_email() RETURNS trigger AS $$
  use Email::Valid;
  return if Email::Valid->address($_TD->{new}{email});
  elog(ERROR, "invalid email address $_TD->{new}{email} inserted into $_TD->{table_name}(email)");
  return "SKIP";
$$ LANGUAGE plperlu;

Add a trigger constraint to your table on your column (assuming that your table is named "users" and your column is named "email"):

CREATE TRIGGER users_valid_email_trig
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE PROCEDURE validate_email();

And you're done!

This solution uses the Email::Valid Perl module to handle validation, which in turn relies on a regex to ensure RFC 822 compliance. However, it is a monster of a regex, so don't try to come up with your own.

If you feel uncomfortable with enabling plperlu instead of plain plperl, you could probably port the relevant functions into your database.

Works for me:

psql (9.0.2)
Type "help" for help.

postgres=> CREATE OR REPLACE FUNCTION "f_IsValidEmail"(text) returns BOOLEAN AS
postgres-> 'select $1 ~ ''^[^@\s]+@[^@\s]+(\.[^@\s]+)+$'' as result
postgres'> ' LANGUAGE sql;
CREATE FUNCTION
postgres=> commit;
COMMIT
postgres=> SELECT "f_IsValidEmail"('myemail@address.com');
 f_IsValidEmail
----------------
 t
(1 row)

postgres=>

• Domain email.sql - basic minimal validation, but fast. Because validation will be triggered every time on an UPDATE query, even if the value of the field has not changed!
• Function is_email.sql - validation almost according to specification, but slow.