Search code examples
sqlregexstringpostgresqlcheck-constraints

PostgreSQL - regex constraint so that varchar contains only digits

I have a column in my PostgreSQL table for phone numbers of type VARCHAR. I have chosen this datatype because in my country phone numbers start with a 0.

I need to introduce a constraint to check that the phone number contains only digits.

This is what I have so far:

ALTER TABLE contactInfo ADD CONSTRAINT checkPhone
CHECK(phone NOT SIMILAR TO '%[a-z]%' AND phone SIMILAR TO '%[0-9]%');

It seemed to work, but I am afraid it does not filter out characters specific do different languages (like ù û ü â à etc.).

How can I do it ?

Solution

  • You could be more specific that you want digits only:

    CHECK(phone ~ '^[0-9]*$')

    That's even shorter:

    CHECK(phone ~ '^\d*$')

    If you don't want to allow the empty string, replace qantifier * (0 or more) with + (at least one).