How to store regex "literals" in Postgres?

Question

I want to store regex pattern/option "literals" in a Postgres database, like:

/<pattern>/options

I think it's helpful to indicate the expected format and use of the text. Also, the application framework I'm using can coerce this kind of text into the proper Regex type.

I looked through the data types and provided extensions and didn't see anything specific. Am I missing one?

If there is no specialized type, is there a reasonable way to constrain TEXT to likely contain a regex (not to validate the regex, just to ensure text between forward-slashes). Does this work?

pattern TEXT CONSTRAINT is_regex (pattern LIKE '/%/%')

---

At the moment, I'm only using these literals in application code, which is why the TEXT to Regex transformation is very helpful. At some point, I might get better at CTEs and transform them back to regular TEXT (without forward-slashes or options) to be used in Postgres pattern matching functions.

Answer 1

PostgreSQL doesn't offer such type (as of now), but generally speaking you have a few options to preserve database integrity (I can only assume you want this to avoid worrying that the data you read from the database fails your application, because it's not a valid regular expression).

Your best bet is (which you already figured out) is to use a CHECK constraint, one way or the other. If you plan to use this pattern in multiple places, I suggest you to use domain types. That way, you don't have to define these constraints at multiple columns. Ironically the best way to write such a CHECK constraint is to write a regexp pattern to match your regexp patterns (because there are multiple regexp implementations with slight differences). It obviously won't be perfect, but it might be good enough. I.e.

create domain likely_regexp as text
    check (value ~ '^/([^/]*(\\/[^/]*)*[^\\])?/[a-z]*$');

But if you're okay to check against PostgreSQL's implementation, you can (ab)use the fact that CHECK constraints fails not only when the evaluated expression is false, but they also fail when the expression throws (raises) some error. So you can call a regexp function in order to detect if it's actually a valid regular expression or not. Altough you still have to split the pattern and the options part.

create domain pg_regexp as text
    check (regexp_replace('', replace(substring(value from '^/(.*)/'), '\/', '/'),
                          '', substring(value from '/([^/]*)$')) = '');

https://rextester.com/YFG18381