Use Django ORM Postgresql regex when the regex is in the database and the value is a string

Question

I understand how to do Postgresql regular expression searches through the Django ORM when I am passing the regular expression. What is the equivalent when the regular expression is in the database table, and I am passing the string?

For example, the following Postgresql and Django ORM queries are equivalent:

INSERT INTO StringValues (val) VALUES ('abc');

SELECT * FROM StringValues WHERE val ~* '^[a-z]{3}$';

# is the same as

StringValues.objects.filter(val__regex=r'^[a-z]{3}$')

How would can you do the following query in Django?

INSERT INTO StringValues (val) VALUES ('^[a-z]{3}$');

SELECT * FROM StringValues WHERE 'abc' ~* val;

I.E. the regular expression is in the table and I want the row that matches my string.

In my specific case performance isn't an issue - there will be probably under 100 rows to compare - but comments on the performance of this also welcome thanks, if compilation of every regex is a really bad idea.

I believe you are looking for this solution: stackoverflow.com/a/31689831/1971587 — Joey Wilhelm
– Joey Wilhelm, Commented Jul 26, 2017 at 18:41
Exactly - apologies, I'd forgotten to mark my answer below as the accepted answer. — Chris
– Chris, Commented Jul 26, 2017 at 20:11
Possible duplicate of How can I perform a regex lookup with the regular expression being stored in a field value? — Louis
– Louis, Commented Aug 25, 2019 at 13:38

Chris · Accepted Answer · 2016-09-30 19:46:27Z

5

StringValues.objects.extra(where=["'abc' ~ val"])

edited Sep 30, 2016 at 19:46

answered Sep 30, 2016 at 19:37

Chris

6,5226 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use Django ORM Postgresql regex when the regex is in the database and the value is a string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related