Jaki typ danych byłby odpowiedni do przechowywania adresów e-mail w PostgreSQL?

Mogę użyć varchar(lub nawet text), ale zastanawiam się, czy istnieje bardziej konkretny typ danych dla wiadomości e-mail.

Custom DOMAINs

Nie sądzę, aby używanie citext(bez rozróżniania wielkości liter) było wystarczające ^[1] . Korzystając z PostgreSQL, możemy stworzyć domenę niestandardową, która jest zasadniczo pewnymi zdefiniowanymi ograniczeniami względem typu . Możemy stworzyć domenę na przykład nad citexttypem lub ponad text.

Korzystanie ze type=emailspecyfikacji HTML5

Obecnie najbardziej poprawna odpowiedź na pytanie, jakim jest adres e-mail, jest podana w RFC5322 . Ta specyfikacja jest niesamowicie złożona ^[2] , do tego stopnia, że wszystko ją psuje. HTML5 zawiera inną specyfikację dla e-maili ,

To wymaganie jest umyślnym naruszeniem RFC 5322, który definiuje składnię adresów e-mail, która jest jednocześnie zbyt surowa (przed znakiem „@”), zbyt niejasna (po znaku „@”) i zbyt luźna (dopuszczanie komentarzy , białych znaków i cytowanych ciągów w sposób nieznany większości użytkowników), aby można je było tutaj zastosować. [...] Poniższe wyrażenie regularne zgodne z JavaScript i Perl jest implementacją powyższej definicji.

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

Jest to prawdopodobnie to, czego chcesz, a jeśli jest wystarczająco dobre dla HTML5, prawdopodobnie jest wystarczająco dobre dla Ciebie. Możemy to wykorzystać bezpośrednio w PostgreSQL. Używam również citexttutaj (co technicznie oznacza, że ​​możesz po prostu trochę regexować wizualnie, usuwając wielkie lub małe litery).

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

Teraz możesz zrobić ...

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

Ale nie

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

Ponieważ oba powracają

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

Ponieważ jest to również oparte na citext

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

domyślnie zwraca true.

Używanie plperlu/Email::Valid

Co ważne, istnieje bardziej poprawna metoda wykonania tego zadania, która jest o wiele bardziej złożona w użyciu plperlu. Jeśli potrzebujesz tego poziomu poprawności, nie chcesz citext. Email::Validmoże nawet sprawdzić, czy domena ma rekord MX (przykład w dokumentach Email :: Valid)! Najpierw dodaj plperlu (wymaga superużytkownika).

CREATE EXTENSION plperlu;

Następnie utwórz funkcję , zauważ, że oznaczamy jako 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';
$$;

Następnie utwórz domenę ,

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

Przypisy

1. Używanie citextjest technicznie nieprawidłowe. SMTP określa local-partwielkość liter. Ale znowu jest to przypadek głupoty ze specyfikacji . Zawiera własne kryzysy tożsamości. Specyfikacja mówi local-part(część przed @) „MOŻE być rozróżniana wielkość liter” ... „MUSI BYĆ traktowana jako rozróżniana wielkość liter”… a jednak „wykorzystywanie rozróżniania wielkości liter w lokalnych częściach skrzynki pocztowej utrudnia interoperacyjność i jest odradzane”.
2. Specyfikacja adresu e-mail jest tak złożona, że ​​nie jest nawet samodzielna. Complex jest naprawdę mało powiedziane, a ci , którzy sprawiają, że specyfikacja nawet tego nie rozumie. . Z dokumentacji na regular-expression.info

   Żadne z tych wyrażeń regularnych nie wymusza ograniczenia długości ogólnego adresu e-mail, części lokalnej ani nazw domen. RFC 5322 nie określa żadnych ograniczeń długości. Wynika to z ograniczeń innych protokołów, takich jak protokół SMTP do faktycznego wysyłania wiadomości e-mail. RFC 1035 stwierdza, że ​​domeny muszą mieć maksymalnie 63 znaki, ale nie uwzględnia tego w specyfikacji składni. Powodem jest to, że prawdziwy język regularny nie może wymuszać ograniczenia długości i jednocześnie nie zezwalać na kolejne myślniki.

Zawsze używam CITEXTdo wiadomości e-mail, ponieważ w adresie e-mail (w praktyce) nie jest rozróżniana wielkość liter , tj. John@Example.com jest taki sam jak John@example.com.

Łatwiej jest również ustawić unikalny indeks, aby zapobiec duplikatom, w porównaniu do tekstu:

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

Porównywanie wiadomości e-mail jest również łatwiejsze i mniej podatne na błędy:

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

w porównaniu do:

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

CITEXTjest typem zdefiniowanym w standardowym module rozszerzeń o nazwie „citext” i dostępnym po wpisaniu:

CREATE EXTENSION citext;

PS texti varcharsą praktycznie takie same w Postgres i nie ma kary za używanie, textjak można się spodziewać. Sprawdź tę odpowiedź: Różnica między tekstem a varcharem

Zawsze używam, varchar(254)ponieważ adres e-mail nie może być dłuższy niż 254 znaki.

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

Postgresql nie ma wbudowanego typu adresów e-mail, chociaż natknąłem się na niektóre typy danych.

Ponadto możesz dodać wyzwalacz lub inną logikę w celu ustandaryzowania adresów e-mail na wypadek, gdybyś chciał dodać do niego unikalny klucz.

W szczególności domainczęść adresu e-mail (która ma postać local-part@, domainnie rozróżnia małych i wielkich liter, podczas gdy local-partmusi być traktowana jako rozróżniana wielkość liter . Patrz http://tools.ietf.org/html/rfc5321#section-2.4

Inną kwestią jest, jeśli chcesz przechowywać nazwy i adresy e-mail w formularzu "Joe Bloggs" <joe.bloggs@hotmail.com>, w którym to przypadku potrzebujesz ciągu dłuższego niż 254 znaków i nie będziesz w stanie znacząco użyć unikalnego ograniczenia. Nie zrobiłbym tego i sugeruję osobne przechowywanie nazwiska i adresu e-mail. Ładne drukowanie adresów w tym formacie jest zawsze możliwe w warstwie prezentacji.

Możesz być zainteresowany skorzystaniem z czeku CONSTRAINT (być może łatwiejszym, ale możesz odrzucić więcej, niż chcesz, lub korzystasz z FUNKCJI omówionej tutaj i tutaj . Zasadniczo chodzi o kompromisy między specyfiką a łatwością implementacji. Ciekawy temat chociaż. PostgreSQL ma nawet rodzimy typ adresu IP, ale nie jest to projekt na pgfoundry dla typu danych email tutaj . jednak najlepszym znalazłem na ten temat jest e-mail domeny. Domena jest lepsza niż ograniczenie sprawdzania, ponieważ jeśli je zmienisz, musisz to zrobić tylko raz w definicji domeny i nie podążać śladami w dół tabel nadrzędnych i podrzędnych, zmieniając wszystkie ograniczenia sprawdzania. Domeny są naprawdę fajne - trochę jak typy danych, ale prostsze do wdrożenia. Użyłem ich w Firebird - Oracle nawet ich nie ma!