本地PostgreSQL正常，Heroku部署后报operator does not exist错误

本地与Heroku均部署了PostgreSQL 13版本数据库，Django和Python版本完全一致。调用本地数据库的API一切正常，但调用Heroku上的数据库API时触发错误。

搜索输入（如party）为字符串类型，trademark_trademark模型字段定义：

models.TextField(null=True)

疑问：字符串无法查询TextField类型字段吗？如果是，需要什么类型？问题根源在哪？

错误详情 #

ProgrammingError at /trademarks/api/trademarks/
operator does not exist: text % unknown
LINE 1: ...rademark" WHERE "trademark_trademark"."trademark" % 'party' ...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
Request Method: GET
Request URL:    https://CENSORED.herokuapp.com/trademarks/api/trademarks/?q=party&class_nos=0&no_related=true
Django Version: 4.0.5
Exception Type: ProgrammingError
Exception Value:    
operator does not exist: text % unknown
LINE 1: ...rademark" WHERE "trademark_trademark"."trademark" % 'party' ...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
Exception Location: /app/.heroku/python/lib/python3.9/site-packages/django/db/backends/utils.py, line 89, in _execute
Python Executable:  /app/.heroku/python/bin/python
Python Version: 3.9.15
Python Path:    
['/app/.heroku/python/bin',
 '/app',
 '/app/.heroku/python/lib/python39.zip',
 '/app/.heroku/python/lib/python3.9',
 '/app/.heroku/python/lib/python3.9/lib-dynload',
 '/app/.heroku/python/lib/python3.9/site-packages']
Server time:    Wed, 02 Nov 2022 23:00:27 +0000

已尝试操作：更换不同版本的PostgreSQL、Django和Python。

相关代码 #

错误疑似与queryset = self.filter_queryset(self.get_queryset())相关：

class TrademarkList(generics.ListAPIView):

    serializer_class = TrademarkSerializer

    def list(self, request, *args, **kwargs):
        queryset = self.filter_queryset(self.get_queryset())
        serializer = self.get_serializer(queryset, many=True)
        response_list = serializer.data 
        newdict = {'count': self.total_no}
        response_list = {'results': response_list, 'extra': newdict}
        return Response(response_list)

    def get_queryset(self):

        try:
            q = self.request.query_params.get('q')
        except:
            q = None
        ...

        class_nos = self.request.query_params.get('class_nos')
        no_related = self.request.query_params.get('no_related')

        api_class_list = class_nos.split(",")
        conflicting = not self.request.query_params.get('no_related')

        if q is not None:

            search_classes = []

            if class_nos!='0' and class_nos!=None:
                for k in api_class_list:
                    # print k
                    search_classes.append(k)
                    if conflicting!='false':
                        search_classes.extend(get_conflicting_classes(k))
                    else:
                        break

            q_set = q.split(' ')

            q_set_tri = []
            for i in q_set:
                n=0
                for j in range(0, len(i)):
                    try:
                        triad = i[n:n+2]
                        print(triad)
                        if len(triad)==2:
                            q_set_tri.append(triad)
                        n+=1
                    except:
                        break

            q_replaced = q.replace('-','').replace('.','').replace("'",'').replace("'",'')

            queryset_tri = Trademark.objects.filter(trademark__trigram_similar = q_replaced)
        
            queryset = queryset_tri.filter(status__in = ('Registered','Pending','Accepted','Advertised','Accepted with conditions','Provisionally refused'))
            
            results = queryset
            
            sorted_results = tmsearch.custom_input_sort_2022(results,q_replaced)

            self.total_no = len(sorted_results)
            
            return sorted_results

        ....

解决方案 #

问题根源是Heroku PostgreSQL数据库未安装pg_trgm扩展，执行以下命令即可修复：

heroku pg:psql DATABASE_URL -a app
CREATE EXTENSION pg_trgm;

内容的提问来源于stack exchange，提问作者Stephan V