Re: how do I CHECK 'field only contains characters 0-9 or space?' - Mailing list pgsql-novice

From Tom Lane
Subject Re: how do I CHECK 'field only contains characters 0-9 or space?'
Date
Msg-id 11322.985538796@sss.pgh.pa.us
Whole thread Raw
In response to how do I CHECK 'field only contains characters 0-9 or space?'  (Tom Strickland <tom@stricklandc.demon.co.uk>)
List pgsql-novice
Tom Strickland <tom@stricklandc.demon.co.uk> writes:
> The subject says it all really. I have a phone no. field of type
> VARCHAR(15) and would like to add a check that says 'only allow
> numbers or the space character in this field'. Can I do this with a
> CHECK (and if so how?) or do I need a trigger?

Sure: regexp pattern match will do that sort of thing for you.

regression=# create table foo (phone text check (phone ~ '^[0-9 ]*$'));
CREATE
regression=# insert into foo values('555 1212');
INSERT 147838 1
regression=# insert into foo values('555-1212');
ERROR:  ExecAppend: rejected due to CHECK constraint foo_phone
regression=#

Note the above will allow empty-string phone numbers; if you don't want
that, write + instead of *.  For more info see
http://www.postgresql.org/devel-corner/docs/postgres/functions-matching.html

            regards, tom lane

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: On Postgres and permissions, cant read a file into a table
Next
From: Seema Noor
Date:
Subject: Problem in PostgreSQL - jdbc