Re: Email address column verification foraddress list - Mailing list pgsql-general

From Andrus
Subject Re: Email address column verification foraddress list
Date
Msg-id F8E3EEE23D5F4BF7BC17A2795CCE8B2E@andrusnotebook
Whole thread Raw
In response to Re: Email address column verification for address list  (Peter Geoghegan <peter.geoghegan86@gmail.com>)
Responses Re: Email address column verification foraddress list
List pgsql-general
Peter,

thank you.

> Why don't you just separate them into individual fields (perhaps in a
separate table to have arbitrary many addresses)? What you're doing
violates 1NF - fields should be atomic (i.e. in their simplest form,
so you never have to parse values from them).

This is existing database deployed to many sites and used by many programs.
Re-factoring db and software to add this minor feature seems to be not
reasonable. For 99% of cases field contains only single address.

> Here's a reasonable email address domain. Note that it is AS text, not
>AS character(200). It does not attempt to match the TLD to a list of
>known TLDs, nor should it (that's very probably impractical, at least
>with regex):

>CREATE DOMAIN email_address
>  AS text
>   CONSTRAINT email_address_check CHECK ((VALUE ~

>'^(([A-Za-z0-9]+_+)|([A-Za-z0-9]+\\-+)|([A-Za-z0-9]+\\.+)|([A-Za-z0-9]+\\++))*[A-Za-z0-9]+@((\\w+\\-+)|(\\w+\\.))*\\w{1,63}\\.[a-zA-Z]{2,6}$'::text));
>If you absolutely must put an arbitrary number of e-mail addresses in
one field, I suggest you use the domain in an array. Unfortunately,
arrays of domains are not directly supported, last I checked.

I don't know how to use arrrays to solve this.

How to create constraint for char(200) column for comma separated email list
?

Andrus.


pgsql-general by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: Email address column verification for address list
Next
From: "Satish Burnwal (sburnwal)"
Date:
Subject: Query is stuck