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, Nov 25 '21 at 16:05

score 1 · Answer 1 · edited Jun 03 '22 at 13:31

1

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 03 '22 at 13:31

mustaccio

18,234
16
48
57

answered Jun 01 '22 at 10:20

Imad Maachari

11
2

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

1 Answers1