I am trying to get below constraint work in postgresql which checks the column wcode for the pattern. If the pattern doesn't match should throw an error.
CONSTRAINT wcoding CHECK (wcode::text ~ '[\w]{4,4}-[\w]{2,2}-[\w]{1,1}'::text);
Geniun input string is "AA14-AM-1". which actually works. but the problem is if I enter "AA14-AM-14" or "AA14-AM-1444" it doesn't through an error. I want to restrict input to use this ("AA14-AM-1") pattern.
You have an "unbounded" regex (not sure if that is the correct technical term). Which essentially means the pattern has to occur anywhere inside the input string. To match the input string with the exact pattern, you need an "anchored" regex:
CONSTRAINT wcoding CHECK (wcode::text ~ '^[\w]{4,4}-[\w]{2,2}-[\w]{1,1}$');
The ^ and $ "anchor" the pattern at start and ending which results in the fact that the input string must match the pattern exactly (not permitting the pattern as a sub-string of a longer input value).
AA14-AM-1hello world!AA14-AM-1 which would be an input string that start and end with this pattern.
@a_horse clarifies the role of ^ and $. But simplify overall:
ALTER TABLE ADD CONSTRAINT wcoding
CHECK (wcode::text ~ '^\w{4}-\w\w-\w$');You don't need a character class for class shorthands like \w.
And why is there a cast to text? Might be redundant.
text is just fine. I think you don't need to cast at all (depending on the actual data type of the column).Malav, in PostgreSQL, this compact regex does what you want:
First Method
[\w]{4}-[\w]{2}-\w(?!=\w)
Second Method
[\w]{4}-[\w]{2}-\w\y
Please note that instead of {4,4} you can write {4} to mean "exactly four times".
How does this work?
After the last word character, we check that there is no other word character. For this, in the first method, we use a negative lookahead (?=\w)
In the second method, we use a word boundary \y (In most regex flavors I would add a word boundary \b at the end, but in PostgreSQL it is \y )
This is why in the first version I used a negative lookahead instead (more portable). Use whichever version you like.
{4,4} is ugly syntax, I don't know anyone who uses that in real code, please see {4} in my answer.
{4,4}is redundant, it should be{4}. Also[\w]should be simply\w- for better reading your regex. + Also neither{1,1}, nor{1}is necessary.