How to use pg_trgm operators(e.g. %>) in django framework?

Question

I'm using the pg_trgm for similarity search on PostgreSQL DB and I need to return the results to the front by using the Django model. But, I got a problem that the operator %> cannot be recognized by the Django framework.

Any advice?

Thank you.

I using the model.objects.raw() to execute SQL. I got an error response:

unsupported format character '>' (0x3e) at index 52
searchParam ='test'
mymodel.objects.raw('select * from mytable where columnname %> %s', [searchParam])
ValueError: response:unsupported format character '>' (0x3e) at index 52

entpnerd · Answer 1 · 2019-01-23T04:21:24.650

0

Try replacing the %> term with the escaped %% symbol. This should result in the following simple SQL query:

'select * from mytable where columnname = %%%s%%'

I believe this query should suffice for your purposes.

edited Jan 23 '19 at 04:21

answered Jan 23 '19 at 03:16

entpnerd

10,049
8
47
68

thank you.but the [=] does not meet similarity search which i shown above. – Chasedream Jan 23 '19 at 04:17
Mixed up. Try the answer now that uses escaped `%%` symbol. – entpnerd Jan 23 '19 at 04:22

score 0 · Accepted Answer · answered Jan 23 '19 at 03:54

It looks like your problem is actually related to Python's string substitution. To construct the final query string, Django does something like:

self.sql % self.params

This uses the old style %-formatting, which wants to interpret your %> as a string substitution mechanism, similar to %s, but that's not a valid combination. To create a % character in the formatted string, you just have to use %% in your input string, like so:

In [1]: MyModel.objects.raw('SELECT * FROM myapp_mymodel WHERE myfield %%> %s', ['test'])
Out[1]: <RawQuerySet: SELECT * FROM myapp_mymodel WHERE myfield %> test>

...

By the way, Django has documentation on basic usage of pg_trgm without having to resort to raw query strings. Just make sure you have a migration that activates the extension first:

In addition to the trigram_similar lookup, you can use a couple of other expressions.

To use them, you need to activate the pg_trgm extension on PostgreSQL. You can install it using the TrigramExtension migration operation.

thank you give so many good advises and I resolved the problem by using the [%%>]. — Chasedream, Jan 23 '19 at 04:21

How to use pg_trgm operators(e.g. %>) in django framework?

2 Answers2