2

I would like to execute an SQL query which has crosstab function in Python, however I get this error message:

psycopg2.ProgrammingError: function crosstab(unknown, unknown) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Is there a workaround? I already tried upgrading psycopg2 (pip install psycopg2 --upgrade). Thanks a lot

Marina
  • 330
  • 1
  • 6
  • 15

2 Answers2

5

Try to precede function with schema name:

<<YourSchema>>.crosstab(....

In case extension wasn`t installed and You want to run query from psycopg2 to install it, commit connection right after.

Alexa3emyh
  • 66
  • 2
2

Have you installed the extension in your schema?

If you run the code:

CREATE EXTENSION tablefunc WITH SCHEMA <<YourSchema>>;

And rerun, you should be able to use your query then. The module should only need to be installed once, won't need to modify your SQL query to install it.

Devasta
  • 1,489
  • 2
  • 17
  • 28
  • thanks for you comment. I have installed the extension in the schema, in fact, there's no problem in executing the query in Navicat. I'm not sure how psycopg2 works, it seems the package does not know how to 'handle' crosstab. – Marina Jun 13 '18 at 08:30
  • Maybe I should close this issue, the only work around seems to be not to use crosstab in the sql query but to do pivoting in python. – Marina Jun 13 '18 at 08:32
  • Would it be possible for you to post the code that you are trying to run? Maybe someone a bit more experienced than I could help then. :) – Devasta Jun 14 '18 at 09:00