Check constraint on Postgres to insert only capital alphabetic letters only-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

You can use

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

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.