PostgreSQL - regex constraint so that varchar contains only digits [duplicate]

Question

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 · Accepted Answer · 2020-09-19 10:46:59Z

8

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

answered Sep 19, 2020 at 10:46

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ROBlackSnail Over a year ago

Thank you. Sorry for the duplicate, the other answered questions were in other languages, not PostgreSQL.

Timo Treichel Over a year ago

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*$')

Collectives™ on Stack Overflow

PostgreSQL - regex constraint so that varchar contains only digits [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related