0

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 ?

GMB
  • 216,147
  • 25
  • 84
  • 135
ROBlackSnail
  • 501
  • 1
  • 5
  • 20

1 Answers1

5

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).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Thank you. Sorry for the duplicate, the other answered questions were in other languages, not PostgreSQL. – ROBlackSnail Sep 19 '20 at 10:58
  • a phone number can have way more signs. People tend to write it different, but its still valid like "+49(0)1234/567 89 01". so, eigther include them, or exculde them before comparing like: `CHECK(regexp_replace(phone,'[\/\s\(\)+-]','','g') ~ '^\d*$')` – Timo Treichel Feb 23 '22 at 15:57