34

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.

32

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.

  • 1
    The W3.org link is broken; here's an alternate source: html.spec.whatwg.org/multipage/… – MaxGabriel Feb 17 '18 at 2:02
  • @MaxGabriel thanks stick around, you'll get the edit perms soon enough I'll get it fixed in there. – Evan Carroll Feb 17 '18 at 2:22
  • Is there a reason to have both a-z and A-Z in the character classes? – xehpuk May 31 '18 at 9:00
  • @xehpuk well, because ~ is case sensitive you either have to (a) use ~* case insensitive or (b) have the upper and lower case letters in the char-class. – Evan Carroll May 31 '18 at 14:40
  • citext's ~ seems to be case-insensitive to me, that's why I'm asking. – xehpuk May 31 '18 at 14:51
43

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

10

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 http://tools.ietf.org/html/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.

  • According to 4.5.3.1. Size Limits and Minimums, the maximum length is 320 chars (including the @). – Andriy M Mar 4 '17 at 20:37
  • 1
    @AndriyM There's nothing in referenced section which says 320. And that's wrong anyway; tools.ietf.org/html/rfc5321#section-4.5.3.1.3 states that the maximum length of a path is 256 characters, and that has to include the surrounding "<" and ">" making the maximum 254. – Colin 't Hart Mar 5 '17 at 6:43
  • I arrived at 320 as a maximum based on 4.5.3.1.1 ("The maximum total length of a user name or other local-part is 64 octets") and 4.5.3.1.2 ("The maximum total length of a domain name or number is 255 octets"). So, 64 + 255 + 1 (the @) = 320. Perhaps I'm misinterpreting it. – Andriy M Mar 5 '17 at 9:37
  • 2
    @AndriyM Read the accepted answer to the question I linked to. It explains it all. It's definitely 254, and not 320. – Colin 't Hart Mar 5 '17 at 10:38
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.