Question 113928 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Check constraint on Postgres to insert only capital alphabetic letters only

I have a text field on my PostgreSQL database that I want to store only capital alphabetic letters not but not special characters in a column.

I have already used CHECK (location_id ~* '[A-Z]') I am able to insert both alphabetic and special characters that doesn’t not solve my requirement.

Tags:

2

Answers


  1. 0

    As documented in the manual the ~* operator checks regex "case insensitively". If you want to only allow uppercase letters, you need to use the ~ operator which checks case sensitively.

    '[A-Z]' only allows a single character. If you want multiple characters, you need '[A-Z]*' or '[A-Z]+' if you want at least one character.

    This only matches the uppercase characters somewhere in the string. To ensure that all characters are uppercase you need to anchor the regex to the start and end of the string using ^ and $

    CHECK (location_id ~ '^[A-Z]+$')
    Login or Signup to reply.
  2. 0

    You can use

    CHECK (location_id ~ '^[A-Z]+$')

    or

    CHECK (location_id ~ '^[[:upper:]]+$')

    The issues are two:

    • ~* matches in a case insensitive way, so [A-Z] matches lowercase letters, too
    • [A-Z] matches one uppercase ASCII letter anywhere in the string, so other chars are also allowed. ^[A-Z]+$ matches a string consisting of only uppercase letters.

    Note that [[:upper:]] can also be used to match uppercase letters.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search