Postgres function raising error when calling from Python script but running fine in PgAdmin

Question

I have a postgres function called 'medrealize()' inside which i am creating temp table and creating index for temp tables using below lines

CREATE INDEX SQLOPS_RefICD_ICD10_idx ON  t$SQLOPS_RefICD_ICD10 USING gist (code gist_trgm_ops );
CREATE INDEX regexes_idx ON  t$regexes  USING gist (icdregex  gist_trgm_ops );

When calling the function from pgadmin, the function runs without any issues. But when calling the function from python using

cursor.execute("BEGIN")
cursor.execute("CALL medrealize();")
cursor.execute("COMMIT")

I am getting below error

operator class \"gist_trgm_ops\" does not exist for access method \"gist\"\

Any idea why its running fine when calling from PgAdmin and raising error when calling from Python?

You will need to show the actual procedure(it is not a function) medrealize. Also are you actually using $ in table names? Are you sure you are connecting to the same database in the Python and pgAdmin cases. gist_trgm_ops comes from the pg_trgm extension so that extension would need to be installed in the database. — Adrian Klaver
– Adrian Klaver, Commented Nov 25, 2021 at 16:05

mustaccio · Accepted Answer · 2022-06-03 13:31:21Z

5

Did you install the trigram index extension in your database ?

CREATE EXTENSION IF NOT EXISTS pg_trgm;

If not, try to add it inside your function .

edited Jun 3, 2022 at 13:31

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

answered Jun 1, 2022 at 10:20

Imad Maachari

611 silver badge3 bronze badges

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

Collectives™ on Stack Overflow

Postgres function raising error when calling from Python script but running fine in PgAdmin

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related