Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tom Strickland <tom(at)stricklandc(dot)demon(dot)co(dot)uk>
Cc: Postgres Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: how do I CHECK 'field only contains characters 0-9 or space?'
Date: 2001-03-25 16:46:36
Message-ID: 11322.985538796@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Tom Strickland <tom(at)stricklandc(dot)demon(dot)co(dot)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

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2001-03-25 16:48:07
Subject: Re: On Postgres and permissions, cant read a file into a table
Previous:From: Lars ForsethDate: 2001-03-25 12:05:03
Subject: On Postgres and permissions, cant read a file into a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group