¿Cuál es la mejor forma de almacenar una dirección de correo electrónico en PostgreSQL?

Presta atención porque en este tutorial vas a hallar la contestación que buscas.

Solución:

Yo siempre uso CITEXT para correo electrónico, porque una dirección de correo electrónico (en la práctica) no distingue entre mayúsculas y minúsculas, es decir [email protected] es lo mismo que [email protected]

También es más fácil configurar un índice único para evitar duplicados, en comparación con el texto:

-- 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)));

Comparar correos electrónicos también es más fácil y menos propenso a errores:

SELECT * FROM address WHERE email = '[email protected]';

en comparación con:

SELECT * FROM address WHERE lower(email) = lower('[email protected]');

CITEXT es un tipo definido en un módulo de extensión estándar llamado “citext”, y está disponible escribiendo:

CREATE EXTENSION citext;

PD text y varchar son prácticamente iguales en Postgres y no hay penalización por usar text como es de esperar. Compruebe esta respuesta: diferencia entre texto y varchar

Personalizado DOMAINs

No creo que use citext (no distingue entre mayúsculas y minúsculas) es suficiente^[1]. Usando PostgreSQL podemos crear un dominio personalizado que es esencialmente algunas restricciones definidas sobre un escribe. Podemos crear un dominio, por ejemplo, sobre el citext tipo, o sobre text.

Usando HTML5 type=email Especificaciones

Actualmente, la respuesta más correcta a la pregunta de qué es una dirección de correo electrónico se especifica en RFC5322. Esa especificación es increíblemente compleja^[2], tan es así que todo lo rompe. HTML5 contiene una especificación diferente para el correo electrónico,

Este requisito es una violación deliberada de RFC 5322, que define una sintaxis para direcciones de correo electrónico que es simultáneamente demasiado estricta (antes del carácter “@”), demasiado vaga (después del carácter “@”) y demasiado laxa (permitiendo comentarios , caracteres de espacio en blanco y cadenas entre comillas de maneras desconocidas para la mayoría de los usuarios) para que sean de uso práctico aquí. […] La siguiente expresión regular compatible con JavaScript y Perl es una implementación de la definición anterior.

/^[a-zA-Z0-9.!#$%&'*+/=?^_`~-][email protected][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])?)*$/

Es probable que esto sea lo que desea, y si es lo suficientemente bueno para HTML5, probablemente sea lo suficientemente bueno para usted. Podemos hacer uso de eso directamente en PostgreSQL. Yo tambien uso citext aquí (lo que técnicamente significa que puede simplemente la expresión regular un poco visualmente eliminando las mayúsculas o minúsculas).

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`~-][email protected][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])?)*$' );

Ahora puedes hacer …

SELECT '[email protected]'::email;

Pero no

SELECT '[email protected],,ar.com'::email;
SELECT '[email protected]@foobar.com'::email;

Porque ambos regresan

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

Porque esto también se basa en citext

SELECT '[email protected]'::email = '[email protected]';

devoluciones true por defecto.

Utilizando plperlu/Email::Valid

Como nota importante, existe un método más correcto para hacer esto que es mucho más complejo usando plperlu. Si necesita este nivel de corrección, lo hace no querer citext. Email::Valid ¡Incluso puede verificar si el dominio tiene un registro MX (ejemplo en documentos de Correo electrónico :: Válido)! Primero, agregue plperlu (requiere superusuario).

CREATE EXTENSION plperlu;

Luego cree la función, observe que marcamos como un 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: $emailn";
  return 'true';
$$;

Luego crea el dominio,

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

Notas al pie

1. Utilizando citext es técnicamente incorrecto. SMTP define local-part como sensible a mayúsculas y minúsculas. Pero, de nuevo, este es un caso en el que la especificación es estúpida. Contiene sus propias crisis de identidad. La especificación dice local-part (la parte antes de la @) “PUEDE ser sensible a mayúsculas y minúsculas” … “DEBE SER tratado como sensible a mayúsculas y minúsculas” … y, sin embargo, “explotar la sensibilidad a mayúsculas y minúsculas de las partes locales del buzón impide la interoperabilidad y se desaconseja”.
2. La especificación de una dirección de correo electrónico es tan compleja que ni siquiera es autónoma. Complejo es realmente un eufemismo, aquellos que hacen la especificación ni siquiera lo entienden. De los documentos en regular-expression.info
   

   Ninguna de estas expresiones regulares impone límites de longitud en la dirección de correo electrónico general o la parte local o los nombres de dominio. RFC 5322 no especifica ninguna limitación de longitud. Aquellos provienen de limitaciones en otros protocolos como el protocolo SMTP para enviar correos electrónicos. RFC 1035 establece que los dominios deben tener 63 caracteres o menos, pero no lo incluye en su especificación de sintaxis. La razón es que un true el lenguaje regular no puede imponer un límite de longitud y no permitir guiones consecutivos al mismo tiempo.

Yo siempre uso varchar(254) ya que una dirección de correo electrónico no puede tener más de 254 caracteres.

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

Postgresql no tiene un tipo integrado para las direcciones de correo electrónico, aunque encontré algunos tipos de datos contribuidos.

Además, es posible que desee agregar un disparador o alguna lógica similar para estandarizar las direcciones de correo electrónico en caso de que desee agregar una única key en eso.

En particular, el domain parte de la dirección de correo electrónico (que es del formulario local-part@domain no distingue entre mayúsculas y minúsculas mientras local-part debe tratarse como sensible a mayúsculas y minúsculas. Ver http://tools.ietf.org/html/rfc5321#section-2.4

Otra consideración es si desea almacenar nombres y direcciones de correo electrónico en el formulario "Joe Bloggs" <[email protected]>, en cuyo caso necesitas un string más de 254 caracteres y no podrá usar una restricción única de manera significativa. No haría esto y sugeriría almacenar el nombre y la dirección de correo electrónico por separado. Siempre es posible imprimir direcciones bonitas en este formato en su capa de presentación.

Reseñas y puntuaciones del tutorial

Si crees que ha sido provechoso este post, agradeceríamos que lo compartas con otros entusiastas de la programación y nos ayudes a extender nuestro contenido.

---

---

Utiliza Nuestro Buscador