Menu
How can I create a constraint to use a regular expression in postgres?
708
A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression. For instance, to require positive product prices, you could use: CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );
The PostgreSQL CHECK constraint controls the value of a column(s) being inserted. The PostgreSQL provides the CHECK constraint, which allows the user to define a condition, that a value entered into a table, has to satisfy before it can be accepted.
CREATE TABLE emails ( email varchar CONSTRAINT proper_email CHECK (email ~* '^[A-Za-z0-9._+%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$') ); (regex may be incomplete, you can search for regexp for email matching all over the web and pick the one you like best).
86
I recommend using an existing email address parsing module instead of making up your own pattern matching. For example:
CREATE OR REPLACE FUNCTION check_email(email text) RETURNS bool LANGUAGE plperlu AS $$ use Email::Address; my @addresses = Email::Address->parse($_[0]); return scalar(@addresses) > 0 ? 1 : 0; $$; CREATE TABLE emails ( email varchar CONSTRAINT proper_email CHECK (check_email(email)) ); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
