Check constraints are a great feature, I was especially excited when I found out that PostgreSQL supports regular expressions in check constraints. Now I want to expose the regex check up to an application layer so that it can be used at input time, before the database chokes on it during an insert or an update. So I went looking for information on how to ask PostgreSQL for the check constraint for a given table and column name. Verlana’s PostgreSQL General Bits came to the rescue with the entry on Implementation of Constraints. The last query in the entry will lookup all on the constraints for a given table. I modified this query to extract just the constraint regular expression for a given table and column.
select substring(consrc from '^.+ [~|~*|!~|!~*] ''(.+)''.+$')
AS constraint_regex
from pg_class r,
pg_constraint c
where r.oid = c.conrelid
and contype = 'c'
and relname = 'your_table_name'
and consrc ILIKE '((your_column_name)::text %'
One reply on “Exposing PostgreSQL Regular Expression Check Constraints”
very interesting,
how can you use the same thing if you want to compare to columns and join tables if one matches a subset of the other?
e.g.
table 1
(id, name)
table 2
(id, full name)
can you do a join with ILIKE to find the matching names (or subsets of names)?