What is the best way to store an email address in PostgreSQL?

Question

What would be the right datatype to store email addresses in PostgreSQL?

I can use varchar (or even text), but I wonder if there is a more specific data type for emails.

Answer 1

Custom DOMAINs

I don't think using citext (case-insensitive) is enough^[1]. Using PostgreSQL we can create a custom domain which is essentially some defined constraints over a type. We can create a domain for instance over the citext type, or over text.

Using HTML5 type=email spec

Currently the most correct answer to the question what is an e-mail address is specified in RFC5322. That spec is insanely complex^[2], so much so that everything breaks it. HTML5 contains a different spec for email,

This requirement is a willful violation of RFC 5322, which defines a syntax for e-mail addresses that is simultaneously too strict (before the "@" character), too vague (after the "@" character), and too lax (allowing comments, whitespace characters, and quoted strings in manners unfamiliar to most users) to be of practical use here. [...] The following JavaScript- and Perl-compatible regular expression is an implementation of the above definition.

/^[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])?)*$/

This is likely what you want, and if it's good enough for HTML5, it's probably good enough for you. We can make use of that directly in PostgreSQL. I also use citext here (which technically means you can simply the regex a bit visually by removing either the upper-case or lower-case).

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])?)*$' );

Now you can do...

SELECT 'asdf@foobar.com'::email;

But not

SELECT 'asdf@foob,,ar.com'::email;
SELECT 'asd@f@foobar.com'::email;

Because both of those return

ERROR:  value for domain email violates check constraint "email_check"

Because this is based on citext too

SELECT 'asdf@foobar.com'::email = 'ASdf@fooBAR.com';

returns true by default.

Using plperlu/Email::Valid

As an important note, there is a more correct method of doing this that is far more complex using plperlu. If you need this level of correctness you do not want citext. Email::Valid can even check if the domain has an MX record (example in docs of Email::Valid)! First, add plperlu (requires superuser).

CREATE EXTENSION plperlu;

Then create the function, notice we mark at as an IMMUTABLE:

CREATE FUNCTION valid_email(text)
  RETURNS boolean
  LANGUAGE plperlu
  IMMUTABLE LEAKPROOF STRICT AS
$$
  use Email::Valid;
  my $email = shift;
  Email::Valid->address($email) or die "Invalid email address: $email\n";
  return 'true';
$$;

Then create the domain,

CREATE DOMAIN validemail AS text NOT NULL
  CONSTRAINT validemail_check CHECK (valid_email(VALUE));

Footnotes

1. Using citext is technically wrong. SMTP defines local-part as being case sensitive. But, again, this is a case of the spec being stupid. It contains its own identity crises. The spec says local-part (the part before the @) "MAY be case-sensitive" ... "MUST BE treated as case sensitive" ... and yet "exploiting the case sensitivity of mailbox local-parts impedes interoperability and is discouraged."
2. The spec for an email address is so complex, it's not even self-contained. Complex is truly an understatement, those making the spec don't even understand it.. From the docs on regular-expression.info

Neither of these regexes enforce length limits on the overall email address or the local part or the domain names. RFC 5322 does not specify any length limitations. Those stem from limitations in other protocols like the SMTP protocol for actually sending email. RFC 1035 does state that domains must be 63 characters or less, but does not include that in its syntax specification. The reason is that a true regular language cannot enforce a length limit and disallow consecutive hyphens at the same time.

Answer 2

I always use CITEXT for email, because an email address is (in practice) case insensitive, i.e. John@Example.com is same as john@example.com.

It is also easier to setup an unique index to prevent duplicates, as compared to text:

-- citext
CREATE TABLE address (
   id serial primary key,
   email citext UNIQUE,
   other_stuff json
);

-- text
CREATE TABLE address (
   id serial primary key,
   email text,
   other_stuff json
);
CREATE UNIQUE INDEX ON address ((lower(email)));

Comparing emails is also easier and less prone to errors:

SELECT * FROM address WHERE email = 'JOHN@example.com';

as compared to:

SELECT * FROM address WHERE lower(email) = lower('JOHN@example.com');

CITEXT is a type defined in a standard extension module named "citext", and available by typing:

CREATE EXTENSION citext;

P.S. text and varchar are virtually the same in Postgres and there is no penalty for using text as one may expect. Check this answer: Difference between text and varchar

Answer 3

I always use varchar(254) as an email address may not be longer than 254 characters.

See https://stackoverflow.com/questions/386294/what-is-the-maximum-length-of-a-valid-email-address

Postgresql has no built-in type for email addresses, though I did come across some contributed data type.

In addition, you may wish to add a trigger or some such logic to standardize email addresses in case you wish to add a unique key on it.

In particular, the domain part of the email address (which is of the form local-part@domain is case insensitive while local-part must be treated as case sensitive. See https://www.rfc-editor.org/rfc/rfc5321#section-2.4

Another consideration is if you wish to store names and email addresses in the form "Joe Bloggs" <joe.bloggs@hotmail.com>, in which case you need a string longer than 254 characters and you won't be able to meaningfully use a unique constraint. I wouldn't do this and suggest storing name and email address separately. Pretty printing addresses in this format is always possible in your presentation layer.

Answer 4

You might be interested in using a CHECK CONSTRAINT (possibly easier, but might reject more than you'd want to, or you use a FUNCTION, discussed here and here). You can use a regex with the constraint.

You could do something like this:

CREATE TABLE person 
(
  name TEXT,
  address1 TEXT,
  ...
  ...  other fields...
  ...
  email TEXT
  ...    
  CONSTRAINT 
    proper_email CHECK (email ~* '^[A-Za-z0-9._+%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
);

The ~* operator matches case-insensitively. The regex(¹) can be as complex as you like - see this magisterial answer which contains a reference to a definitive regex that is 6,509 characters long!

Bascially, it's all about tradeoffs between specificity and ease of implementation. Interesting topic though. PostgreSQL even has a native IP address type, but there is a project on pgfoundry for an email data type here.

However, the best I found about this is an email domain. The domain is better than a check constraint because if you change it, you only have to do it once in the domain definition and not follow trails down parent-child tables changing all your check constraints.

Domains are really cool - kinda like data types, but simpler to implement. I used them in Firebird - Oracle doesn't even have them!

^{1) example shamelessly lifted from here}

Answer 5

There is no such a data type like 'email', but we can still use regexp within PostgreSQL. So, let's take advantage of it.

The very first thing you would like to do is:

1. CREATE EXTENSION citext; This data type is for texts to be case INsensitive. So, doesn't matter if the text is in upper or lower case, Postgres will take them both as the same text.
2. Add a CHECK constraint within your table

CREATE TABLE persons(
    person_id SERIAL PRIMARY KEY,
    person_first_name VARCHAR(50) NOT NULL,
    person_last_name VARCHAR(50),
    person_gender gender_t NOT NULL,
    person_birthdate DATE,
    person_email CITEXT NOT NULL UNIQUE,
    CHECK(
        person_email ~ '^[\w]+\@\w{0,6}\.\w{2,4}$'
    )
);

Now here, you are applying the regexp to your person_email column.By the way, the regexp is kinda illustrative,there are better ways to validate an email.

For the record, the data type in person_gender is a custom type

CREATE TYPE gender_t AS ENUM('M', 'F');

Answer 6

• 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.