django postgres LIKE query syntax error at or near "%"

Question

In django, to query JSONB, I do:

    cursor.execute("""\
    SELECT * FROM "somemodel_some_model" 
    WHERE UPPER(("somemodel_some_model"."data" 
    #>> array[0,'fieldX'])::text[]) 
    LIKE UPPER(% %s %)
    """,[my_string])

.. and I get:

IndexError: list index out of range

I know the above cant be true because when I use the ORM to achive this using:

obj=Some_Model.objects.filter(data__0__fieldX__icontains=search_term)

.. I get the results. To investigate this, I drop to SQL and do the following:

SELECT * FROM "somemodel_some_model"
WHERE UPPER(("somemodel_some_model"."data" 
#>> array[0, 'fieldX'])::text[]) 
LIKE UPPER(%my_search_string%)

..but, I get:

django.db.utils.ProgrammingError: syntax error at or near "%"

So, the question is, do I need to escape %? Seems odd

Hasitha Jayawardana · Accepted Answer · 2019-03-15 07:37:01Z

1

When using cursor.execute like syntax should be LIKE %% string %% or if you use column and LIKE %% || column || %%

edited Mar 15, 2019 at 7:37

Hasitha Jayawardana

2,4624 gold badges21 silver badges39 bronze badges

answered Mar 15, 2019 at 6:51

Ozan

111 bronze badge

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

Comments

Vao Tsun · Accepted Answer · 2018-02-13 14:06:25Z

0

try this:

 cursor.execute("SELECT * FROM somemodel_some_model WHERE UPER((somemodel_some_model.data #>> array[0,'fieldX'])::text[])  LIKE UPPER(concat('%', %s, '%')) ",[my_string])

because if you use UPPER(% %s %)

resulting query would be like UPPER(% 'your_string' %) (I don't know if django quotes literals) or UPPER(% your_string %) - both cases not valid for SQL, So you either gave to construct [%my_string%] in django, or use concat as in example above

edited Feb 13, 2018 at 14:06

answered Feb 13, 2018 at 10:18

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

3 Comments

AJW Over a year ago

unfortunately, when I use LIKE UPPER(concat('%', %s, '%')) it still gives the same error (I presume there is a typo in your statement with a missing ))

Vao Tsun Over a year ago

yes - there were a typo. are you sure in your """?.. there's no need in "somemodel_some_model" - you can use somemodel_some_model, same for tablename

AJW Over a year ago

I am not totally sure I understand your last comment, but, when I use somemodel_some_model for my other queries, it seems to execute perfectly.

Collectives™ on Stack Overflow

django postgres LIKE query syntax error at or near "%"

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related