DOMAINs personalizados

Eu não acho que usar citext(não diferencia maiúsculas de minúsculas) é suficiente ^[1] . Usando o PostgreSQL podemos criar um domínio personalizado que é essencialmente algumas restrições definidas sobre um tipo . Podemos criar um domínio, por exemplo citext, sobre o tipo ou sobre text.

type=emailUsando a especificação HTML5

Atualmente, a resposta mais correta para a pergunta o que é um endereço de e-mail é especificada em RFC5322 . Essa especificação é insanamente complexa ^[2] , tanto que tudo a quebra. HTML5 contém uma especificação diferente para e-mail ,

Esse requisito é uma violação intencional da RFC 5322, que define uma sintaxe para endereços de e-mail que é simultaneamente muito estrita (antes do caractere "@"), muito vaga (após o caractere "@") e muito relaxada (permitindo comentários , caracteres de espaço em branco e strings entre aspas de maneiras desconhecidas para a maioria dos usuários) para serem de uso prático aqui. [...] A seguinte expressão regular compatível com JavaScript e Perl é uma implementação da definição acima.

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

Isso é provavelmente o que você quer, e se for bom o suficiente para HTML5, provavelmente é bom o suficiente para você. Podemos fazer uso disso diretamente no PostgreSQL. Eu também uso citextaqui (o que tecnicamente significa que você pode simplesmente o regex visualmente removendo as letras maiúsculas ou minúsculas).

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

Agora você pode fazer...

SELECT '[email protected]'::email;

Mas não

SELECT 'asdf@foob,,ar.com'::email;
SELECT 'asd@[email protected]'::email;

Porque ambos retornam

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

Porque isso também é baseado no citext

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

retorna verdadeiro por padrão.

Usando plperlu/Email::Valid

Como uma observação importante, existe um método mais correto de fazer isso que é muito mais complexo usando plperlu. Se você precisa desse nível de correção, você não quer citext. Email::Validpode até verificar se o domínio tem um registro MX (exemplo na documentação de Email::Valid)! Primeiro, adicione plperlu (requer superusuário).

CREATE EXTENSION plperlu;

Em seguida , crie a função , observe que marcamos como um 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';
$$;

Em seguida , crie o domínio ,

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

Notas de rodapé

1. Usar citexté tecnicamente errado. SMTP define local-partcomo sendo sensível a maiúsculas e minúsculas. Mas, novamente, este é um caso da especificação ser estúpida. Ele contém suas próprias crises de identidade. A especificação diz local-part(a parte antes do @) "Pode diferenciar maiúsculas de minúsculas" ... "Deve ser tratada como sensível a maiúsculas" ... e ainda "explorar a distinção entre maiúsculas e minúsculas das partes locais da caixa de correio impede a interoperabilidade e é desencorajada".
2. A especificação de um endereço de e-mail é tão complexa que nem é autocontida. Complexo é realmente um eufemismo, aqueles que fazem a especificação nem a entendem. . Dos documentos em regular-expression.info

Nenhuma dessas regexes impõe limites de comprimento no endereço de e-mail geral ou na parte local ou nos nomes de domínio. A RFC 5322 não especifica nenhuma limitação de comprimento. Esses decorrem de limitações em outros protocolos, como o protocolo SMTP para enviar e-mails. A RFC 1035 afirma que os domínios devem ter 63 caracteres ou menos, mas não inclui isso em sua especificação de sintaxe. A razão é que uma linguagem regular verdadeira não pode impor um limite de comprimento e não permitir hífens consecutivos ao mesmo tempo.

Eu sempre uso CITEXTpara email, porque um endereço de email (na prática) não diferencia maiúsculas de minúsculas , ou seja, [email protected] é o mesmo que [email protected].

Também é mais fácil configurar um índice exclusivo para evitar duplicatas, em comparação com o 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 e-mails também é mais fácil e menos propenso a erros:

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

em comparação com:

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

CITEXTé um tipo definido em um módulo de extensão padrão chamado "citext" e disponível digitando:

CREATE EXTENSION citext;

PS texte varcharsão praticamente os mesmos no Postgres e não há penalidade por usar textcomo se pode esperar. Verifique esta resposta: Diferença entre texto e varchar

Eu sempre uso varchar(254)como um endereço de e-mail não pode ter mais de 254 caracteres.

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

O Postgresql não tem um tipo embutido para endereços de e-mail, embora eu tenha encontrado alguns tipos de dados contribuídos.

Além disso, você pode querer adicionar um gatilho ou alguma lógica desse tipo para padronizar os endereços de e-mail caso deseje adicionar uma chave exclusiva a ele.

Em particular, a domainparte do endereço de e-mail (que tem o formato local-part@ domainnão diferencia maiúsculas de minúsculas, enquanto local-partdeve ser tratada como diferencia maiúsculas de minúsculas. Consulte https://www.rfc-editor.org/rfc/rfc5321#section-2.4

Outra consideração é se você deseja armazenar nomes e endereços de e-mail no formulário "Joe Bloggs" <[email protected]>; nesse caso, você precisa de uma string com mais de 254 caracteres e não poderá usar significativamente uma restrição exclusiva. Eu não faria isso e sugiro armazenar o nome e o endereço de e-mail separadamente. Endereços de impressão bonitos neste formato são sempre possíveis em sua camada de apresentação.

Você pode estar interessado em usar um CHECK CONSTRAINT(possivelmente mais fácil, mas pode rejeitar mais do que gostaria, ou usar uma FUNCTION, discutida aqui e aqui ). Você pode usar um regex com a restrição.

Você poderia fazer algo assim:

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

O ~*operador corresponde sem distinção entre maiúsculas e minúsculas . O regex( ¹ ) pode ser tão complexo quanto você quiser - veja esta resposta magistral que contém uma referência a um regex definitivo com 6.509 caracteres!

Basicamente, trata-se de compensações entre especificidade e facilidade de implementação. Mesmo assim, tópico interessante. O PostgreSQL tem até um tipo de endereço IP nativo, mas existe um projeto no pgfoundry para um tipo de dados de email aqui .

No entanto, o melhor que encontrei sobre isso é um domínio de e-mail . O domínio é melhor do que uma restrição de verificação porque se você alterá-lo, você só precisa fazer isso uma vez na definição do domínio e não seguir as trilhas das tabelas pai-filho alterando todas as suas restrições de verificação.

Os domínios são muito legais - como tipos de dados, mas mais simples de implementar. Eu os usei no Firebird - o Oracle nem os tem!

^{1) exemplo descaradamente retirado daqui}

Não existe um tipo de dados como 'email', mas ainda podemos usar regexp no PostgreSQL. Então, vamos aproveitar.

A primeira coisa que você gostaria de fazer é:

1. CREATE EXTENSION citext;Esse tipo de dados é para textos que diferenciam maiúsculas de minúsculas. Portanto, não importa se o texto está em letras maiúsculas ou minúsculas, o Postgres irá usar ambos como o mesmo texto.
2. Adicione uma restrição CHECK em sua tabela

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}$'
    )
);

Agora aqui, você está aplicando o regexp à sua coluna. person_emailA propósito, o regexp é meio ilustrativo, existem maneiras melhores de validar um email.

Para o registro, o tipo de dados em person_genderé um tipo personalizado

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

• Domínio email.sql- validação mínima básica, mas rápida. Porque a validação será acionada toda vez em uma UPDATEconsulta, mesmo que o valor do campo não tenha sido alterado!
• Função is_email.sql- validação quase de acordo com a especificação, mas lenta.