Search code examples
sqlpostgresqlcheck-constraints

How to alter a column to accept values in some specific format?

My table has a column that shows the phone number and I want to add a constraint where this number must be in a specific format like +cod.country-cod.local-num.local. For example: '+54-351-4350114'.

And later I want to add a constraint where num.local must have at least 7 digits.

Solution

  • I guess that you are looking for a Postgres CHECK constraint: such constraint does accept a regexp, with the SIMILAR TO operator:

    ALTER TABLE mytable ADD CONSTRAINT phone_number_check CHECK(
    phone_number SIMILAR TO '\+\d+-\d+-\d{7,}'
)

    Regexp explanation:

    \+        the + sign
\d+       at least one digit
-         the - sign
\d+       at least one digit
-         the - sign
\d{7,}    at least 7 digits

    This will allow values like '+54-351-1234567', while, for example rejecting '+54-351-123456'. You can freely adapt the regexp to your exact requirement, using Postgres Regular Expressions.

    Demo on DB Fiddle