Thread: newbie: Column CHECK(col contains '@') ?

Hello list...

One column in my table contains email addresses - I want to check that any value entered contains a '@'.  How do I
createa CONSTRAINT or CHECK to ensure this when creating the table? 

Thanks for any help.
/j-p.


-----------------------
 JUSTATEST Art Online
  www.justatest.com

JP,

> One column in my table contains email addresses - I want to check
> that any value entered contains a '@'.  How do I create a CONSTRAINT
> or CHECK to ensure this when creating the table?

Here's the online documentation.  It's very good, you should give it a
try!

http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/sql-createtable.html

-Josh Berkus

> One column in my table contains email addresses - I want to check
> that any value entered contains a '@'.  How do I create a
> CONSTRAINT or CHECK to ensure this when creating the table?

create table em (
  em text constraint is_email check (em like '%@%.%')
);

will work fine, assuming that this check (something @ something . something)
is acceptable in your context as "looks like an email address"

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

Thanks Joel... that did the trick (even better than I had asked for).
Forgive my ignorance, but it your solution a regular expression?

Can anyone suggest a good source where I can read up on these (regex's) in relation to postgresql?

thanks again,
/j-p.


On Sun, 12 May 2002, Joel Burton wrote:

> > One column in my table contains email addresses - I want to check
> > that any value entered contains a '@'.  How do I create a
> > CONSTRAINT or CHECK to ensure this when creating the table?
>
> create table em (
>   em text constraint is_email check (em like '%@%.%')
> );
>
> will work fine, assuming that this check (something @ something . something)
> is acceptable in your context as "looks like an email address"
>
> - J.
>
> Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> Knowledge Management & Technology Consultant
>


-----------------------
 JUSTATEST Art Online
  www.justatest.com

On Mon, 2002-05-13 at 11:21, john-paul delaney wrote:
> Thanks Joel... that did the trick (even better than I had asked for).
> Forgive my ignorance, but it your solution a regular expression?
>
> Can anyone suggest a good source where I can read up on these (regex's)
> in relation to postgresql?

The PostgreSQL manual has a section (section 4.6) on pattern matching
using REGEX and pattern matching using the SQL 'LIKE' operator.

The LIKE operator (which was what Joel used in his solution for you)
uses '%' as a wildcard and _ as a single character match.

Regex is much more complicated, and there are many sources of help for
it out on the internet.  A similar check using a regex operator would be
something like:

(em ~ '@.*\.')

since there is no need to specify leading and trailing wildcards within
a regex (instead you specify that you want to anchor the regex to the
beginning and/or ending of the string).

A search on the internet might provide a more thorough regex for
validation of e-mail addresses.  A slightly more complex one I have used
is:

(em ~* '^[^@]+@[a-z0-9-]+\.[a-z]+')

which should validate (a) there is only a single '@' in the address and
(b) the first part of the domain name contains only valid domain-name
like characters.  The ~* operator is the case insensitive regex match
which I didn't use in the one above since there was no alphabetic
matching involved.

How I do this in my own applications is actually to implement a function
for valid email addresses, viz:

CREATE FUNCTION valid_email(TEXT) RETURNS BOOLEAN AS '
  DECLARE
    email ALIAS FOR $1;
    user TEXT;
    domain TEXT;
  BEGIN
    IF email !~ ''.@.'' THEN
      RETURN FALSE; -- One @ good
    END IF;
    IF email ~ ''@.*@'' THEN
      RETURN FALSE; -- Two @s bad
    END IF;
    domain := substring( email from position( ''@'' in email) + 1 );
    user   := substring( email from 1 for position( ''@'' in email) - 1
);
    IF domain ~* ''([a-z0-9-]+\.)+([a-z])?[a-z][a-z]$'' THEN
      -- Only really worth validating the domain
      RETURN TRUE;
    END IF;
    RETURN FALSE;
  END;
' LANGUAGE 'plpgsql';

This checks for internal spaces as well, and means that the rules for
valid e-mail addresses happens in only one place.

Regards,
                    Andrew.
>
> thanks again,
> /j-p.
>
>
> On Sun, 12 May 2002, Joel Burton wrote:
>
> > > One column in my table contains email addresses - I want to check
> > > that any value entered contains a '@'.  How do I create a
> > > CONSTRAINT or CHECK to ensure this when creating the table?
> >
> > create table em (
> >   em text constraint is_email check (em like '%@%.%')
> > );
> >
> > will work fine, assuming that this check (something @ something . something)
> > is acceptable in your context as "looks like an email address"
> >
> > - J.
> >
> > Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> > Knowledge Management & Technology Consultant
> >
>
>
> -----------------------
>  JUSTATEST Art Online
>   www.justatest.com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?

JP,

> Thanks Joel... that did the trick (even better than I had asked for).
>
> Forgive my ignorance, but it your solution a regular expression?

Actually, no.  The LIKE and ILIKE operators are a regular part of SQL.
 They really only accept one "wildcard", the % in place of "anything".

Postgres has a Regexp operator, "~" (the tilde) which does Unix-style
pattern-matching.

> Can anyone suggest a good source where I can read up on these
> (regex's) in relation to postgresql?

2 Places:  Functions and Operators, in the online docs.
Any beginner's guid to PostgreSQL, such as Bruce Momjian's book or the
Wrox Press book.  (see http://techdocs.postgresql.org/  ----> book
reviews).

-Josh Berkus

Apologies Josh... I'm so dumb.  I overlooked the Functions and Opterators chapter (it's taking me a little time to
assimilatethe structure of online documentation).  Thanks for your patience and explanations. 

/j-p.


On Sun, 12 May 2002, Josh Berkus wrote:

> JP,
>
> > Thanks Joel... that did the trick (even better than I had asked for).
> >
> > Forgive my ignorance, but it your solution a regular expression?
>
> Actually, no.  The LIKE and ILIKE operators are a regular part of SQL.
>  They really only accept one "wildcard", the % in place of "anything".
>
> Postgres has a Regexp operator, "~" (the tilde) which does Unix-style
> pattern-matching.
>
> > Can anyone suggest a good source where I can read up on these
> > (regex's) in relation to postgresql?
>
> 2 Places:  Functions and Operators, in the online docs.
> Any beginner's guid to PostgreSQL, such as Bruce Momjian's book or the
> Wrox Press book.  (see http://techdocs.postgresql.org/  ----> book
> reviews).
>
> -Josh Berkus
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


-----------------------
 JUSTATEST Art Online
  www.justatest.com

JP,

> Apologies Josh... I'm so dumb.  I overlooked the Functions and
> Opterators chapter (it's taking me a little time to assimilate the
> structure of online documentation).  Thanks for your patience and
> explanations.

Well, the online docs are a reference, not a learning tool.  I heartily
reccommend that you get a PostgreSQL book, it'll save you a *lot* of
time asking questions of the list.

-Josh

I second that!! I bought the PostgreSQL Developer's Handbook and it has been
a G*D send. Between that and Professional PHP4 I've been able to make a lot
of progress without going in too many circles.

Although I still don't understand half of what is said on here :)

Julie
<snip>
----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "john-paul delaney" <jp@justatest.com>; <pgsql-novice@postgresql.org>

> Well, the online docs are a reference, not a learning tool.  I heartily
> reccommend that you get a PostgreSQL book, it'll save you a *lot* of
> time asking questions of the list.
>
> -Josh

Wow... and this is the Novice list?  Thanks Andrew, you cover it all from a to z.  It'll take me a bit of work to
digestyour message and put it into practice, as the (weird and) wonderful world of regex's is very new to me.  

regards
/j-p.


On 13 May 2002, Andrew McMillan wrote:

> On Mon, 2002-05-13 at 11:21, john-paul delaney wrote:
> > Thanks Joel... that did the trick (even better than I had asked for).
> > Forgive my ignorance, but it your solution a regular expression?
> >
> > Can anyone suggest a good source where I can read up on these (regex's)
> > in relation to postgresql?
>
> The PostgreSQL manual has a section (section 4.6) on pattern matching
> using REGEX and pattern matching using the SQL 'LIKE' operator.
>
> The LIKE operator (which was what Joel used in his solution for you)
> uses '%' as a wildcard and _ as a single character match.
>
> Regex is much more complicated, and there are many sources of help for
> it out on the internet.  A similar check using a regex operator would be
> something like:
>
> (em ~ '@.*\.')
>
> since there is no need to specify leading and trailing wildcards within
> a regex (instead you specify that you want to anchor the regex to the
> beginning and/or ending of the string).
>
> A search on the internet might provide a more thorough regex for
> validation of e-mail addresses.  A slightly more complex one I have used
> is:
>
> (em ~* '^[^@]+@[a-z0-9-]+\.[a-z]+')
>
> which should validate (a) there is only a single '@' in the address and
> (b) the first part of the domain name contains only valid domain-name
> like characters.  The ~* operator is the case insensitive regex match
> which I didn't use in the one above since there was no alphabetic
> matching involved.
>
> How I do this in my own applications is actually to implement a function
> for valid email addresses, viz:
>
> CREATE FUNCTION valid_email(TEXT) RETURNS BOOLEAN AS '
>   DECLARE
>     email ALIAS FOR $1;
>     user TEXT;
>     domain TEXT;
>   BEGIN
>     IF email !~ ''.@.'' THEN
>       RETURN FALSE; -- One @ good
>     END IF;
>     IF email ~ ''@.*@'' THEN
>       RETURN FALSE; -- Two @s bad
>     END IF;
>     domain := substring( email from position( ''@'' in email) + 1 );
>     user   := substring( email from 1 for position( ''@'' in email) - 1
> );
>     IF domain ~* ''([a-z0-9-]+\.)+([a-z])?[a-z][a-z]$'' THEN
>       -- Only really worth validating the domain
>       RETURN TRUE;
>     END IF;
>     RETURN FALSE;
>   END;
> ' LANGUAGE 'plpgsql';
>
> This checks for internal spaces as well, and means that the rules for
> valid e-mail addresses happens in only one place.
>
> Regards,
>                     Andrew.
> >
> > thanks again,
> > /j-p.
> >
> >
> > On Sun, 12 May 2002, Joel Burton wrote:
> >
> > > > One column in my table contains email addresses - I want to check
> > > > that any value entered contains a '@'.  How do I create a
> > > > CONSTRAINT or CHECK to ensure this when creating the table?
> > >
> > > create table em (
> > >   em text constraint is_email check (em like '%@%.%')
> > > );
> > >
> > > will work fine, assuming that this check (something @ something . something)
> > > is acceptable in your context as "looks like an email address"
> > >
> > > - J.
> > >
> > > Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
> > > Knowledge Management & Technology Consultant
> > >
> >
> >
> > -----------------------
> >  JUSTATEST Art Online
> >   www.justatest.com
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>


-----------------------
 JUSTATEST Art Online
  www.justatest.com

> > CREATE FUNCTION valid_email(TEXT) RETURNS BOOLEAN AS '
> >   DECLARE
> >     email ALIAS FOR $1;
> >     user TEXT;
> >     domain TEXT;
> >   BEGIN
> >     IF email !~ ''.@.'' THEN
> >       RETURN FALSE; -- One @ good
> >     END IF;
> >     IF email ~ ''@.*@'' THEN
> >       RETURN FALSE; -- Two @s bad
> >     END IF;
> >     domain := substring( email from position( ''@'' in email) + 1 );
> >     user   := substring( email from 1 for position( ''@'' in email) - 1
> > );
> >     IF domain ~* ''([a-z0-9-]+\.)+([a-z])?[a-z][a-z]$'' THEN
> >       -- Only really worth validating the domain
> >       RETURN TRUE;
> >     END IF;
> >     RETURN FALSE;
> >   END;
> > ' LANGUAGE 'plpgsql';
> >
> > This checks for internal spaces as well, and means that the rules for

Fine idea, but be careful about the regex for domains: it tries to ensure
that the TLD ending (.com, .us, etc) is 2-3 characters long. ".intl" and
".info" are both legal TLDs that are four characters long. A better replace
for the line is

> >     IF domain ~* ''([a-z0-9-]+\.)+([a-z])*[a-z][a-z]$'' THEN
                                             ^- note was a ? before

For succintness' sake, though, this seems like overkill: 3 regex matches
that could be collapsed into one. I'd do:

  em ~* '^[^@]+@[a-z0-9-]+\.[a-z]*[a-z][a-z]'

(slightly modified from Andrew's earlier suggestion to include the 2-or-more
chars in TLD)

Not sure how this will play with domains with non-US characters.

- J.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant