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

Question

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?

Robert Harvey · Accepted Answer · 2009-06-06 17:48:14Z

2

ozgur,

Try

to_tsquery(%s)

instead of

to_tsquery('%s')

answered Jun 6, 2009 at 17:48

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

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

2 Comments

shanyu Over a year ago

Thanks! This is embarrassing, I think I better take a vacation asap :)

Robert Harvey Over a year ago

Would have been more embarrassing if I had guessed wrong...I'm more used to SQL Server syntax. ;)

Daniel Migowski · Accepted Answer · 2009-06-06 17:39:51Z

0

I believe you are missing a ' after the E.

answered Jun 6, 2009 at 17:39

Daniel Migowski

1 Comment

shanyu Over a year ago

The E stuff is put there by Django ORM, I just pass a string.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related