Re: Simple, Add constraint

[Richard Broersma Jr <rabroersma(at)yahoo(dot)com>]

--- nhrcommu(at)rochester(dot)rr(dot)com wrote:
> Trying to add a constraint so that all phone numbers will be in a
> '111-222-3333' format.  Tried a few combos, with below the latest effort. 
> 
> ALTER TABLE "fortour"."mine" ADD CONSTRAINT mine_cnstr_2 CHECK (phone
> LIKE '999s999s9999')

in addition the the LIKE predicate, you can use regular expressions which are a more powerful tool
for pattern recognition.

   ALTER TABLE "fortour"."mine" 
ADD CONSTRAINT mine_cnstr_2 
         CHECK (phone ~ E'^[0-9]{3}-[0-9]{3}-[0-9]{4}$'); 

http://www.postgresql.org/docs/8.2/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP

However, you can generalize this by using creating a DOMAIN called PhoneNumbers.  Notice the
example used for zipcodes in the following link:

http://www.postgresql.org/docs/8.2/interactive/sql-createdomain.html

Regards,
Richard Broersma Jr.