Thread: how do I CHECK 'field only contains characters 0-9 or space?'

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?

Thanks,
Tom

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