CODEWITHSUNDEEP
djangopostgresqldjango-orm
shanyu
shanyu

Reputation: 9716

Why do I get an error when I try to execute a query with parameters in postgreSQL?

The db is PostgreSQL. When I try to execute a query with parameters, such as this one

cursor.execute("""
    SELECT u.username, up.description, 
        ts_rank_cd(to_tsvector(coalesce(username,'')|| coalesce(description,'')) , to_tsquery('%s')) as rank

        FROM auth_user u INNER JOIN pm_core_userprofile up on u.id = up.user_id
        WHERE to_tsvector(coalesce(username,'')|| coalesce(description,'')) @@ to_tsquery('%s')
        ORDER BY rank DESC;
    """, ["hello","hello"])

Django complains of a ProgrammingError, adding syntax error at or near the parameter (in this example, "hello"). Here's the part of the Django generated SQL statement that the error comes from: 

to_tsquery('E'hello'')

Even if I copy-paste it to a postgreSQL shell, I get the syntax error. If I omit the 'E' part, it works. What should I make of it?

Upvotes: 0

Views: 251

Answers (2)

Robert Harvey
Robert Harvey

Reputation: 180787

ozgur,

Try

to_tsquery(%s)

instead of

to_tsquery('%s')

Upvotes: 2

Daniel Migowski
Daniel Migowski

Reputation:

I believe you are missing a ' after the E.

Upvotes: 0

Related Questions