I was thinking the other day how great it would be if you could store a regex pattern requirement in the database for each column. After a few Google searches I came across a post on the PostgreSQL Novice email list that gives an example of how to do this using PostgreSQL’s Check Constraint feature in combination with the POSIX Regular Expression support in PostgreSQL. Here is an insanely simple example:
CREATE TABLE example_table (
first_name VARCHAR(100)
CONSTRAINT check_first_name
( first_name ~* '^[a-z]+$')
);
That would create a table named ‘example_table’ with one column, called ‘first_name’, that would only accept inserts or updates if ‘first_name’ only contained lower case letters a through z. I love it when something pops into my head that I think would be really cool and then find out that someone else already has it working! (Although having PCRE would have been an added bonus.)
I also looked to see if MySQL supported this. It looks like the current versions do not, at least according to their create table syntax documentation or their constraints documentation. So I started going through their todo list to see if it is planned for some point in the future. I didn’t see it on the feature list for 4.1 or for the new feature list for 4.1. Nothing in the todo list for 5.0 about constraints. Ahhhhh, found it. On the todo list for 5.1 is an entry under ‘New functionality’ called ‘Column-level constraints’. I wasn’t able to find any information about when MySQL 5.1 might become the stable version. The only thing I did find was an email where the guess for MySQL 5.1 was sometime around 2006.
8 replies on “PostgreSQL Check Constraint Supports Regular Expressions”
As of version 7.4, the regular expression engine used by PostgreSQL is the same as that used by tcl (the language, that is). I haven’t investigated it’s capabilities yet, but I am given to understand that it supports a lot of Perl-style regex extensions. Whether it is “PCRE”, I can’t say.
Jeff: Yeah, 7.4+ uses an import of the regex code in recent versions of TCL. That code is itself a new version of Henry Spencer’s regex code (updated by none other than Henry Spencer himself); it is not pcre, though. Nevertheless, PostgreSQL’s regex implementation is pretty featureful.
Thanks for the example! One small correction, I think your last line should read:
( first_name ~ ‘^[a-z]+$’)
[…] Postgres is one of the few databases I’m aware of that currently solves the problem. You can see an example at Joseph Scott’s Blog of using a regex in a check constraint. […]
[…] One a related note, I wrote a simple example on using regular expressions in check constraints. […]
No Preston, The star means the expression is case insensitive.
Hi Peter,
If the purpose of this check is to only allow lowercase letters (as it is stated in the description), then what we want is a case sensitive search. With the star, the check will allow both uppercase and lowercase letters.
syntax:
ALTER Table Table_name
ADD constraints CHECK_column_name
CHECK (REGEXP_LIKE(column_name,'(([0-9][a-z]{1}|[a-z][0-9]{1}))’));
consider the above syntax whether it is an apporiate syntax to create an constraint for a particular column
Example:
ALTER Table userinfo
ADD constraints CHECK_password
CHECK (REGEXP_LIKE(password,'(([0-9][a-z]{1}|[a-z][0-9]{1}))’));
unable to determine the fault ,since the query is unable to get updated..
Kindly reply at your earliest.
Regards
Jayanth S.